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

'
' Example of CJetLinkedTables
'
' 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_CJetLinkedTables()
  ' Comments: Examples of using the CJetLinkedTables class to work with Microsoft Jet linked tables using DAO in VBA and VB6.
  '           See the Immediate Window for results.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrSampleDatabase As String = cstrSamplePath & "SAMPLE.MDB"
  Const cstrTmpDatabaseName As String = cstrSamplePath & "TMPLINK.MDB"
  Const cstrNewDatabaseName As String = cstrSamplePath & "NEWLINK.MDB"
  Const cstrSampleTable As String = "Categories"
  Dim clsJetLinks As CJetLinkedTables
  Dim strTable As String
  Dim strPath As String
  Dim strType As String

  Set clsJetLinks = New CJetLinkedTables

  ' Before creating any links, we'll create the temporary sample database named by the cstrTmpDatabaseName constant.
  ' This database will receive all the links we create.

  ' If this database already exists in the sample directory, first delete the file, and then recreate it
  On Error Resume Next
  Kill cstrTmpDatabaseName
  Kill cstrNewDatabaseName
  On Error GoTo 0

  ' Create the databases
  Debug.Print "Creating the temporary sample databases..."
  DAO.DBEngine.CreateDatabase cstrTmpDatabaseName, DAO.dbLangGeneral

  DAO.DBEngine.CreateDatabase cstrNewDatabaseName, DAO.dbLangGeneral

  ' Open the database using the class
  Debug.Print "The class is now opening the temporary sample database..."
  clsJetLinks.OpenDB cstrTmpDatabaseName, False, False

  ' Use the database property to get its property values
  Debug.Print "Database version: " & clsJetLinks.Database.Version

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

  ' Create a link to the dBASE5 table
  strTable = "Linked_dBASE5"
  If clsJetLinks.CreateISAMLink(cstrSamplePath, "dBASE5.dbf", strTable, cjlTypedBASE5) Then
    Debug.Print "dBASE5 linked table created."

    ' Use the class functions to get information on the linked table:

    ' Get the path of the linked table
    strPath = clsJetLinks.GetLinkedPath(strTable)
    Debug.Print "GetLinkedPath(): The linked dBase table resides in " & strPath

    ' Get the data source type
    strType = clsJetLinks.GetLinkedType(strTable)
    Debug.Print "GetLinkedType():  The table [" & strTable & "] is of type " & strType

  Else
    Debug.Print "dBASE5 linked table NOT created."
  End If

  ' 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.
  If clsJetLinks.CreateExchangeLink("My Mail", "Inbox", False, clsJetLinks.Database.name, "Microsoft Outlook") Then
    Debug.Print "Exchange Inbox linked table created."
  Else
    Debug.Print "Exchange Inbox linked table NOT created."
  End If

  ' 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. 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.
  If clsJetLinks.CreateExchangeLink("My Mail", "Contacts", False, clsJetLinks.Database.name, "Microsoft Outlook") Then
    Debug.Print "Exchange data source linked table created."
  Else
    Debug.Print "Exchange data source linked table NOT created."
  End If

  ' 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 clsJetLinks.CreateODBCLink("Pubs", "authors", "Linked_ODBC_Authors", "sa", "") Then
    Debug.Print "ODBC linked table created."
  Else
    Debug.Print "ODBC linked table NOT created."
  End If

  ' See if the 'Authors' table is linked (it's not)
  Debug.Print "IsTableLinked(): The Authors table is " & IIf(clsJetLinks.IsTableLinked("Authors"), "linked.", "not linked.")

  ' Test all links in the test database
  If clsJetLinks.TestLinkedTables Then
    Debug.Print "TestLinkedTables(): All links in the sample database are valid."
  Else
    Debug.Print "TestLinkedTables(): One or more links in the sample database are invalid."
  End If

  ' To finish up, delete all table links from the test database
  If clsJetLinks.DeleteAllLinks Then
    Debug.Print "DeleteAllLinks(): All links in the sample database have been deleted."
  Else
    Beep
    Debug.Print "DeleteAllLinks(): All links in the sample database were *not* deleted."
  End If

  ' Close the class database
  clsJetLinks.CloseDB

  ' Close the class
  Set clsJetLinks = Nothing

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)