I'm currently designing a front end database, which links to some back end tables. I'm trying to find a way of allowing end users (who will be using runtime) to select which back end file they link to.
Ideally I need them to select from a combo box, or from a command button which data tables they link to.
I'm struggling with the VB help files as to how to change the Linked Table path and filename.
I use this to relink tables in a runtime DB. Not sure if this will help you, as it doesn't allow users to pick and choose which tables, just the location of the backend DB. Maybe you can modify it to meet your needs. I'm not the author, but have tweeked it a bit myself.
sub to delete and relink
Private Sub cmdLinkTables_Click()
On Error GoTo Err_cmdLinkTables_Click
Dim strFileName, strTableName As String
strFileName = PromptFileName()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
strTableName = obj.Name
'Some other objects in the .AllTables Collection are not tables
If Not (Left(strTableName, 4) = "MSys") Then
'1. Delete the current link
DoCmd.DeleteObject acTable, strTableName
'This MsgBox was used to debug. Comment out or delete as you like
'2. Re-Link the table
'MsgBox "Linking " & strTableName & "."
DoCmd.TransferDatabase acLink, "Microsoft Access", strFileName, _
acTable, strTableName, strTableName
Then I use this to allow is user to browse for the BE DB. (Can copy this into new module).
Option Compare Database
' (Copy them to the (declarations) section of a module.)
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustomFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_PATHMUSTEXIST = &H800
Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long
Public Function PromptFileName() As String
Dim filebox As OPENFILENAME ' open file dialog structure
Dim FName As String ' filename the user selected
Dim result As Long ' result of opening the dialog
' Configure how the dialog box will look
' Size of the structure.
.lStructSize = Len(filebox)
' Handle to window opening the dialog.
.hwndOwner = 0 'Me.Hwnd
' Handle to calling instance (not needed).
.hInstance = 0
' File filters to make available: Access Databases and All Files
.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
vbNullChar & vbNullChar
'.lpstrCustomFilter is ignored -- unused string
.nMaxCustomFilter = 0
' Default filter is the first one (Text Files, in this case).
.nFilterIndex = 1
' No default filename. Also make room for received
' path and filename of the user's selection.
.lpstrFile = Space(256) & vbNullChar
.nMaxFile = Len(.lpstrFile)
' Make room for filename of the user's selection.
.lpstrFileTitle = Space(256) & vbNullChar
.nMaxFileTitle = Len(.lpstrFileTitle)
' Initial directory is C:\.
.lpstrInitialDir = "C:\" & vbNullChar
' Title of file dialog.
.lpstrTitle = "Select a File" & vbNullChar
' The path and file must exist; hide the read-only box.
.flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
' The rest of the options aren't needed.
.nFileOffset = 0
.nFileExtension = 0
'.lpstrDefExt is ignored -- unused string
.lCustData = 0
.lpfnHook = 0
'.lpTemplateName is ignored -- unused string
' Display the dialog box.
result = GetOpenFileName(filebox)
If result <> 0 Then
' Remove null space from the file name.
FName = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
'Debug.Print "The selected file: "; fname
'return the string of the file name
PromptFileName = FName
About the relathionships: you don't need to delete the linked table to change it's source. As long as the table name doesn't change, you shouldn't need to change the relationship.
Here's a snippet of code from something kicking around on my desktop that allows the user to change the linked table locations.
Other code in the form shows the user where the tables are currently located and allows then to choose a new location using the Access file open dialog box.
I have a native table (in the FE - not linked) that contains the names of the tables that need to be linked - that's what the recordset is doing, getting the table names.
Function SetNew() As Boolean
Dim rsDat As DAO.Recordset
Dim tblX As TableDef
Dim tblY As TableDef
Dim sC As String
Dim sT As String
Dim blDone As Boolean
Dim iC As Integer
Dim iT As Integer
Dim iI As Integer
On Error GoTo Err_Function
blDone = False
Set rsDat = CurrentDb.OpenRecordset("Select * From LinkTables")
iT = rsDat.RecordCount
iC = 0
sC = ";DATABASE=" & Me.txtNew
lblX.Caption = "Table " & Format(iC + 1) & " of " & Format(iT) & " '" & rsDat(0) & "'" & vbCrLf & "Connect='" & sC & "'"
For Each tblX In CurrentDb.TableDefs
If tblX.Name = rsDat(0) Then Set tblY = tblX
tblY.Connect = sC
iC = iC + 1
Loop Until rsDat.EOF
blDone = True
Set rsDat = Nothing
Set tblX = Nothing
Set tblY = Nothing
SetNew = blDone
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
"Table " & Format(iC) & " of " & Format(iT) & " '" & rsDat(0) & "'", vbOKOnly, "Error"
Often, I'll have the FE check to make sure the BE is visible by grabbing the linked table location and using the DIR command to "touch" it. If the BE is not found, then I take action, depending on the app. In this case, I allow the user to find it and reconnect. In other instances, I tell the user "Verify network connection; log out and back in, etc."
On Error GoTo Change_Links_Error
Dim DB As Database
Dim tbl As TableDef
Dim Site As String
Dim Error As Integer
Dim np As String
Dim i As Integer
Dim tbln As String
Dim lnk As String
Dim folderspec As String
Dim fs, f, f1, fc, s, fa
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\Inventory Software\Central Files")
Set fc = f.Files
For Each f1 In fc
fa = f1.Type Like "*Access*"
If fa = True Then
s = s & Mid(f1.Name, 7, Len(f1.Name) - 10) & vbCrLf
MsgBox s, vbInformation, "The Following Sites are setup: "
Set DB = CurrentDb()
Site = InputBox("Which Site do you want to connect to?", "Site Required")
If Site = "" Then End
np = "C:\Inventory Software\Central Files\MIP - " & Site & ".mdb"
Dim stDocName As String
Dim stLinkCriteria As String
For i = 0 To DB.TableDefs.Count - 1
tbln = DB.TableDefs(i).Name
Set tbl = DB.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
tbl.Connect = ""
tbl.Connect = ";DATABASE=" & np
Error = MsgBox("Invalid Site ID", vbRetryCancel, "Error")
If Error = 4 Then Run (ChangeLinks()) Else Application.CloseCurrentDatabase
I then created a little one line macro that runs the ChangeLinks function:
Function Name: ChangeLinks()
Basically the function goes to the directory specified in:
Set f = fs.GetFolder("C:\Inventory Software\Central Files")
and brings up a little message box listing the names of all the Access files
In the next prompt box, you enter the filename, and this assigns the new path and filename to the following variable:
thanks guys this is just why i have fallen in love with this forum
I agree this is the best solution for a person with my level......
i have implemented the linking though usinG DEV ASHISH's code which i copied straight and called the function on the on load function of my main form
BUT i wasnt really satisfied as i didnt understand it all clearly and wont be able to write it myself again....this code now i understand and maybe devs code is also on the same lines - i think only it caters to a whole lot of other situations-given time i will be able to......understand and implement it straight out next when i need it and further customise it.