<

Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

MS Access VBA - converting linked tables to local tables

Published on
8,478 Points
5,478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Join & Write a Comment

Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month