Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access 2000: How Do I relink a table during runtime?

Tables and Relationships

Access 2000: How Do I relink a table during runtime?

by  ahmun  Posted    (Edited  )
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.


Code:
[color green]
Code:
' Parts of this code are liscensend and the information can be found
[/color]
Code:
[color green]
Code:
' on the following web page:
[/color]
Code:
[color green]
Code:
' http://www.vbapi.com/ref/g/getopenfilename.html
[/color]
Code:
[color green]
Code:
' Declarations and such needed for the example:
[/color]
Code:
[color green]
Code:
' (Copy them to the (declarations) section of a module.)
[/color]
Code:
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
[color green]
Code:
' open file dialog structure
[/color]
Code:
    Dim fname As String
[color green]
Code:
' filename the user selected
[/color]
Code:
    Dim result As Long
[color green]
Code:
' result of opening the dialog
[/color]
Code:
[color green]
Code:
' Configure how the dialog box will look
[/color]
Code:
    With filebox
[color green]
Code:
' Size of the structure.
[/color]
Code:
        .lStructSize = Len(filebox)
[color green]
Code:
' Handle to window opening the dialog.
[/color]
Code:
        .hwndOwner = 0 'Me.Hwnd
[color green]
Code:
' Handle to calling instance (not needed).
[/color]
Code:
        .hInstance = 0
[color green]
Code:
' File filters to make available: Access Databases and All Files
[/color]
Code:
        .lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
            vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
            vbNullChar & vbNullChar
[color green]
Code:
'.lpstrCustomFilter is ignored -- unused string
[/color]
Code:
        .nMaxCustomFilter = 0
[color green]
Code:
' Default filter is the first one (Text Files, in this case).
[/color]
Code:
        .nFilterIndex = 1
[color green]
Code:
' No default filename.  Also make room for received
[/color]
Code:
[color green]
Code:
' path and filename of the user's selection.
[/color]
Code:
        .lpstrFile = Space(256) & vbNullChar
        .nMaxFile = Len(.lpstrFile)
[color green]
Code:
' Make room for filename of the user's selection.
[/color]
Code:
        .lpstrFileTitle = Space(256) & vbNullChar
        .nMaxFileTitle = Len(.lpstrFileTitle)
[color green]
Code:
' Initial directory is C:\.
[/color]
Code:
        .lpstrInitialDir = "C:\" & vbNullChar
[color green]
Code:
' Title of file dialog.
[/color]
Code:
        .lpstrTitle = "Select a File" & vbNullChar
[color green]
Code:
' The path and file must exist; hide the read-only box.
[/color]
Code:
        .flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
[color green]
Code:
' The rest of the options aren't needed.
[/color]
Code:
        .nFileOffset = 0
        .nFileExtension = 0
[color green]
Code:
'.lpstrDefExt is ignored -- unused string
[/color]
Code:
        .lCustData = 0
        .lpfnHook = 0
[color green]
Code:
'.lpTemplateName is ignored -- unused string
[/color]
Code:
    End With
[color green]
Code:
' Display the dialog box.
[/color]
Code:
    result = GetOpenFileName(filebox)
    If result <> 0 Then
[color green]
Code:
' Remove null space from the file name.
[/color]
Code:
        fname = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
[color green]
Code:
'Debug.Print "The selected file: "; fname
[/color]
Code:
    End If
[color green]
Code:
'return the string of the file name
[/color]
Code:
    PromptFileName = fname
    
End Function

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.

[color red]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.[/color]

Code:
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
[color green]
Code:
' Search for open AccessObject objects in AllTables collection.
[/color]
Code:
    For Each obj In dbs.AllTables
        strTableName = obj.Name
[color green]
Code:
'Some other objects in the .AllTables Collection are not tables
[/color]
Code:
        If Not (Left(strTableName, 4) = "MSys") Then
[color green]
Code:
'1. Delete the current link
[/color]
Code:
            DoCmd.DeleteObject acTable, strTableName
[color green]
Code:
'This MsgBox was used to debug.  Comment out or delete as you like
[/color]
Code:
[color green]
Code:
'2. Re-Link the table
[/color]
Code:
            MsgBox "Linking " & strTableName & "."
            DoCmd.TransferDatabase acLink, "Microsoft Access", strFileName, _ 
                acTable, strTableName, strTableName
        End If
    Next obj

Exit_cmdLinkTables_Click:
    Exit Sub

Err_cmdLinkTables_Click:
    MsgBox Err.Description
    Resume Exit_cmdLinkTables_Click

End Sub

3. Sources
[ul][li]Threads I found useful and led me to the solutions: thread700-194277, and thread700-93254 [/li]
[li]Microsoft's version (works for Access 97): http://support.microsoft.com/default.aspx?scid=kb;EN-US;q154397[/li]
[li]Download page for Microsoft's solutions (solutions.mdb): http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/html/mdbdownload.asp[/li]
[li]Source Code Example for solution in Access 2000: http://www.vbapi.com/ref/g/getopenfilename.html[/li]
[/ul]
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top