Relink / Swap Between Live, Test And Local Data, Any Version
Jan 8 2007, 08:09 AM
UdderAccess Admin + UA Ruler
Joined: 27-April 02
From: Upper MI
Even though this was designed and attached in Access 97 format (Access 2007 and 2010 now tested and included), it works in A2K, A2K2, A2K3, A2K7 and A2K10 as well. This is now version 0.08 - supporting the linking of Excel files ! Tested for SQL Server files - but you must make sure the proper connectivity and permissions to ensure flexible re-connectivity.
Glenn Lloyd was kind enough to test this in A2K and found an issue with the DAO references, so modifications were made to set the recordset declarations to DAO.Recordset.
Oftentimes when testing front-ends against large, live data sets, I like to link to a set of test data tables rather than the live records that are so heavily depended upon. No point in risking the company's "live" data. So this little utility was designed to do just that. By making a copy of the company's live data on another server or directory, I can now relink to that copy, test my db and not put the company's live data at risk. Nothing new so far. A good practice to exercise.
What's new is how this utility works in a front-end that is under constant change and re-development. With three tables and two forms to import into a working Access application, (There is a 3rd form, "frmAutoLinker", that is not required and only works with the "Syslinks" and "Syslinks_TEST" tables.) This utility does a bit more than the previous Relinkers I posted here. By having three tables to store the linked table names and directory paths, one can serve as the storage for "LIVE" data (SysLinks) and the other serves to store the tables and directory paths of the "TEST" data (SysLinks_TEST) as well as a third table for storing "LOCAL" data (SysLinks_LOCAL) links for same machine testing as well. This makes it fast and easy to switch between Live, Test and Local data. Both Foreign and Local table names are supported. (Foreign/Local table name support is handy when you need to link to tables from different databases that are named similarly.)
(you can click the image to download as well)
When first brought into an active project, the three tables are empty, and the frmLinker shows this by displaying no table names and by unhiding a "Get Links" button. Clicking the "Get Links" button copies all the table names and directory paths of all tables currently linked to the front-end of the application. If there are any records in the "SysLinks" table, the "Get Links" button does not display.
The "DATA" option group toggles between the data tables (SysLinks_LIVE, SysLinks_TEST & SysLinks_LOCAL - one of the three is always set to "SysLinks") and renames the current "SysLinks" table to it's appropriate set name, then renames the selected data link table to "SysLinks"; then requeries the subform to display the data. The asubform ALWAYS has "SysLinks" as the name of its RecordSource.
Mind you, SysLinks_Test and SysLinks_Local are 'hidden' objects. If you do not see them when you go to import, go to select "Tools\Options" from the menu bar, then in the "View" tab of the Options dialog, select the 'Hidden objects" and "System objects" so they are both checked , then click on the "OK" button. You should then be able to see the two hidden tables: Syslinks_TEST & Syslinks_LOCAL
The blue text below the buttons displays the current status of currently linked tables, if any exist.
The "Delete Linked Tables" will do exactly that. Delete all linked tables from the database. (It doesn't delete the tables in the remote container, just the links within the current front-end.) And with all the links removed, the utility is now ready to relink all the tables listed in the subform.
The "Rebuild Linked Tables" will re-link all the tables listed in the subform. If the blue text below the buttons is not displaying "Nothing ! - No Links Exist Right Now.", the user is prompted to be sure adding the links in the list is OK.
Initially, because the "SysLinks", "SysLinks_TEST" and "SysLinks_LOCAL" tables are empty, they will need to be populated, which means that the links for your live data must first be made manually. Then the "SysLinks" table can be populated with the "Get Links" button. Once the live data links are stored, you then click the "Use TEST Data Instead" button and then click the "Delete Linked Tables" button to clear out all the exisitng live data table links. OK, so now your links are removed and your subform is displaying no data, but your "LIVE" data links are safely stored in the "SysLink_LIVE" table. (Remember, the "Use TEST Data Instead" button is now re-captioned to read:"Use LIVE Data Instead".) So, the first time you link to the test data, you have to do it manually. Once you have done that, you can then use the "Get Links" button to populate the subform (which is using the "TEST" data table - the "LIVE" data table has been renamed to "SysLinks_LIVE"). If you close the form when you manually link to your test data tables, the "Get Links" button will no reappear, because at form load time, that button is hidden if any tables are linked or if any data is in the currently named "SysLinks" table. And because there are linked tables but nothing in the currently named "SysLinks" table, the "Get Links" button will not appear at form load time. If you toggle the "Set To Use XXX Data" button twice, the "Get Links" button will appear if there is no data in current "SysLinks" table.
So, once the tables are populated with the proper Live, Test and Local table link data, switching back and forth between Live, Test and Local data is fast and simple. Just toggle the "Data" option group, click the "Delete Linked Tables" button, then clcik the "Rebuild Linked Tables" button.
Of course, all of this can brought into further automation, by consolidating the code of three buttons into one and properly ordering the sequence of events, but for purposes of getting the ball rolling with this utility, This type of functionality is found in the frmAutoLinker form.
There is also a command button ("Empty the SysLinks Table") which has its Visible property set to "No". When enabled and actuated, this button will simply delete all records in the currently named "SysLinks" table.
The "Order and "Use" fields can be useful but are not required.
Also, clicking once on any of the column labels wil 'sort' the data by the field selected. A second click on the same field will reverse the sort order. And clicking on the little arrow above a sorted column will turn the sorting off.
On a final note, it is NOT reccommended to link tables using mapped drive paths. I suggest UNC drive referencing for this utility. eg: "M:\Data\mydb.mdb" is a 'Mapped' drive path spec; "\\Server01\Data\mydb.mdb" is a UNC drive path spec.
Feel free to modify it to suite your needs.
Updated 9-26-2012 - the versions for 2007 and 2010 now include both "mdb" and "accdb" file types in the code.
relinker8.zip ( 262.76K )Number of downloads: 1277
(zip updated on Sept 26, 2012 - original download count was 1,893)
Sep 26 2012, 02:06 PM
Joined: 26-September 12
A great utility! Thanks very much!
I had to change line 245 in cmdBuildLinks_Click
If Right(![ConnectionString], 3) = "mdb" Then
If Right(![ConnectionString], 5) = "accdb" Then
Also the utility doesn't seem to handle attachment type tables, but removing them from Syslinks, Syslinks_LIVE and Syslinks_LOCAL tables seems to work just fine. Any attachments seem to get linked perfectly.
Sep 26 2012, 02:41 PM
UdderAccess Admin + UA Ruler
Joined: 27-April 02
From: Upper MI
DickfromMK - Thanks for the note regarding the file types in the code. Good catch. I must've missed it on the upgrade.
The 2007 & 2010 files in the attachment in the original post have been modified to provision ACCDB and XLSX files.
Dec 12 2012, 04:50 PM
Joined: 10-December 12
I added the following so that it would not delete my DSN links to Quickbooks.
Public Function DeleteTableLinks() As Boolean
50 If tblLinked.Connect <> "" And Left(tblLinked.Connect, 4) <> "ODBC" Then
Dec 30 2016, 03:40 PM
Joined: 23-April 15
I modified the code to work with password protected back end on this and added a field in the table called pxc to store a pass word in case anyone runs across this issue.
On Error GoTo err
Dim rstLnk As Recordset
Dim plt As String
Dim dbs As DAO.Database
100 If Me.txtLinkedTo.Value <> "Nothing ! - No Links Exist Right Now." Then
110 ' If MsgB'ox("There are tables already linked to this front-end." & vbCrLf & vbCrLf & _
"Are you sure you want to continue?", vbQuestion + vbYesNo, "Are You Sure?") = vbNo Then
160 DoCmd.Hourglass True
170 DoCmd.SetWarnings False
220 Set rstLnk = CurrentDb.OpenRecordset("SELECT SysLinks.* FROM SysLinks;")
230 With rstLnk
240 Do While Not .EOF
245 If Right(![ConnectionString], 3) = "mdb" Then
Set dbs = DBEngine.OpenDatabase(![ConnectionString], False, False, ";pwd=" & ![pxc])
250 DoCmd.TransferDatabase acLink, "Microsoft Access", ![ConnectionString], acTable, ![TableName], ![TableName]
Set dbs = Nothing
255 ElseIf Right(![ConnectionString], 3) = "xls" Then
260 DoCmd.TransferSpreadsheet acLink, 8, ![TableName], ![ConnectionString]
270 If Not .EOF Then
330 DoCmd.SetWarnings True
340 DoCmd.Hourglass False
350 'MsgBox "You've Successfully relinked to the data file.", vbOKOnly + vbInformation, "Relink Success!"
MsgBox "Error: " & err.Number & " - " & err.Description & vbCrLf & vbCrLf & "On frmLinker form on line " & Erl & " of 'cmdBuildLinks'.", vbInformation + vbOKOnly, "Error !"
|Search Top Lo-Fi||24th January 2017 - 04:41 PM|