Adding custom buttons with your theme in an SMF forum

I recently wanted to update a theme I was developing for an SMF forum so it could add the “unread messages” and “replies to my posts” links as a menu items on the main menu.

Since the menu is placed on every page all this takes place in the main index.template.php for your theme.

With the integration hooks introduced in SMF v2.0 it becomes a very easy process. First during the theme initialisation (function template_init) when need to add a temporary (ie it only lasts for the display of the page) integration hook for the menu buttons

	add_integration_function('integrate_menu_buttons', 'mytheme_button_add',FALSE);

Then we just add a function at the end of the template page

function mytheme_button_add(&$buttons) {
	global $context,$txt,$scripturl;

	//only do this if user is logged in
	if ($context['user']['is_logged'])
	{
		//this is our new entry
		$newbuttons = array('unread' => array(
					'title' => $txt['view_unread_category'],
					'href' => $scripturl . '?action=unread',
					'show' => true,
					'sub_buttons' => array()
					),
				'replies' => array(
					'title' => $txt['replies'],
					'href' => $scripturl, '?action=unreadreplies',
					'show' => true,
					'sub_buttons' => array()
					)
				);
		//now insert just after home.
		$buttons = array_slice($buttons,0,1,true) + $newbuttons + array_slice($buttons,1,count($buttons)-1,true);
	}
	return;
}

Note how we slice the button array into two halves (in this case 1 before the break and count -1 after, but you could put them at any position), and insert the new buttons in between.

The disappointing state of Linux Video Editing software

I thought it would be a simple job. Record a video of me using my Air Hockey program to demonstrate its features. I have spent some time writing a script and recording and editing it in AudaCity. I have generated a number of ogg/vorbis/theora (not sure which is the right term) video files using recordMyDesktop showing the software running, and so all I needed to do was pick video sequencing editor to put them all together and I would be done.

Easy, or so I thought. Wrong!!!

Continue reading “The disappointing state of Linux Video Editing software”

A Conditional Formatting problem with Microsoft Access

I have been puzzling for some time why a Microsoft Access continuous form in an application I am maintaining insisted on replacing the background colour of a control by making it transparent when the record was not selected, and although the background colour returned when actually selecting the record the conditional formatting that was supposed to have been applied failed to do so  . Only today have I found the reason, and so I thought I would share it with you here.

Continue reading “A Conditional Formatting problem with Microsoft Access”

Removing Security From An Access Application

I have been working with an old Access 2000 application and decided, due to the change in use to a Kiosk type application, where multiple people use the same application without shutting down, that the security mechanism based on Workgroup security will have to go. This application had previously been secured by using a bespoke workgroup file, where the original Admin user had been deleted and a new Administrator user had been added to act as the overall administrator.

I tried the mechanisms that I found by searching for it, namely to give privileges to everything to the user group but it failed at the point where you are supposed to import the application into a newly created database – telling me, not unreasonably, that it was secured by a workgroup file that I hadn’t provided.

The breakthrough came when I was reading about the security provided by Access 2007, and in particular the new accdb file format. User level security no longer applies to these file formats and Access removes any security it has applied when you convert to this database format.

So the process turns out to be simple. Just follow the following steps (using Access 2010 – I assume earlier versions will have equivalent options):-

  1. Start Access up as normal for a secured file (ie specify the workgroup file on the command line)
  2. Select the file tab and chose the “Save and Publish” option. Select the file format for the accdb format and click on the large “Save As” button
  3. When the conversion completes (mine took about 5 minutes – it is not an immediate process) and the file is saved in the new format close down Access
  4. Restart access without a work group file and create a new blank database in the the earlier Access file format (I am using Access 2000 format)
  5. Use the “External Data” tab and select an “Access” filetype to import from
  6. Import all objects from the file that you saved in the accdb format

The only issues I have had with that process so far are

  • Some of the reports were set up for a printed not on my current development machine. I will have to re do the printer setup for those
  • I was missing the references to ADO and DAO objects, so I had to manually re-add them (from Visual Basic, Tools, References menu).

How to have a SQL Server stored procedure be the source for an Access report

In converting my Access back end database I came across a little gotcha that took a while to solve. I was trying to dynamically have a stored procedure on the server act as the record source for a Report. But what ever I tried, I kept getting an Error Message “Run-time error ‘32585’ This feature is only available in an ADP”.

Google searches did not exactly throw up an answer, but it gave me several avenues to explore. Eventually and with a bit of tweaking I eventually found a solution that works. Since the report I was converting had previously used a Query (“qryPromotionLetter”) as its datasource, I knew that I could rely on that name to provide a vehicle to hold a new dynamically created query. The problem was that the connection string I needed for my ADO.Connection didn’t seem to work with the DAO.queryDef that I was creating. But as you can see from the code below, I eventually found the solution.

    Dim conn As New ADODB.Connection
    Dim SQL As String, stConnect As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    
    conn.Open getStrConn 'getStrConn  is function that returns the string to make an ADO connection
    Set db = CurrentDb()
 
    On Error Resume Next
    Set qd = db.QueryDefs("qryPromotionLetter")
    If Not IsNull(qd) Then
    ' So it exists, we must recreate it because the date might be different
        db.QueryDefs.Delete qd.name
    End If
        
    stConnect = "ODBC;driver=SQL Server;" & conn.Properties("Extended Properties") & ";"
    Set qd = db.CreateQueryDef("qryPromotionLetter")
    qd.Connect = stConnect
    qd.SQL = "EXEC dbo.qryPromotionLetter '" & letterDate & "';"  'letter date  can come from anywhere
    qd.ReturnsRecords = True

    qd.Close
    Set qd = Nothing
    Set conn = Nothing
    Set db = Nothing

An automated backup regime for a small SQL Server database

I am making this post to show how to make a simple automated backup regime for a relatively small SQL Server (I am using SQL Server 2008 R2 Express Edition) database. I am doing so, because when I was looking for some inspiration for doing this myself I found it quite hard to get all the information together in one place. I hope this is of use to someone.

The database itself is supporting a small business and will typically have transactions that alter it only during the day. The database itself is currently about 150MB big and a full backup takes less than a minute. It replaces an Access database which is being backed up daily. As a result of that, I decided not to bother with differential backups, and instead take a full backup daily. But I believe we can do better (than with Access) with respect to data security, so I am using the full backup mode on the database and taking transaction log backups. For convenience, I take one just before business starts for the day at 8:00am, and repeat the process every 4 hours (at Midday, 4:00pm and at 8:00pm) until business is shut. Because this is the Express Edition of SQL Server I am scheduling the backups using the standard Windows task scheduler.
Continue reading “An automated backup regime for a small SQL Server database”

How to create an Access SubReport which uses as SQL Server Stored Procedure as its data

I am porting an Access Front End/Access Back End application into an Access Front End/SQL Server Back End application.

One thing that got me stumped for a while was a report which had an embedded query as a subreport. This query was complicated in that it had many joins, group by clauses on inner selects that were parametrised and quite complicated functions to work out what to display. It took a long time to run.

Ran a check on the SQL being sent to the server and soon realised that in order to conduct the joins the enquiry was returning all the records in one of our major tables. This was to produce an output of just a few lines long. There was only one thing for it, this query would have to be executed on the server.
Continue reading “How to create an Access SubReport which uses as SQL Server Stored Procedure as its data”

Importing Access Database into SQLServer when the former is protected with a workgroup file

I have been trying to explore different ways of populating a SQLServer database with data from a fairly complex multiuser Microsoft Access application, with a view to migrating the application to using SQLServer for its primary data server. The application is split into front end accessing a main backend database (and a couple of other smaller backed ones), and is protected via a workgroup file that is NOT the standard system.mdw.

Using SQLServer Management Studio, I was attempting to import the data. However all attempts at that were failing because I was not specifying the workgroup file and so the Jet db engine was (rightly) rejecting attempts to connect.

Although I would have assumed this is a fairly standard operation – since the recommendation to secure your Access application with a new workgroup file is fairly strong, I was unable to find much information about this issue on the internet. However after a bit of digging I did finally find how to achieve it, so I thought I would add a post here to show how its done. Continue reading “Importing Access Database into SQLServer when the former is protected with a workgroup file”

Calling a Form with Return Values.

I recently learnt a neat trick for calling a form in Microsoft Access and retrieving values from it. It is really nice to encase the whole thing in a function which retrieves a return value.

Let me show you with a simple example – I want to return an address based on a post code lookup.

First, we need to define an new Type to hold addresses. All address formats are different, but this is mine

Public Type Address
    valid As Boolean 'true of remainder of record holds a valid address, false otherwise
    hseNoStName As String
    address1 As String
    town As String
    county As String
End Type

We then have a function to do the lookup

Public Function getAddress(postcode As String) As Address
    Dim returnAddress As Address
    DoCmd.OpenForm "frmPostCodeSearch",,,,,acDialog,postcode   'call form as dialog,with postcode as openArgs
    If CurrentProject.AllForms("frmPostCodeSearch").isLoaded Then
    ' We found an address, so return it
        returnAddress.valid = true
        returnAddress.hseNoStName = Forms!frmPostCodeSearch.hseNoStName
        returnAddress.address1 = Forms!frmPostCodeSearch.address1
        returnAddress.town = Forms!frmPostCodeSearch.town
        returnAddress.county = Forms!frmPostCodeSearch.county
        DoCmd.close acForm "frmPostCodeSearch",acSaveNo
    Else
        returnAddress.valid = False
    End If
    getAddress = returnAddress
End Sub

I won’t go into the full detail of the post code search as its quite hard. The important bit is what to do if no address matches – So there is a click on an “Abandon” button

Private Sub Abandon_Click()
    DoCmd.Close acForm,"frmPostCodeSearch",acSaveNo
End Sub

And what happens when the user double clicks on field in the record selected

Private Sub hseNoStName_DblClick()
    Me.Dirty = false 'ensures record is not locked
    Me.Visible = false 'causes dialog to return to calling function
End Sub

A Video about my Air Hockey software

I have made a video about my Air Hockey game, a web based simulation of an Air Hockey table. I repeat it here, although it is also available on my software applications page

Making a video that works with Flowplayer seemed to be quite a difficult job. In producing the video using linux I tried

  • Cinelerra – wouldn’t show a preview of any of my files
  • Cinelerra-CV – crashed and wouldn’t recover when I added anything complex
  • Kdenlive – refused to display a single image for a video clip of about 30 seconds.
  • Pitivi – crashed doing anything (although to be fair this was in 32 bit mode, and when I tried again after I upgraded my system to 64 bit mode it was a lot more stable)
  • Blender – very limited codecs – had trouble rendering at the transitions (fading didn’t seem to work)
  • Openshot – did seem to crash a lot, particularly when seeking backwards.  I got round it by saving frequently.  This is what I eventually used.

Even after I completed the project, my first rendering attempt produced an mp4 file that flowplayer would not play with any sound.  I had to use ffmpeg to re-encode the audio into aac format (which is should have done in the first place but didn’t)

The other really big problem I struggled with is recording sound created by the software.  In the end I had to record the audio onto an old mp3 player by feeding the output from my sound card into it, and then transfer the resultant mp3 file back to my system and use the video editor to synchronise the audio with the video I had recorded at the same time.  As you can see I didn’t do a very good job.  I was hampered by the fact that the only video editor I could consistently achieve results with was openshot, and it does not display (unlike many of the other video editors)  the audio waveform in the track.  Synchronisation was a trial and error process of moving the track and replaying that part.