Manual Table Relinking
March 17, 2006
Last month's article, Automatic Table relinking, demonstrated how to implement table linking behind the scenes, with no user interaction required. However, there are times when we have to trust the users with the awesome responsibility of pointing to the correct database. Perhaps it is only at the initial setup of the client application, or perhaps your program allows users to connect to different databases. Whatever the reason, at times you need to allow for manual table relinking.
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:
1. Relink ODBC tables to Pubs database on a SQL Server
2. Demonstrate how to display a Progress meter with cancel button
3. Show the same process for dynamic linking to an MDB file
4. How to open the Linked Table Manager wizard.
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:
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:
' 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.