LINKING AND UNLINKING TABLES THROUGH VBA

Various methods are available to link tables manually and automatically. In the following sections, you see how to link a back end, based on it being in the current folder or in another folder.

Linking and Unlinking Tables in a Jet Back End Located in the Application's Folder

In section 4, the ap_AppInit() function checks to see whether the back-end database is in the same folder as the front end and, if so, links the tables automatically.

'-- Section 4: If the Data MDB is found in the App Directory,
'--            link the files.
 If Dir(pstrAppPath & "" & pstrBackEndName) = pstrBackEndName Then

    ap_LinkTables dbLocal, dynSharedTables, pstrAppPath & "" & _
                   pstrBackEndName
    pstrBackEndPath = pstrAppPath

 Else

The code in section 4 calls the ap_LinkTables subprocedure and passes four arguments:

• dbLocal, the local database

• dynSharedTables, the dynaset containing the list of tables to link

• pstrAppPath and pstrBackEndName, the front-end database path concatenated to the back-end database name

One last task needs to be completed in this code section: to store the front end's file path into the global variable pstrBackEndPath.

It's time to look at what's done to relink to a new back end through the ap_LinkTables routine. Listing 26.8 shows the code for ap_LinkTables.

Listing 26.8. VideoApp.mdb: Relinking Tables in the dynSharedTables Recordset

Public Sub ap_LinkTables(dbLocal, dynSharedTables, strDataMDB As String)

    Dim tdfCurrent As TableDef
    Dim flgAddTable As Boolean
    Dim intTotalTbls As Integer
    Dim intCurrTbl As Integer

    On Error GoTo Err_LinkTables

    '-- Get the total number of linked tables, then display the
    '-- progress meter.
    dynSharedTables.MoveLast
    intTotalTbls = dynSharedTables.RecordCount
    dynSharedTables.MoveFirst

    SysCmd acSysCmdInitMeter, "Linking Tables....", intTotalTbls

    intCurrTbl = 1

    Do Until dynSharedTables.EOF
        '-- Update the progress meter
        SysCmd acSysCmdUpdateMeter, intCurrTbl

        '-- Attempt to open the current link
        On Error Resume Next
        Set tdfCurrent = dbLocal.TableDefs(dynSharedTables!TableName)

        flgAddTable = Err.Number

        On Error GoTo Err_LinkTables

        '-- If there was an error, create the link from scratch,
        '-- otherwise, just update the connect string
        If flgAddTable Then

            Set tdfCurrent = _
               dbLocal.CreateTableDef(dynSharedTables!TableName)
            tdfCurrent.SourceTableName = dynSharedTables!TableName
            tdfCurrent.Connect = ";DATABASE=" & strDataMDB
            CurrentDb.TableDefs.Append tdfCurrent

        Else

            tdfCurrent.Connect = ";DATABASE=" & strDataMDB
            tdfCurrent.RefreshLink

        End If

        dynSharedTables.MoveNext
        intCurrTbl = intCurrTbl + 1

    Loop

Exit_LinkTables:

    SysCmd acSysCmdRemoveMeter

    Exit Sub

Err_LinkTables:

    Resume Exit_LinkTables

End Sub

This code is all that's needed to link or relink tables in your application. Here's what this routine does:

In prior versions, the TransferDatabase method was used instead of the TableDefs collection. However, I've found that using the TableDefs collection results in better performance. The TransferDatabase method is one of the more powerful commands when importing, exporting, and linking tables. You use this command for these actions with most of the different file formats. The actual syntax of TransferDatabase is as follows:

DoCmd.TransferDatabase TransferType,
							DatabaseType,
							DatabaseName,
							ObjectType,
							Source,
							Destination,
							StructureOnly,
							StoreLogin
						

This method is used here because of the straightforward way (one line of code) in which the task of linking is performed. The TransferDatabase method uses these arguments:

• TransferType can be acImport, acExport, or acLink.

• DatabaseType can be one of many types, including Microsoft Access, Microsoft FoxPro, and dBASE.

• DatabaseName is the name of the database, including the full path. For Xbase files, it's the full table name, such as C:Examples est.dbf. With Access databases, it's the full .mdb name.

• ObjectType, when you're importing and exporting, can be any of the Access objects. When you're linking, this parameter is acTable.

• Source is the table name, as it is in the source database.

• Destination is the name for the new table, or link. This allows you to have a different name between the original table in the back end and the linked entry in the front end. This is useful in case you already have a table in the front end with the same name as the back end.

• StructureOnly is used with importing and exporting only, in case you want to import a file structure but no data.

• StoreLogin is used when linking to SQL Server databases to allow you to store the SQL Server logon with the table link. This is so that when you run queries, SQL Server doesn't request the logon every time they're run.

Other ways to establish or re-establish links of tables include not deleting the TableDef, and just updating the Connect property of the TableDef. The problem comes when a table is added to the list; you must jump through more hoops to add the TableDef to the TableDefs collection.

Finding the Jet Back End with the OpenFile API Call

In section 5 of the ap_AppInit() function, things are simple because the back-end database was in the same folder as the application. But most of the time this won't be the case. Frequently, the back end of the application will be out on a network drive and the front end will be on a local drive. Section 5 of ap_AppInit() covers this situation:

   '-- Section 5: Allow the user to locate the BackEnd MDB
   If Not ap_LocateBackend(dbLocal, dynSharedTables, _
          strCurrError) Then
      flgLeaveApplication = True
   End If

End If

Listing 26.9 shows the code for the ap_LocateBackend() function.

Listing 26.9. VideoApp.mdb: Locating the Back End with a Call to an API Routine

Public Function ap_LocateBackend(dbLocal, dynSharedTables, _
       strCurrError) As Boolean

    Dim ocxDialog As Object

    ap_LocateBackend = True

    DoCmd.Echo True
    Beep

    If MsgBox("A problem has occurred accessing the linked tables." & _
         vbCrLf & vbCrLf & "The error was: " & strCurrError & vbCrLf & _
         vbCrLf & "Would you like to locate the backend?", vbCritical + _
         vbYesNo, "Error with Backend") = vbYes Then
       Dim strFileName As String
       strFileName = ap_FileOpen("Locate backend database", _
          pstrBackEndName)

       If Len(strFileName) <> 0 Then
          DoEvents
          ap_LinkTables dbLocal, dynSharedTables, strFileName
          pstrBackEndPath = Left$(strFileName, _
                                InStrRev(strFileName, ""))

       Else

          ap_LocateBackend = False

       End If

    Else

       ap_LocateBackend = False

    End If

Exit_ap_LocateBackend:
    Exit Function

Error_ap_LocateBackend:
    ap_LocateBackend = False
    Resume Exit_ap_LocateBackend

End Function

First, the user is told that the back end can't be found. The user then is asked whether he wants to locate it (see Figure 26.10). If the user clicks Yes, the Locate Backend Database dialog appears (see Figure 26.11).

The following code line calls ap_FileOpen, an API wrapper function located in modWindowsAPICalls:

strFileName = ap_FileOpen("Locate backend database", pstrBackEndName)

Listing 26.10 shows the code for the ap_FileOpen() function.

Listing 26.10. VideoApp.mdb: Making the API Call

Public Function ap_FileOpen(Optional strTitle As String = "Open File", _
                     Optional strFileName As String = "", _
                     Optional strFilter As String = "") As String

   Dim OpenFile As OPENFILENAME
   Dim lngReturn As Long
   If Len(strFileName) = 0 Then
      strFileName = String(257, 0)
   End If

   If Len(strFilter) = 0 Then
      strFilter = "Access Databases (*.mdb)" & Chr(0) & _
                    "*.mdb" & Chr(0)
   End If

   OpenFile.lStructSize = Len(OpenFile)

   OpenFile.lpstrFilter = strFilter
   OpenFile.nFilterIndex = 1
   OpenFile.lpstrFile = strFileName + _
            Space(255 - Len(strFileName))
   OpenFile.nMaxFile = 255
   OpenFile.lpstrFileTitle = strFileName
   OpenFile.nMaxFileTitle = OpenFile.nMaxFile
   OpenFile.lpstrInitialDir = CurrentProject.Path
   OpenFile.lpstrTitle = strTitle
   OpenFile.flags = 0

   api_GetOpenFileName OpenFile

   ap_FileOpen = Left(OpenFile.lpstrFile, _
            InStr(OpenFile.lpstrFile, Chr$(0)) - 1)

End Function

An explanation of this code and other API calls can be found in Chapter 15, “Using API Calls to Extend the Power of Access.”