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

A class to create and refresh Microsoft Jet linked tables using DAO in VBA and VB6.

This class makes it easy to work with multiple linked table objects without having to open and close the database each time.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CJetLinkedTables class.
Database Property Get the DAO Database object of the currently opened database which you can use to get and set database properties. Don't close the database object through the Database property using the DAO close method because class will no longer have the necessary state to perform operations. Instead, perform the CloseDB method if you had the class open the database, or close your own database variable that was passed to the class.
DatabasePassword Property The database password used to open the database. This property only has a value if the OpenDB method was called and a password was specified. The property cannot 'extract' the database password used to open a database outside of the class.
OpenExclusive Property Status of whether or not the database was opened by the class in exclusive mode. The value of this property only has meaning if the OpenDB method was called. This property cannot 'extract' the Exclusive setting of a database opened outside of the class.
OpenReadOnly Property Status of whether or not the database was opened by the class in read-only mode. The value of this property only has meaning if the OpenDB method was called. This property cannot 'extract' the read-only setting of a database opened outside of the class.
Class_Terminate Terminate Class termination.
CloseDB Method Close the currently open database. Note that this only works if the class opened the database through the OpenDB() method. If the class has a database open that was passed to it through the Database property, or the OpenDB() method has not yet been called, this method silently fails.
CreateExchangeLink Method Create a new linked table to the specified Exchange data source. Use this method to create a link in the specified database to a Microsoft Exchange data store such as an inbox, address book, or contact list. The value for the strFolderName parameter can be quite tricky--it needs to correspond to a node in the treeview that is visible when you have Exchange Inbox or Microsoft Outlook open.

Connection String Syntax: Exchange 4.0;MAPILEVEL=Mailbox - Dave Jones|Inbox;TABLETYPE=0;DATABASE=C:\NWIND.MDB;Profile=Microsoft Outlook;PWD=topsecret.

For complete details on the valid settings for Exchange connection strings, search DAO online help for "Connect".
Note: This code requires your email profile to be configured properly. Sometimes Office installs the Microsoft Exchange driver, but it is not registered. This problem causes the "Could not find installable ISAM" error message when you try to link to an Exchange data source through DAO/Jet. To solve this problem, you must manually register the Exchange Driver. For more information, see the Microsoft KB209805 article "Could not find installable ISAM".
CreateISAMLink Method Create a new linked table to the specified data source. Use this method to create a new table link in the specified database to any of the following data sources: DBase, Excel, FoxPro, HTML, Paradox, Text.
CreateJetLink Method Create a new linked table to an Access/Jet table in another database.
Note: You can link a table with a new name by specifying the new name in the strTableName parameter. In such a case, the database with the link shows a table name different from the name of the table that it is linked to.
CreateODBCLink Method Create a new linked table to an ODBC data source using an existing DSN. Use this method to create a link to a registered ODBC data source, such as a SQL Server or Oracle installation. To create and register ODBC data sources, use the ODBC Administrator available under the Windows Control Panel, or use the RegisterDatabase method of the DAO DBEngine object. The optional strDatabase, strUID and strPassword parameters override the DSN values. If you do not specify a value for one of the parameters, and the DSN does not have that value specified, the ODBC driver will prompt you for values.

Connection string syntax: ODBC; DSN= datasourcename; DATABASE=database; UID=user; PWD=password; [LOGINTIMEOUT=seconds;]
CreateODBCLinkDriver Method Create a new linked table to an ODBC data source specified by a driver and server (DSN less connection). Use this method to create a link to a registered ODBC data source, such as a SQL Server or Oracle installation. To create and register ODBC data sources, use the ODBC Administrator available under the Windows Control Panel, or use the RegisterDatabase method of the DAO DBEngine object. The optional strDatabase, strUID and strPassword parameters override the DSN values. If you do not specify a value for one of the parameters, and the DSN does not have that value specified, the ODBC driver will prompt you for values.

Connection string syntax: ODBC; DRIVER={SQL Server}; SERVER=localhost; PORT=3306; DATABASE=database; UID=user; PWD=password; [LOGINTIMEOUT=seconds;]
CreateSharePointLink Method Create a new table linked to a SharePoint list.

Connection string syntax: ACEWSS; HDR=NO; IMEX=2; DATABASE=database; LIST=list; RetrieveIds=Yes
DeleteAllLinks Method Delete all linked tables in the current database.
*Caution* This deletes ALL links in the current database without confirmation.
GetLinkedPath Method Get the linked path of the named table. Assumes the table is linked to an Access/Jet database.
GetLinkedType Method Get the source type of a linked table.
IsTableLinked Method Determine if a table is a linked table.
OpenDB Method Open the named Access/Jet database. Before using the class, the class must be associated with a database. Use the OpenDB method to tell the class which database you want to use. This method opens the database using the options specified and keeps the database open until the class is de-instantiated. If you already have a database variable open and want to use this class, you can set the class Database property to an open DAO Database object variable. See the Database property of this class for more information.
RelinkTable Method Relink (attach) a table to another database. This method only works with non-Exchange, non-ODBC table links. To relink Exchange or ODBC table links, you should delete and recreate the table links. The method accomplishes its task by setting the Connect property of the specified table's TableDef object to the new path. It then calls the RefreshLink method to force Jet to open the source database and table and establish the permanent link.
RelinkTables Method Relink all the linked (attached) tables to a new database. This method only works for tables linked to Access databases. To relink Exchange or ODBC table links, you should delete and recreate the table links. This method accomplishes its task by setting the Connect property of the specified table's TableDef object to the new path. It then calls the RefreshLink method to force Jet to open the source database and table and establish the permanent link.
TestLinkedTable Method Test the validity of the specified linked table. Table links become broken when the database or directory that is linked to is moved, deleted, or renamed. Testing table links is not an easy operation because DAO does not expose a property that tells you whether or not the link is valid. The problem is further complicated because you can set an object variable to a linked table with a broken link, and no runtime error is generated. This is because Jet caches information about the table when the link is created. When you point an object variable to the link, the cached information is read, not the information from the actual source table. The only sure-fire way to test a link's validity is to try and get information from the underlying table that is not cached. To do this, this method turns off error handling and tries to get the name of the first field in this table. Since this information is not cached in the table link, Jet is forced to try and open the source table that the link points to. If the link is broken, a runtime error occurs. We trap this error and can determine that the link is broken.
TestLinkedTables Method Test the links of all the linked tables. Check the Connect property of each table in the database to determine which are linked. For information on the technique for determining if links are valid, see the TestLinkedTable method.
FailedLinkedTables Method Test the links of all the linked tables to see if they are okay.
'
' 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

Total Visual SourceBook 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

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS