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.

Jumping to a particular record in MS Access

I have been working with Microsoft Access for a while but I had run up against a problem that took me a while to solve. I have a form, with two sub-forms side by side, both of which contain similar subforms displayed in continuous form mode. Each subform is a query based around activities on a daily basis, with each record representing a particular date. The reason the subforms are side by side, is to allow the user to compare dates with different filters (the filters are controlled by combo boxes at the head of each subform column.

What I had been trying to do was when a user clicks in one subform, the current record pointer in the other subform moves in tandem. I had some visual basic, that attempted to move to the first record of the subforms recordset and then search forward looking for the particular date in question. I was finding the record, but couldn’t find a way to move the current record two it.

I eventually found the solution, which was to do the searching on the recordSetClone of the form, and then set the forms bookmark to that of the recordSet. The snippet of code below shows how. “subFromDateField” is the field name of the field in the Subforms Data source (a query) which contains the date we want to jump to, and “ctlCalendar” is a calendar control set up by the users click on the other form to the date we are working with. “mySubForm” is the name of the control in the main form which holds the subform.

Dim rs AS DAO.recordset
Dim f AS Form
Dim q AS String

q = "subFormDateField <= #" & Format(ctlCalendar.value,"yyyy-mm-dd") & "#"
Set f = Me.mySubForm.Form
Set rs = f.RecordsetClone
If rs.RecordCount <> 0 Then
    rs.MoveFirst
    rs.FindFirst(q)
    f.Bookmark = rs.Bookmark
End If

Remarkably simple when you know how.

UPDATE: It turns out that you have to be rather careful about date format. I am working in UK date format, but queries like the above assume American! It seems the easiest to convert to ISO date format as there is less ambiguity that way

Managing SMF software in GIT

Yesterday, I upgraded three sites running SMF (Simple Machines Forum) software to v2.0RC5, upgrading them from v2.0RC4.  Some of these sites are more than just a simple implementation of the base software.  All of them have some modifications added, one (Melinda’s Backups forum) has a considerable degree of modification, and unlike WordPress, where plugins are pretty much stand alone, relying on well defined hooks in the software to get into the path, modifications in SMF tend to be intrusive.  Despite all the modifications, I upgraded all the sites over just a few hours.

I believe that is because of the approach I have taken to managing the configurations in Git.  In this post I want to explain how I do it.

Continue reading “Managing SMF software in GIT”

Displaying my RSS feed on Hartley Consultants’ front page

I had always planned to replace the feed on the front page of Hartley Consultants web site with a summary of recent posts from this blog.  I had previously sucked in news articles from the local SMF Forum that is on site, but I intend to remove the forum shortly because I am setting up the equivalent on this site, where it is more appropriate.

It turned out to be remarkably easy to achieve.  A call to “file_get_contents” with the url of my feed returned the raw xml into a variable, whilst a call to “preg_match_all” enabled me to parse the feed content and find the relevant information to display.  The formatting of the articles was already achieved by matching the same format as I had used for the forum feed.

Here is the code I used

	$feed = file_get_contents('http://www.chandlerfamily.org.uk/feed/');
	//having got the xml feed from chandler's zen we parse the xml looking for all the entries
	$pat = '#<item>.*?<title>(.*?)</title>.*?<link>(.*?)</link>.*?<pubDate>[^,]+,\s+(\d+)\s+([[:alpha:]]+).*?</pubDate>'; 
	$pat .= '.*?<description><!\[CDATA\[(.*?)\[\.\.\.\]\]\]></description>.*?</item>#sm';
	if (preg_match_all($pat,$feed, $topics,PREG_SET_ORDER)){
		$item = 0;
		foreach($topics as $newsitem) {
			$item++;
?>    <div class="newsitem">
        <div class="date"><h4><?php echo $newsitem[4]; ?></h4><h5><?php echo $newsitem[3]; ?></h5></div><h3><?php echo $newsitem[1]; ?></h3>
        <p><?php echo $newsitem[5];?></p>
        <p><a href="<?php echo $newsitem[2]?>" title="view full post at www.chandlerfamily.org.uk (in a new window)" target="_blank">More ...</a></p> 
    </div>
<?php
			if($item > 2) break;
		}
	} else {
		echo '<div class="newsitem">Sorry No Data</div>';
	}
	unset ($feed);
	unset ($topics);

The most important part is the pattern to match. But even that turned out to be quite straight forward, even in it did take a bit of trial and error to get it exactly right. I hope someone trying to do the same thing can use this as a starting point.

Allowing Syntax Highlighting in Comments

I have installed the SyntaxHighlighter Evolved plugin to allow me to post snippets of code to my blog.  However (as far as I can tell) it does not natively support allowing syntax highlighting in comments.  As I was reviewing the old posts pulled in from my Drupal site, and in particular the comments that I had made to update some of the older posts, I realised that I had quoted code in them which would be useful to also highlight.  I decided that I would try and add the facility for syntax highlighting to be added to comments in a simple way as possible.

I noticed that that way highlighting seemed to work is that it adds a construct to the <pre> tags that delineate the code that should be highlighted of “class=brush:lang;” where lang is the particular language we are highlighting for.  I felt this was a little complex for comments to include in their “semi” html markup,  but that it should be simple enough for them to add <pre lang=”lang”> as a construct.

Continue reading “Allowing Syntax Highlighting in Comments”

Integrating Gitweb output into a WordPress page

One of my known stumbling blocks to converting my site to WordPress was how was I going to manage to incorporate gitweb so that its output appears within the middle of a page, formatted to at least look like a normal page. The problem occurs because gitweb takes over managing the output and writes an html page complete with headers and footer. You are allowed to provide your own site_header.html and site_footer.html files, but these are inserted just after the opening tag and just before the closing tag. There is no way you can add to the header.

One approach to this, and the one I took on my previous version of this site, was to embed gitweb within an <iframe>. Effectively gitweb’s output is sandboxed within the <iframe> context. Unfortunately, despite some javascript trying to detect it, when the quantity of output changed the size of the iframe needed to change and it didn’t seem to do that automatically. I often found the output was chopped off (with a scroll bar provided) even when my full page was nowhere near full screen.

I have taken a different approach this time. In outline I have created a holding page with permalink “/software/” to hold the repository code and then created a special template for my site theme which when called embeds output from gitweb into the page. There are a few tweaks to the header is moved to the correct place and that links within the output are correct.  Down to the detail.

Continue reading “Integrating Gitweb output into a WordPress page”