Link front end to back end at run time based on user input

[lwwhite]

Access 2003. I am maintaining 5 almost identical databases (long story). Each of them is split into a back end and front end. While individual maintenance of the back ends is inevitable, I would really like to maintain just one front end that users could use to link to the back end of their choice. The problem is that they would not be comfortable using Access's table-linking function and I would not want them to. I have an Enter Defaults window that requires users to select the product they are working with. I would like to use this parameter to drive some code that selects and links the front end dynamically to the appropriate back end at run time, transparently to the user. Is this possible with Access? If so, can you provide an example? I am no VB whiz, but if I have a basic example, I can usually "reverse engineer" it and figure out where to go from there.

Thanks for any help. You guys are an invaluable resource and I'm glad you're here!

LW

[ADezii]

Access 2003. I am maintaining 5 almost identical databases (long story). Each of them is split into a back end and front end. While individual maintenance of the back ends is inevitable, I would really like to maintain just one front end that users could use to link to the back end of their choice. The problem is that they would not be comfortable using Access's table-linking function and I would not want them to. I have an Enter Defaults window that requires users to select the product they are working with. I would like to use this parameter to drive some code that selects and links the front end dynamically to the appropriate back end at run time, transparently to the user. Is this possible with Access? If so, can you provide an example? I am no VB whiz, but if I have a basic example, I can usually "reverse engineer" it and figure out where to go from there.

Thanks for any help. You guys are an invaluable resource and I'm glad you're here!

LW

What you are requesting is definately possible using VBA. Give me a little time,and I'll post a code template for you.

[ADezii]

Access 2003. I am maintaining 5 almost identical databases (long story). Each of them is split into a back end and front end. While individual maintenance of the back ends is inevitable, I would really like to maintain just one front end that users could use to link to the back end of their choice. The problem is that they would not be comfortable using Access's table-linking function and I would not want them to. I have an Enter Defaults window that requires users to select the product they are working with. I would like to use this parameter to drive some code that selects and links the front end dynamically to the appropriate back end at run time, transparently to the user. Is this possible with Access? If so, can you provide an example? I am no VB whiz, but if I have a basic example, I can usually "reverse engineer" it and figure out where to go from there.

Thanks for any help. You guys are an invaluable resource and I'm glad you're here!

LW

Here is your Code Template based on the following assumptions:
__1. Combo Box containing the Back End Database Names is called cboLink
__2. The 5 Back End Database Names are Back End 1 to Back End 5
__3. Notice that this code is called from the AfterUpdate Event of cboLink
__4. Any questions, feel free to ask

Dim strDBPath As String, strTableName As String, LinkedDB As Database
Dim tbfLinked As TableDef, MyDB As DAO.Database, Mytdf As DAO.TableDef
Dim Response As Integer, strPrompt As String
 
strPrompt = "Do you wish to Re-Link to the " & Me![cboLink].Value & " Database?"
Response = MsgBox(strPrompt, vbQuestion + vbYesNo + vbDefaultButton1, "Relink Tables")
  If Response = vbNo Then Exit Sub
 
Set MyDB = CurrentDb()
 
Select Case Me![cboLink]
  'e.g. C:\Databases\MASTER\Test.mdb
  Case "Back End 1"
    strDBPath = "Path to Back End 1"
  Case "Back End 2"
    strDBPath = "C:\Dell\Employees.mdb"     'Test Path
  Case "Back End 3"
    strDBPath = "Path to Back End 3"
  Case "Back End 4"
    strDBPath = "Path to Back End 4"
  Case "Back End 5"
    strDBPath = "Path to Back End 5"
End Select
 
' Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
 
' Open Database object from saved Microsoft Jet database
' for exclusive use (True)
Set LinkedDB = wrkJet.OpenDatabase(strDBPath, True)
 
'Delete all existing Tables, except System, in preparation for re-linking.
'Also, Delete 'only' Linked Tables (Len(Connect) > 0)
For Each Mytdf In MyDB.TableDefs
  If Left$(Mytdf.Name, 4) <> "MSys" And Len(Mytdf.Connect) > 0 Then
    MyDB.TableDefs.Delete Mytdf.Name
  End If
Next
 
For Each tbfLinked In LinkedDB.TableDefs
  'Filter out the System Tables
  If Left$(tbfLinked.Name, 4) <> "MSys" Then
    'Do the actual Linkage
    DoCmd.TransferDatabase acLink, "Microsoft Access", strDBPath, acTable, tbfLinked.Name, tbfLinked.Name, False
  End If
Next
LinkedDB.Close
End Sub