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

From Microsoft Access, work with Microsoft Jet linked tables in the current database to link to external data sources using DAO in VBA.

This module includes procedures for working with linked (attached) tables. Microsoft Jet provides a powerful mechanism for accessing external data sources. This technique allows you to create a permanent link to an external database or file without importing a copy of the data which may become outdated. Linked tables are useful because they make the external data source appear as a standard table in the database. This makes the management of data across disparate sources easy, and since links cache structure information about the external data source, accessing the data is usually faster than opening the external data source each time. You can even run (heterogeneous) queries among different data sources. A variety of data sources are supported: Microsoft Access/Jet databases, dBASE DBF files, Paradox db files, FoxPro DBF files, Excel spreadsheets, HTML files, Text Files, Exchange data, ODBC data sources, SharePoint lists.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modJetLinkedTables module.
CreateExchangeLink Procedure

Create a new linked table in the current database to the specified Exchange data source. Use this method to create a link in the current database to a Microsoft Exchange data store such as an inbox, address book, contact list, etc. 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 Procedure Create a new linked table in the current database 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 Procedure Create a new linked table in the current database 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 Procedure Create a new linked table in the current database 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 Procedure Create a new linked table in the current database 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 Procedure Create a new table linked to a SharePoint list. Connection string syntax: ACEWSS; HDR=NO; IMEX=2; DATABASE=database; LIST=list; RetrieveIds=Yes.
DeleteAllLinks Procedure Delete all linked tables in the current database. *Caution* This deletes ALL links in the current database without confirmation.
FailedLinkedTables Procedure Test the links of all the linked tables to see if they are okay.
GetLinkedPath Procedure Get the linked path of the named table. Assumes the table is linked to an Access/Jet database.
GetLinkedType Procedure Get the source type of a linked table.
IsMTableLinked Procedure Determine if the named table is a linked table.
JetRelinkTable Procedure Relink (attach) a table in the current database 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 Procedure Relink all the linked (attached) tables in the current database 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 Procedure Test the validity of the specified linked table. Table links become broken when the database or directory that it 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.
TestLinkedTables Procedure Test the links of all the linked tables in the current database. 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.
' 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

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