<

Go Premium for a chance to win a PS4. Enter to Win

x

MS Access VBA - converting linked tables to local tables

Published on
8,820 Points
5,820 Views
Last Modified:
Background
What I'm presenting in this article is the result of 2 conditions in my work area:
  1. We have a SQL Server production environment but no development or test environment; and
  2. 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                                     
    MsgBox _
      "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  
  End If
  ‘ 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                                 
      RunCommand acCmdConvertLinkedTableToLocal
    End If
  Next
  ' 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"                                                    
End Sub

Open in new window


 
0
Comment
Author:Don
0 Comments

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month