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.
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 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”
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”