Ms Access VBA Automatic Relinking Frontend to 2 Backend via form

[bobbyer]

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

 

[dhookom]

I would create a table of tables with the related Rolodex or timeclock and a table of file names with the possible locations. Build this into your code to delink which tables to which back end.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bobbyer]

Wow Duane. I haven't been on a forum for years and you've helped me out a few times back then. So glad to know you're still around.
I will create a table with Timeclock and Rolodex table names and relate it to the location table this weekend. Will I have to change the code? I found the code by searching the internet and I can't say I really understand it except to substitute "LnkDataBase".

 

[bobbyer]

As an alternative, is it possible to insert an if statement inside the for each statement that states if the table isn't in Timeclock, look in file Rolodex? I'm concerned with the back and forth, forgetting to add any new table name to the table listings.

 

[dhookom]

You could try something like the following. You would call the sub with either "C" or "G" as arguments. I'm not sure where in your code, LnkDataBase was getting set.

Sub Relinktables(Optional strCG as String = "G")
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim strTable As String
  Set dbs = CurrentDb()
  Select Case strCG
    Case "G"
       LnkDatabase = "G:\"
    Case Else
       LnkDatabase = "C:\Users\bobbye\desktop\"
  End Select  
  For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
      If tdf.Connect <> ";DATABASE=" & [highlight #FCE94F]LnkDataBase[/highlight] 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

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bobbyer]

Thanks Duane. (I set the LnkDataBase on a form)
I'll try your solution over the weekend

 

[bobbyer]

I looked at the above solution but either I’m just not understanding it (Never worked with tdf’s or TableDefs} or it’s not exactly what I’m looking for. Let me explain it this way
1. When I take the Timeclock FE/BE and the Rolodex FE/BE home from work (G drive) put it on my desktop (C drive).
2. I open the Rolodex FE, go to the Link Table Manager and link to the Rolodex BE and close the FE. (This I was able to do also with the initial posts’ function without using the Link Table Manager, one FE and one BE)
3. I open the Timeclock FE, go to the Link Table Manager, check all the Timeclock BE boxes in the list that link to the Timeclock BE and I’m linked.
4. Staying in the Link Table Manager I then check just the 5 remaining tables in the list that I use from the Rolodex BE and link those tables.
And I’m up and running.
It’s 3 and 4 I’m trying to find a function to replace using the Link Table Manager as I travel back and forth from office to home. I'd like it on a form as it isn't necessary to do any relinking in the regular daily use of the programs at the office. I am researching to try to understand tdf’s and TableDefs, etc.

 

[dhookom]

Ok, I would go back to my first reply and create a couple tables:

tblFileBELocations (four records with the location of the BE tables)
fbeFBEID autonumber primary key
fbeLocation text field containing

1 C:\users\bobbye\desktop\rolodex_be
2 C:\users\bobbye\desktop\timeclock_be
3 G:\rolodex_be
4 G:\timeclock_be

Then a table of table names with three fields
tblTables
tblName
tblHome number linking to tblFileBELocations
tblWork number linking to tblFileBELocations

The linking code would then be something like (caution this is air code on a PC without an Access intstall):

Sub Relinktables(Optional strCG as String = "G")
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim strTable As String
  dim strSQL as String
  Dim rs as DAO.Recordset
  Set dbs = CurrentDb()
  
  Select Case strCG
    'create a recordset of table names and back end locations
    Case "G"
       strSQL = "SELECT tblName, fbeLocation FROM tblTables INNER JOIN tblFileBELocations ON tblWork = fbeFBEID"
    Case Else
       strSQL = "SELECT tblName, fbeLocation FROM tblTables INNER JOIN tblFileBELocations ON tblWork = fbeFBEID"
  End Select  
  Set rs = dbs.OpenRecordset(strSQL)
  With rs
    .MoveFirst
    Do Until .eof
       dbs.TableDefs(!tblName).Connect = ";DATABASE=" & !fbeLocation
       dbs.TableDefs(!tblName).RefreshLink
       .MoveNext
    Loop
    .close
  End With
  Set rs = Nothing
  Set dbs = Nothing
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bobbyer]

Thanks Duane. I think this will work.

 

[Andrzejek]

Duane,

What's the point of passing anything into [tt]strCG[/tt] in the [tt]Sub Relinktables[/tt] since your [tt]Select Case strCG[/tt] creates the same strSQL no matter what is the value of strCG...
[blue]
[tt]strSQL = "SELECT tblName, fbeLocation FROM tblTables INNER JOIN tblFileBELocations ON tblWork = fbeFBEID"[/tt][/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Andy,
You are correct (this will teach me for using notes on my iPad). My choice of field names was also poor

Select Case strCG
    [COLOR=#4E9A06]'create a recordset of table names and back end locations[/color]
    Case "G"
       strSQL = "SELECT tblName, fbeLocation FROM tblTables INNER JOIN tblFileBELocations ON [highlight #FCE94F]tblWork[/highlight] = fbeFBEID"
    Case Else
       strSQL = "SELECT tblName, fbeLocation FROM tblTables INNER JOIN tblFileBELocations ON [highlight #FCE94F]tblHome[/highlight] = fbeFBEID"
  End Select
 [highlight #FCE94F] debug.Print strSQL[/highlight]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Don't beat yourself up, we all make 'Ooops!'es
My job is pointing them

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I figured you might be too busy in the thread "Filter as you type combo box in Access".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bobbyer]

Thanks Duane. Believe it or not I did catch the sql stmt error and the routine works just fine.
Much appreciated.