The following FAQ provides the basic concepts for linking a table at runtime in Access 2000. I developed this in response to needing to re-connect to a back-end Access Database when the front end was distributed as Access Runtime(thus limiting the user to do any editing of the database).
This FAQ is broken into 3 different sections: 1. Get path and file name of Database to link to. 2. Use the TransferDatabase method to link a table. 3. Sources
note: this code needs to be modified before reuse. note to tek-tips moderators: I could not post to the FAQ section. There seems to be some kind of code error when I click on "Preview FAQ" button.
1. Get path and file name of Database to link to. This uses the API for a general open file dialog box in Windows. The Function PromptFileName returns a string that is the full Windows path and file name of the file the user selected through the open file dialog box. The coding and logic comes from the following website: http://www.vbapi.com/ref/g/getopenfilename.html where the original writer offers the code for free provided his name is cited.
' Parts of this code are liscensend and the information can be found ' on the following web page: ' http://www.vbapi.com/ref/g/getopenfilename.html
' Declarations and such needed for the example: ' (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 End Type 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 With filebox ' 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 End With
' 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 End If
'return the string of the file name PromptFileName = fname
2. Use the TransferDatabase method to link a table. (See Access Help for the specifics on this method) This is the crude method. This part involves capturing the names of the tables in the Database, Deleting their links, and re-linking them with the file chosen in step 1.
note: step 2 is in no way optimized nor does it do any checking. It literally grabs all table names, deletes them, and assumes that the back-end selected by the user has all the same tables that were deleted. If you have a mix of internal tables and linked tables, use your own method of findng table names other than the loop shown below.
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 End If Next obj