Once again, I am compelled to point out that I have seen many and various solutions to this issue, and any number of them may be used with success.  The point of this article is not to show the best way to provide a table linking paradigm but rather to consolidate some of the tools and tricks that I have used over the years to serve as a starting point for your solution. 

The download for this article includes a module named API_Functions , which includes published code from some of the all-time great Access programmers, like Dev Ashish and Terry Kreft.  I have been using this code for years (nearly a decade, now that I think about it) and these code snippets always include an attribution to give credit where credit is due.  The rest of the demo, its forms and module code, is my own.  Feel free to use it in part or in whole, but please retain references to the generous programmers who contributed these valuable VBA functions.

The objective for this article is to demonstrate the following:

To give you an idea of what this demo does, consider the screen shot below.  The main form reads the list of linked objects from the MsysObjects table and gives you three options for relinking:  link only the ODBC to SQL Server tables, Link only the MDB tables or open the Linked Table Manager wizard. The first two options prompt you for a source (DSN for the ODBC request and File Path for the MDB option).  Once selected, the relinking process begins and the progress meter displays the process metadata.  Clicking the Cancel button aborts the process.  Cool, huh?  Now let's see how it is done.

Attach ODBC Method

The process for relinking to an ODBC data source, such as SQL Server Pubs database, requires the following steps:

• Get the connection string
• Loop through the correct set of tables
• Set the tabledef's connect property
• Refresh the link

Now, the most difficult part of this is finding the ODBC connect string. One could hard code a list of Servers and Databases and allow the user to select from the list, but a more flexible solution is to allow the user to link by means of new or preexisting ODBC DSNs.  However, how does one open the ODBC Data Source Administrator dialog box?  Well, here's a little trick for that:

   ' This is a relatively simple way to capture a connect string.
   ' Create a database objects and open an ODBC connection.
   ' Then simply read the connect string and discard the object.
   Set wks = DBEngine.Workspaces(0)
   Set dbs = wks.OpenDatabase("", False, False, "ODBC;")
   m_strConnect = dbs.Connect
   GetDSN = True
   
   ' The results of this call is to set the module level variable, m_strConnect,
   ' to the selected connect string.  It will look something like this ...
   ODBC;DSN= My_Pubs_dsn;Trusted_Connection=Yes;DATABASE=pubs

Now, looping through tables to reset their connect string and refresh the link is a trivial matter. This is the same code we used in the last article, with the one distinction that the connect property is now being set to the user-selected value that was stored in the global variable named  m_strConnect.

   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef
   Set dbs = CurrentDb()
   ' Loop through TableDefs collection, only processing
   ' the table if it already has a Connection property.
   For Each tdf In dbs.TableDefs
      If tdf.Connect <> "" Then
         tdf.Connect = m_strConnect
         tdf.RefreshLink
      End If
   Next

That's all there is to it. We have exposed the ODBC Data Source Administrator dialog box and allowed the user to select a database. This is great for SQL Server tables, but it will not work if you need to connect to another Access database. Let's see how that might be accomplished.

Attach MDB Method

Warning: The following code works ONLY if you import the API_Functions module into your MDB file.

It is beyond the scope of this article to explain the inner-workings of the find-file common dialog box, but I will demonstrate how to use the methods that are included in the API_Functions module included in the download. You will have to play around with it a little, but if you haven't already discovered this little gem, you may find this to be the most valuable piece of code in the download.

  Dim strFileMDB As String
   Dim myOpenFile As gtypCw_OPENFILE
   Dim strExt As String
   Dim strFilter As String
   
   ' Specify the default extension
   With myOpenFile
      .strDefaultExtension = ".mdb"
      .strDialogTitle = "Locate MDB data File"
      .strFilter = CreateFilterString("PubsData.mdb", "*.mdb")
      .strInitialDir = CurrentProject.Path
      .strInitialFile = "PubsData.mdb"
      strFileMDB = GetOpenFileEX(myOpenFile)
      strFileMDB = .strFullPathReturned
   End With
   
   FindMDBFile = myOpenFile.strFullPathReturned

The above snippet is taken from a public function named   FindMDBFile()  which returns the path to the data file selected by the user. Once you have that, you are ready to relink to the MDB tables. The code for refreshing links is almost identical to that above, with one exception: You must prefix the file path with the text  ;DATABASE=   to indicate that you're linking to an Access MDB database.

   strDataFile = FindMDBFile()
   Set dbs = CurrentDb
   ' CONNECT STRING FOR MDB MUST BE IN THIS FORMAT:
   '   ;DATABASE=C:\Development\Crestone\DataCTG.mdb
   strConnect = ";DATABASE=" & strDataFile
   
   For Each tdf In dbs.TableDefs
      If tdf.Connect <> "" Then
         tdf.Connect = strConnect
         tdf.RefreshLink
      End If
   Next

To simplify the article I have posted only the code that does the actual work. Of course, you will need to declare all your variables and you will want to add error handling, especially in the case of table linking. If and when it fails, you will need to know because it is possible that such an error could preclude opening the application at all. Even in the best scenario, where a single table fails to relink, it is important for debugging to know and log such errors.

Display the Progress

The sample code includes a form named frmProgress, shown in the image below.  This form is manipulated by a public sub named UpdateProgressMeter, which updates the display by extending the length of a blue label to give a graphic representation of how far along the process is.  The lower label also accepts a new caption and the form is repainted.

   Public Sub UpdateProgressMeter(ByVal strMsg As String, _
                                  ByVal lngCurr As Long, _
                                  ByVal lngTotal As Long)
      On Error Resume Next
      Dim intPercent As Integer
      intPercent = (lngCurr / lngTotal) * 100
    
      With Forms!frmProgress
         !lblBlue.Caption = "Processing  " & intPercent & "%  Completed"
         !lblTransparent.Caption = "Processing  " & intPercent & "%  Completed"
         !lblBlue.Width = intPercent / 100 * !lblTransparent.Width
         !lblTable.Caption = strMsg
         .Repaint
      End With
      DoEvents
   End Sub

The process for implementing this progress meter is simple and the following steps are illustrated in the code snippet below:

• Open form and initialize values
• Call the Update method with each table processed
• Watch for the Cancel flag and respond accordingly
• Close the form when finished

   ' Count the total number of ODBC linked tables (Type=4)
   ' This will be used to determine how far along we are
   ' with the linking of each new table.
   lngTotal = DCount("*", "MsysObjects", "[Type]=4")
   ' Open the progress form and set its properties
   DoCmd.OpenForm "frmProgress"
   strMsg = "Ready to link to ODBC tables"
   UpdateProgressMeter strMsg, 0, lngTotal
   
   Set dbs = CurrentDb
   For Each tdf In dbs.TableDefs
      If tdf.Connect <> "" Then
         ' Update progress meter with info about current table
         lngCurr = lngCurr + 1
         strMsg = "Linking table " & tdf.Name
         UpdateProgressMeter strMsg, lngCurr, lngTotal
         ForcePause 1
         
         tdf.Connect = m_strConnect: tdf.RefreshLink
         
         ' If user pressed the Cancel button, then the global
         ' variable will be true.  In that case, exit the loop.
         If m_fCancel = True Then Exit For
      End If
   Next
   ' Clean up by closing the form.
   DoCmd.Close acForm, "frmProgress"

Not Exactly A Cut-And-Paste Solution

As with most of my articles, the code is supplied as a framework, not a cut-and-paste solution.  Your table linking schema is probably going to be more complicated than what is shown above.  For that matter, the sample code in the download file is more complicated than what is shown above, since I have to manage two sets of tables:  ODBC and MDB linked.  The test for tdf.Connect <> "" isn't sufficient to distinguish which are linked to SQL Server and which to Access.  However, these matters are usually trivial and business rules easy to implement, once you have the basic idea.

I hope you found this discussion useful.  Download the code and play with it.  The Progress Meter and the Find File code are worth it, even if you aren't currently looking for a table linking solution.  As usual, I welcome your feedback, comments and questions.