Deploying MS-Access Table Design Changes to Remote Files

Posted On August 7, 2019

A New Challenge for Access

I designed a Microsoft Access database for one of my clients and it’s split into two files – a front-end file with all the forms, reports and code and a back-end file with the tables. That’s pretty standard.

During the development process, I needed to make adjustments to the table design – new indexes, fields need to be added and removed, relationships changed. That’s usually no problem if I have remote access to the client’s system and can login and make the changes. In this case, I don’t. Even to deploy a new program version, I have to send it to the client and let them unpack the files to their desired locations.

It’s not the ideal situation but it’s what I have to work with. When it comes to making design changes in the back-end tables, I could try to guide the client rep through the process but I’m not risking that. I’d rather have a routine that will automate it and ensure that everything is done as painlessly as possible.

My solution is to add subroutines to the program’s startup that will open the back-end file and use Visual Basic for Applications (VBA) and Structured Query Language (SQL) to make the necessary changes. I also want to perform a backup first and ensure that a specific fix will only be applied once. No sense doing unnecessary work and many of these can only be done once after all. I also want the routine to catch any errors and alert the user to contact me if necessary.

Most of my Access apps have a form that’s loaded on startup. If it’s not the main menu, then it’s a splash screen or a form that takes care of behind-the-scenes operations and then loads the main menu.

By inserting code into the On Load event of this form, I can ensure that it will be run as soon as the database opens. The code then tells the form to close and open whatever other form I want the user to see. This will work fine for running updates before the users do anything in the database and, if everything goes well, it will be instant and invisible to them.

DAO vs. ADO in Access / VBA

There are two different command sets that VBA can use to work with databases, whether it’s the native Access format or other databases like SQL Server. Data Access Objects (DAO) is the default set of commands that Access uses in the background for reading and writing to its own tables and you can use it in VBA to open separate connections as needed. ActiveX Data Objects (ADO) is an object-oriented set of commands that is appropriate for both Access databases and other databases formats such as SQL Server. Both of these command sets act as interfaces, providing the programming objects and commands that represent objects and functions within the database.

If you would like a more detailed explanation of the difference between the two, check out Steve Bishop’s YouTube series on Advanced Programming in Microsoft Access 2013, particularly videos 9 through 13 which demonstrate how to use both. While the series was written for Access 2013, the material remains current as of Microsoft Access 2016.

Using ADO

My main use of ADO has been to issue SQL commands directly to the database that will alter the table’s structures. This can be great for quickly adding columns and indexes or doing mass updates to a new column.

In order to use the ADO commands in VBA, you need to ensure that the proper reference is selected in the VBA environment. To do this select Tools >> References from the top menu.

Once this library is selected, you’ll have access through intellisense to the ADO library objects. This actually leads to a potential drawback of using ADO. If you notice in the screenshot above, the library is located at a specific location on the system that’s running the database. Not all systems necessarily have this library installed. If you’re designing this to run on a remote system, it might fail so you need to know the environment you’re programming for.

The second drawback, at least for some, is that you need to know SQL DDL (Data Definition Language) commands in order to issue them to the back-end file. Of course, SQL is a very valuable skill to have so it’s a good idea to get familiar with it.

When issuing updates in this way, best practices like error handling are very important so let’s start with a basic example. I like to enclose each update in its own subroutine that I can add to or remove from the startup On Load event as needed.

Public Const BACKENDFILE As String = "Z:\Access\Production.accdb"

Public Sub Update20190728()
 
On Error GoTo errHandler

<insert code here>

Exit Sub

errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error: (Updates.Update20190428)"

On the first line, I declare a public constant that will hold the location of the back-end data file. I’ll be referring to it in different updates so it’s good to store it this way in case the location ever changes. If you don’t want your users to know where it is for any reason, this might not be the preferred method.

In VBA, you can use the basic On Error syntax to handle errors. In this case, it’s just going to direct execution to the errHandler block and show the user a message so they know to contact me. Needless to say, I’m going to test this routine thoroughly and do everything possible to make sure it doesn’t fail.

When using the On Error Goto method, don’t forget the Exit Sub line which will keep the code from falling through to the error handler even if there isn’t an error.

The first thing you need to do in the actual procedure is to connect to the database file. (Actually the first thing to do is a backup of the file just in case it all goes south but I’ll talk about that later.)

Dim cnProd As New ADODB.Connection    
cnSales.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BACKENDFILE & ";"

ADO uses a connection string to specify the database that it’s connecting to. Again remember that ADO can connect to a variety of database types that might require different connection information. In this case, it just needs the provider type for the Access back-end file and the data source which is the location of the file. The string uses the constant I declared earlier to insert the location where needed.

If you need to find out how to write a connection string for another type of database or to specify more settings, ConnectionStrings.com is an excellent resource. It provides the format for virtually any database and situation you can imagine.

The next step is to actually open the connection but first I want to make sure that the update needs to be performed. In this case, it’s the addition of a column and index. The customer is probably going to open the database and run the startup routine many times before I send them another update and I don’t want to repeat those steps or even open the connection if it’s already been done.

In this example, I’m adding a new column called “Modified” to the UnitProduction table and an index on the column. The column specification indicates that it does not accept null values and has a default of the current date.

If Not VerifyFieldBySQL("SELECT TOP 1 Modified FROM UnitProduction") Then
   cnSales.Open
   cnSales.Execute "ALTER TABLE UnitProduction ADD COLUMN Modified DATE NOT NULL 
      DEFAULT Date();"
   cnSales.Execute "CREATE INDEX NewIndex ON UnitProduction (Modified);"
   cnSales.Close
End If

Set cnSales = Nothing

The VerifyFieldBySQL function that I reference on the first line is a simple function that I wrote that will issue the SELECT command on the field through the front-end and return value of False if it fails, meaning that the field is not there and it’s okay to proceed with the update. The .Open and .Execute commands then open the connection and execute the specified SQL commands.

The .Close command then closes the connection. This will probably be done anyway once the procedure ends and the cnSales connection variable goes out of scope but it’s best to do this explicitly to keep things neat. At the end, the variable is explicitly disposed of. It’s also a good idea to add these two lines to the error routine just in case the procedure craps out at some point.

errHandler:
cnSales.Close
Set cnSales = Nothing
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error: (Updates.Update20190428)"

Again, testing is essential with this kind of operation and, as I mentioned above, a backup routine is a very good idea. With an Access database, it’s as simple as using the FileCopy command and adding a date string to the file name.

Private Function BackupFile() As Boolean

On Error GoTo errHandler
Dim dateString As String

dateString = DatePart("yyyy", Date) & Format(Date, "mm") & Format(Date, "dd")
FileCopy BACKENDFILE, "Z:\Access\Production" & dateString & ".accdb"
BackupFile = True

Exit Function
errHandler:
BackupFile = False
Err.Raise Err.Number, "File backup procedure", Err.Description

End Function

This function simply copies the file and concatenates a date string using the DatePart function and the current date. As a Boolean function, it returns a True / False so if it fails, you can use this to prevent the update from even running since you haven’t backed up the file.

Using DAO

While ADO focuses on the generic commands that can apply to a variety of database types, DAO features more commands that are specific to Access (and Excel) data sources. It works directly with the Office object model. You can still issue SQL commands if you want but you can also use commands to directly affect tables, queries and other objects in the database.

As with ADO, you need to define the connection and open it but the syntax is quite a bit different. The example below removes the default value from an Access table.

Dim dbProd As DAO.Database
Dim tdfProd As DAO.TableDef

Set dbProd = OpenDatabase(BACKENDFILE)
Set tdfProd = dbProd.TableDefs!UnitProduction
    
If tdfProd.Fields!Amount.DefaultValue = 0 Then
    tdfProd.Fields!Amount.DefaultValue = ""
End If

The first two lines above define a Database and TableDef object which represents a table definition in Access. The third line uses the OpenDatabase command with the location constant I defined earlier to directly open the back-end file and assign it to the database variable. Access maintains its own DAO connection which can be accessed through the CurrentDb object in VBA but, in a split database, it works through the linked tables in the front-end and design changes are not possible.

The fourth line gets a little tricky because DAO is not going to supply table or other object names through intellisense. You have to know the object names in the database and get them right. This is called late-binding because the existence of the object is not going to be verified until the code- is running. Early-binding verifies the objects when the code is compiled. You could also write the line as follows:

Set tdfProd = dbProd.TableDefs("UnitProduction")

In the IF … THEN statement, the references to the Amount field in the UnitProduction table and its DefaultValue property are also late-bound so intellisense is not going to help you out there. You need to know your tables, fields and the available properties or look them up. There will probably be some trial and error involved so, again, testing is all-important as are the other best practices I mentioned in the ADO section.

As with the ADO connection, the following lines will clean up the objects at the end.

dbProd.Close
Set tdfProd = Nothing
Set dbProd = Nothing

Conclusion

Again, if everything is done right and thoroughly tested, the changes should happen in the blink of an eye and the user won’t even notice it. You could add a message to alert the user if you wanted but that’s up to you.

Challenges like this are fun for me. While Access is a desktop database that’s primarily intended for power users and is generally not considered an option for enterprise development, an understanding of the technologies behind it will enable you to do some great things when needed.