472,119 Members | 2,066 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Automatically Relink Frontend to 2 Backends via form

I use two access databases. 1 Rolodex (Client Info and related jobs, etc) - 2 is Timeclock (Employee timesheets with hours, rates etc and all necessary info for creating the various forms and reports for distribution). From time to time I have to take the above home to make changes to the frontend. Right now I use the built in Linked Table Manager and would like to be able to automate the process.
All frontend and backends are located in the same folder at Office (on the server) or my Desktop when Iím at home. Iím only dealing with a total of 2 locations, 2 frontend and 2 backends. (Right now, Iím just testing my home usage (C) not the server in the office (G) as I donít know what the path is yet.
I created a table that contains 4 records:
C:\users\bobbye\desktop\rolodex_be
C:\users\bobbye\desktop\timeclock_be
G:\ rolodex_be
G:\timeclock_be

The Rolodex uses one backend called Rolodex_be. I created a form to select a file and run a relink function that I found online and it works fine.
The Timeclock uses both the Timeclock_be and the Rolodex_be backends. I copied the same function, table and form to Timeclock. I selected the timeclock_be on the form thinking it would run through the function and connect to the timeclock_be (It didnít) and then run it again to connect the Rolodex_be but it didnít work.
How do I get Timeclock FE to connect to both backends? Iím not a programmer so please keep your response as simple as possible

Here is the program I used.
(I made LnkDataBase Public so that I could change the name of the backend as needed via a form
Expand|Select|Wrap|Line Numbers
  1. Sub Relinktables()
  2. Dim dbs As DAO.Database
  3. Dim tdf As DAO.TableDef
  4. Dim strTable As String
  5. Set dbs = CurrentDb()
  6. For Each tdf In dbs.TableDefs
  7.     If Len(tdf.Connect) > 1 Then 'Only relink linked tables
  8.         If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
  9.             If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
  10.                 strTable = tdf.Name
  11.                 dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
  12.                 dbs.TableDefs(strTable).RefreshLink
  13.             End If
  14.         End If
  15.     End If
  16. Next tdf
  17. End Sub
3 Weeks Ago #1

✓ answered by NeoPa

Hi Bobby.

This was harder to find than I'd expected :-(

Let's start anyway with the code to relink a table (originally with a link that points to a single old file) to point to the new version, found in the same folder the current database was opened from. NB. I understand you may have multiple tables linked to such files so there will be more to follow which allows you to handle calling this procedure for all the various tables.
Expand|Select|Wrap|Line Numbers
  1. 'RelinkTable() ensures that the specified (Jet-linked) table, currently linked
  2. '   to one file, will be relinked to a file with the same name, but in the same
  3. '   folder where this current database was opened from.
  4. '   Returns True if the relink was successful (or False if not).
  5. Public Function RelinkTable(ByVal strTable As String, _
  6.                             Optional ByRef dbVar As DAO.Database) As Boolean
  7.     Dim strPath As String, strOldPath As String, strConnect As String
  8.  
  9.     If dbVar Is Nothing Then Set dbVar = CurrentDb()
  10.     With dbVar
  11.         strPath = Left(.Name, InStrRev(.Name, "\"))
  12.         With .TableDefs(strTable)
  13.             If (.Attributes And dbAttachedTable) = 0 Then Exit Function
  14.             strPath = strPath & Mid(.Name, InStrRev(.Name, "\") + 1)
  15.             If Not Exist(strPath) Then Exit Function
  16.             strOldPath = Split(Split(.Connect, "DATABASE=")(1), ";")(0)
  17.             If strPath <> strOldPath Then _
  18.                 .Connect = Replace(.Connect, strOldPath, strPath)
  19.             On Error Resume Next
  20.             Call Err.Clear
  21.             Call .RefreshLink
  22.             RelinkTable = (Err = 0)
  23.         End With
  24.     End With
  25. End Function
NB. I've tried to ensure none of the code here requires access to anything else that I haven't shared so please report at any time if you find the code as written doesn't compile (Before Posting (VBA or SQL) Code provides some basic instructions for compiling etc).

3 9381
NeoPa
32,497 Expert Mod 16PB
Hi Bobby.

Welcome to Bytes.com :-)

I have just the thing for you, but I will need to dig it out as I developed it for a previous client and no longer have access to their servers to get it easily as I used to. That is to say - I don't know if I still have access and wouldn't feel comfortable jumping on their systems without specific permission anyway.

It's late in the day here now but I'll try to get on to it tomorrow. I'm sure I have a usable copy somewhere. Essentially, as long as all linked databases are in the same folder the main project (FE) file is in then it finds them and relinks seamlessly.

If you don't here back before the weekend then feel free to bump this thread to grab my attention and kick me into finding what you need.
3 Weeks Ago #2
NeoPa
32,497 Expert Mod 16PB
Hi Bobby.

This was harder to find than I'd expected :-(

Let's start anyway with the code to relink a table (originally with a link that points to a single old file) to point to the new version, found in the same folder the current database was opened from. NB. I understand you may have multiple tables linked to such files so there will be more to follow which allows you to handle calling this procedure for all the various tables.
Expand|Select|Wrap|Line Numbers
  1. 'RelinkTable() ensures that the specified (Jet-linked) table, currently linked
  2. '   to one file, will be relinked to a file with the same name, but in the same
  3. '   folder where this current database was opened from.
  4. '   Returns True if the relink was successful (or False if not).
  5. Public Function RelinkTable(ByVal strTable As String, _
  6.                             Optional ByRef dbVar As DAO.Database) As Boolean
  7.     Dim strPath As String, strOldPath As String, strConnect As String
  8.  
  9.     If dbVar Is Nothing Then Set dbVar = CurrentDb()
  10.     With dbVar
  11.         strPath = Left(.Name, InStrRev(.Name, "\"))
  12.         With .TableDefs(strTable)
  13.             If (.Attributes And dbAttachedTable) = 0 Then Exit Function
  14.             strPath = strPath & Mid(.Name, InStrRev(.Name, "\") + 1)
  15.             If Not Exist(strPath) Then Exit Function
  16.             strOldPath = Split(Split(.Connect, "DATABASE=")(1), ";")(0)
  17.             If strPath <> strOldPath Then _
  18.                 .Connect = Replace(.Connect, strOldPath, strPath)
  19.             On Error Resume Next
  20.             Call Err.Clear
  21.             Call .RefreshLink
  22.             RelinkTable = (Err = 0)
  23.         End With
  24.     End With
  25. End Function
NB. I've tried to ensure none of the code here requires access to anything else that I haven't shared so please report at any time if you find the code as written doesn't compile (Before Posting (VBA or SQL) Code provides some basic instructions for compiling etc).
2 Weeks Ago #3
NeoPa
32,497 Expert Mod 16PB
Now we simply need to call this for all of your linked tables where this is required. This can be as simple as a string containing a list of the table names but let us know if you're thinking of another way of identifying the tables required.
Expand|Select|Wrap|Line Numbers
  1. 'RelinkHomeTables() ensures all tables that need to be relinked to Jet/ACE
  2. '   databases held in the same folder as the current database get so relinked.
  3. Public Sub RelinkHomeTables()
  4.     Dim lngCount As Long
  5.     Dim strArray() As String
  6.     Dim varItem As Variant
  7.     Dim dbVar As DAO.Database
  8.  
  9.     Set dbVar = CurrentDb()
  10.     strArray = Split("TableA,TableB,TableC,TableD", ",")
  11.     For Each varItem In strArray
  12.         If RelinkTable(strTable:=CStr(varItem), dbVar:=dbVar) Then _
  13.             lngCount = lngCount + 1
  14.     Next varItem
  15.     If lngCount < UBound(strArray) Then
  16.         'You have a problem.
  17.     End If
  18. End Sub
NB. I also updated the earlier post to handle checking first that the new database is not the same as the original one before trying to relink.
2 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by paul b | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.