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
Sub Relinktables()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 1 Then 'Only relink linked tables
If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
strTable = tdf.Name
dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
dbs.TableDefs(strTable).RefreshLink
End If
End If
End If
Next tdf
End Sub
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
Sub Relinktables()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 1 Then 'Only relink linked tables
If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
strTable = tdf.Name
dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
dbs.TableDefs(strTable).RefreshLink
End If
End If
End If
Next tdf
End Sub