The question can be asked “…what do you have to do after upsizing your Access backed to SQL Server to make a front-end Access application connected to SQL Server continue working?”.

The answer to this question can be split into two parts, firstly what is required as changes to the basic design to make your application continue to execute, and what is required to make your application work in a timely and efficient manner.

We start by considering something called dbSeeChanges, which is a change that you can need to make to your code, and we also look at what you may need to consider in terms of when records get defaults and autonumbers and how that may affect your code.

dbSeeChanges

Let us consider a very simple Access database with two tables, one of which has an autonumber, and the other which does not have an autonumber.

Next we have two very simple queries which will update the data in these tables.

Finally we have some program code to perform the updates, this code illustrates several different methods equivalent to double-clicking on the queries. We only show the code for the table which has the autonumber field (code in both cases is nearlt identical); both examples are in the download at the end of this article.

Sub UpdateExamplesWithAutoNumber()
Dim db As Database
Dim rst As Recordset
Dim qdef As QueryDef
' get a reference to the database
Set db = CurrentDb

' note that no recordset type has been specified here
' note this code is very lazy and just edits the first record
' this is to keep the example simple and to the point
Set rst = db.OpenRecordset("tblHasNoAutonumber")
rst.Edit
rst!Description = "This is the first record"
rst.Update
rst.Close

' This is probably the most popular technique to explicity specify a dynaset
Set rst = db.OpenRecordset("tblHasNoAutonumber", dbOpenDynaset)
rst.Edit
rst!Description = "This is the first record"
rst.Update
rst.Close

' Next we execute our query in code
Set qdef = db.QueryDefs("qryUpdateWithoutAutonumber")
qdef.Execute

' Now we execute our query using a second technique
CurrentDb.Execute "qryUpdateWithoutAutonumber"

' The last example, we execute an explicit block of sql
Dim sqlstr As String
sqlstr = "UPDATE tblHasNoAutonumber " & _
"SET Description = 'Updated Text'" & _
"WHERE tblHasNoAutonumber.ATextId ='DDD'"

CurrentDb.Execute sqlstr
End Sub

The above queries and the program code all work, so now lets upsize the two tables to SQL Server and see what happens.

Predictably if you double click to run either of the queries they continueexecute correctly, but what about the program code? The code operating on the table without autonumbers (which have now been translated into what is now a property of a field or column called the IDENTITY property; which is as far as we are concerned just the new name for an autonumber) works, but the code operating on the table with autonumbers fails as shown below.

The code in the second case has to be modified as shown below to incorporate a new option called dbSeeChanges:-

Sub UpdateExamplesWithAutonumbers()
Dim db As Database
Dim rst As Recordset
Dim qdef As QueryDef
' get a reference to the database
Set db = CurrentDb

' note that no recordset type has been specified here
' note this code is very lazy and just edits the first record
' this is to keep the example simple and to the point
Set rst = db.OpenRecordset("tblHasAnAutonumber", dbOpenDynaset, dbSeeChanges)
rst.Edit
rst!Description = "This is the first record"
rst.Update
rst.Close

' This is probably the most popular technique to explicity specify a dynaset
Set rst = db.OpenRecordset("tblHasAnAutonumber", dbOpenDynaset, dbSeeChanges)
rst.Edit
rst!Description = "This is the first record"
rst.Update
rst.Close

' Next we execute our query in code
Set qdef = db.QueryDefs("qryUpdateWithAutonumber")
qdef.Execute (dbSeeChanges)

' Now we execute our query using a second technique
CurrentDb.Execute "qryUpdateWithAutonumber"

' The last example, we execute an explicit block of sql
Dim sqlstr As String
sqlstr = "UPDATE tblHasAnAutoNumber " & _
"SET Description = 'Updated Text'" & _
"WHERE AnAutonumberField = 1"

CurrentDb.Execute sqlstr, dbSeeChanges

End Sub

There are a couple of points to take away from this. Firstly the example which uses CurrentDb.Execute "qryUpdateWithAutonumber" did not have to be modified and this continued to work, secondly adding the dbSeeChanges when operating on a table without an autonumber makes no difference (this is not shown above, but you can check this yourselves). Thirdly the code that did not use an explicit dbOpenDynaset (recordset type option), now needs this in addition to the dbSeeChanges option. So in summary, your code will need changing, but the good news is that it is a small change.

Top of Page

When autonumbers and defaults become available

In Access as soon as you start to edit a record, it will display the autonumber as illustrated below (shown first in Access and then when connected to a SQL Server table).

You can see from the above the autonumber in SQL Server is not yet visible during the insertion of a new record. There is also another subtle issue here, if you had a field with a default value again it will not as yet be shown. As soon as the record is saved, both the autonumber and any defaults will be visible.

So does this matter ?.

Well there are two points to consider here. Firstly it may matter to your users that they don’t see defaults or the autonumber until the record is saved, but that can be overcome by educating the users in terms of when seeing a unique identifier only after saving the record, and if default must be shown before a record in saved then this can be coded into your forms. The second point relates to whether you have any programming logic that relies on defaults or the autonumber being available before the record gets saved. The effect of this is now illustrated in the next example.

Top of Page

Example of programming on BeforeInsert

In the example below we see some simple programming code which has been written on the BeforeInsert event in an Access form. Firstly inserting a new records before the upsizing.

Next after upsizing when linked to SQL Server.

So this gives you an idea of how your code could fail, you can no longer program with the assumption that the autonumber will be available prior to the record being saved.

Options here could involve moving the code from the BeforeInsert to AfterInsert event, or in a more extreme example providing some kind of button for creating a new record, which generates the record and then updates the form to display the new record.

Summary

This article has focused on the bare essentials of what changes you may need to make your application code, part II looks at your design philosophy and how this may need to change. Once you get into the SQL Server mindset not only will you be producing applications which never fail or corrupt, they will also scale to more users and work significantly faster than Access.

Download

The sample code used in this article can be downloaded from this link.

Top of Page

MUST has the ability to support yet as un-defined data types in achieving as high a level of translation as possible. In terms of Access, because we have support for the new DATE data type in SQL Server applications dealing with historical data can now benefit from this. And because Access support pass-through queries there are always ways to get around some of the limitations of using a linked table with new data types.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz