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 List

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 Knowledgebase article #Q209805.

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 that 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;]

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 Code for Using Class: JetLinkedTables

'' 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