What I'm presenting in this article is the result of 2 conditions in my work area:
- We have a SQL Server production environment but no development or test environment; and
- We have an MS Access front end using tables in SQL Server but we are not using any other components of SQL Server (i.e., we are not utilizing stored procedures, views, table-valued functions, etc.).
Without these 2 conditions, I would not present this article as a possible work around. Even without the second condition, if you have a development or test environment, I would recommend using those environments instead of what is presented in the article. Also, I acknowledge that there may be several other alternatives to what I'm presenting but recognize that's usually the case with any challenge we face.
Why Develop this VBA
I have created a MS Access database front end using SQL Server as the backend for data storage only. Although eventually I'd like to run all my processes through stored procedures in SQL Server so that the front end platform could be changed, the volume of data is small enough to run my processes through MS Access VBA. Also, my experience with SQL Server is progressing but not to the point of being able to quickly write the code in the time needed to complete this project. So, I'll come back to writing the code later.
We issue the MS Access front end to members of our team through an automated front-end updater. This process puts a tool icon on their desktop and saves the database to their hard drive. Within the database is a local table with a version number that checks a SQL table with a version number to determine whether the database is the latest (do the version numbers match). This way, in the unlikely event they open the database directly instead of through the icon, the database can check the version numbers, return a message box if they don't match and close the database. The message box tells them they need to use the icon to open the database so that the front-end updater can make sure they're working from the latest version of the database.
A colleague - another programmer - mentioned that he’d like to take a look and play around with the data but because it’s live and we have no development or test environment, he is reluctant to do so. I looked around and cobbled together some code so that he can make a copy of the database, convert the linked tables to local tables, and play around to his heart’s content. Below is the code. Feel free to use it as you see fit. :)
Private Sub cmd_ConvertLinkedToLocal_Click()
' For rare instances in which the linked table found no longer exists in SQL Server
On Error Resume Next
‘ Get current database path/name
myDBName = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1, 200)
‘ Check whether still original name and if so, instruct user to change the database name
If myDBName Like "AuditDatabase*" Then
"AuditDatabase' is included at the beginning of the database " & _
"name." & vbCrLf & vbCrLf & "Please rename your local database before " & _
"proceeding." & vbCrLf & _
"(e.g., change 'AuditDatabase' to 'myAuditDatabase')": Exit Sub
‘ Make sure user really wants to convert database.
msgboxA = MsgBox("DO NOT DO THIS UNLESS YOU ARE SURE!!!. " & vbCrLf & vbCrLf & _
"This will convert your linked tables to local tables!" & vbCrLf & vbCrLf & _
"ARE YOU SURE?!", vbExclamation + vbCritical + vbYesNo, "ARE YOU SURE?!")
' If no, do not proceed
If msgboxA = vbNo Then Exit Sub
' Capture beginning time
Me.txt_StartTime = Now()
Dim myDB As Database, myTDF As TableDef
Set myDB = CurrentDb
' Check each table definition
For Each myTDF In myDB.TableDefs
' If a linked table (source is outside of the current db) . . .
If myTDF.SourceTableName <> "" Then
' Select the table and convert to local
DoCmd.SelectObject acTable, myTDF.Name, True
' Capture ending time - note we also have a text box that computes the difference between beginning time and ending time so we no how long it took (surprisingly quick)
Me.txt_FinishTime = Now()
' Let user know the process is completed
MsgBox "Conversion Completed"