I maintain a medium sized Microsoft Access application that is the operational system for a small business. This business has offices in London, Birmingham Leeds and a copy of the Access application runs on PCs within the office system of these offices, allowing the staff to run the business. A SQL Server database runs in the London office and all the Microsoft Access clients connect to it.
For reasons of confidentiality I wont give too many more details about the business, but this article and those that follow will concentrate on the technical aspects of creating a web based replacement that can sit alongside (but maybe eventually replace) the existing Access client. The reasons for taking this path are both business driven and technical. The key drivers are:-
- Access is Windows only, but there is a desire to connect from other devices (such as iPads and Macintosh laptops).
- Difficulties in distributing each new release of the Access client to all offices. A web application doesn’t have this problem
- Difficulties in doing multiple changes to the Access client simultaneously through lack of version control. The web application is essentially textual source code and can be version controlled (I will use git).
- Ability to explore different UI approaches to existing processes to improve usability.
I am undertaking this project in the background, and expect it to take quite a while to complete. For this reason, a key underlying driver is that the new and old applications sit side by side.
I have decided to build the web application as a Single Page Application (SPA) . I did start this project before, about two years ago, and at that time started with JQuery Mobile. A characteristic of this was I started to see quite large and complex files that would have become increasingly difficult to maintain. The essence of using Polymer is that it possible to build relatively small self contained custom web elements, that comprise both the html user interface, but also the control logic surrounding it. This encapsulation is what I am looking for.
The SPA will communicate with the database with small self contained PHP scripts called via AJAX requests. They will use PDO to access the SQL Server database. I could have chosen from a wide range of approaches, but this one is one I know and feel confident with.
My efforts at writing a series of posts to cover a project have, in the past, led to nothing. I write the first post like this, then get dragged into the project and by the time I have emerged from the other side I have no desire to talk about it further. This may go the same way, or maybe not. Polymer, and web components in general, is a new technology that has yet to be fully explored. I have some opinions about application structure and approaches that I hope to discuss in further articles. Whether they see the light of day is another matter. Lets just wait and see.
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”
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):-
- Start Access up as normal for a secured file (ie specify the workgroup file on the command line)
- 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
- 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
- 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)
- Use the “External Data” tab and select an “Access” filetype to import from
- 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).
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
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
Set qd = Nothing
Set conn = Nothing
Set db = Nothing
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”
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”
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
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
returnAddress.valid = False
getAddress = returnAddress
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()
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
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
f.Bookmark = rs.Bookmark
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