Module: JetLinkedTables in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

' Example of modJetLinkedTables
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modJetLinkedTables()
  ' Comments: Example of the modJetLinkedTables module to link to external data sources from the current Access database.
  '           See the results in the Immediate Window.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const mcstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"

  Const cstrSampleDatabase As String = mcstrSamplePath & "SAMPLE.MDB"
  Const cstrSampleTable As String = "Categories"

  Dim strResult As String

  Debug.Print "Testing the modJetLinkedTables Module: "

  ' If this database already exists in the sample directory, first delete the file, and then recreate it
  On Error Resume Next
  DoCmd.DeleteObject acTable, "Linked_Categories"
  DoCmd.DeleteObject acTable, "Linked_dBASE5"
  DoCmd.DeleteObject acTable, "Linked_Contacts"
  DoCmd.DeleteObject acTable, "Linked_Inbox"
  On Error GoTo 0

  ' Create a link to the sample Jet table from the sample database
  Debug.Print "Creating Jet link..."
  CreateJetLink cstrSampleDatabase, cstrSampleTable, "Linked_Categories"

  ' Create a link to the dBASE5 table
  Debug.Print "Creating dBASE5 link..."
  CreateISAMLink mcstrSamplePath, "dBASE5.dbf", "Linked_dBASE5", mjlTypedBASE5

  ' Create a link to an Exchange data source. This example creates a link to the Inbox folder in the My Mail folder using the "Microsoft Outlook"
  ' Exchange profile.  Change "My Mail" to the name of the mailbox on the server, or personal folder or public folder. E.g. "Mailbox - Bob Jones"
  Debug.Print "Creating Exchange Inbox link..."
  CreateExchangeLink "My Mail", "Inbox", False, CurrentDb.name, "Microsoft Outlook"

  ' Create a link to an Exchange data source. This example creates a link to the Contacts folder in the My Mail folder using the "Microsoft Outlook"
  ' Exchange profile. Change "My Mail" to the name of the mailbox on the server, or personal folder or public folder. E.g. "Mailbox - Bob Jones"
  ' Note that even though the Microsoft documentation states that the TableType argument should be set to 1, this doesn't work.
  ' Our example specifies False for the fAddressBook parameter to get a TableType value of 0, which works.
  Debug.Print "Creating Exchange data source link..."
  CreateExchangeLink "My Mail", "Contacts", False, CurrentDb.name, "Microsoft Outlook"

  ' Create a link to a SQL server table. This example uses the following parameters:
  '  Database     not specified (uses the default from the DSN)
  '  DSN          Pubs
  '  User         sa
  '  Password     zero-length-string
  '  LinkName     Linked_ODBC_Authors
  '  TableToLink  authors
  If CreateODBCLink("Pubs", "authors", "Linked_ODBC_Authors", "", "sa", "") Then
    Debug.Print "ODBC linked table created"
  Else
    Debug.Print "ODBC linked table NOT created"
  End If

  ' Now that we've created some linked tables, let's use the informational functions of the class.
  CurrentDb.TableDefs.Refresh
  Debug.Print "Linked_Categories linked: " & IsMTableLinked("Linked_Categories")
  Debug.Print "Linked path of Linked_Categories is: " & GetLinkedPath("Linked_Categories")
  Debug.Print "Linked type of Linked_Categories is: " & GetLinkedType("Linked_Categories")
  Debug.Print "Relinking table: " & JetRelinkTable("Linked_Categories", cstrSampleDatabase)
  Debug.Print "Link valid:" & TestLinkedTable("Linked_Categories")
  Debug.Print "Relinking all tables: " & RelinkTables(cstrSampleDatabase)
  Debug.Print "All links valid:" & TestLinkedTables

  strResult = FailedLinkedTables()
  If strResult = "" Then
    Debug.Print "All tables linked properly."
  Else
    Debug.Print "The following linked tables failed: " & strResult
  End If

End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)