Working with Microsoft Access Databases

You can access data from tables located in external Microsoft Access databases from your Microsoft Access application. The procedures for linking and opening external Microsoft Access tables directly are very similar to those for linking and opening tables in other external databases. The one difference is that you don’t need to specify a source database type.

Linking an External Microsoft Access Table

To link an external Microsoft Access table to your local Microsoft Access database, use the CurrentDb function to return an object variable that represents the current Microsoft Access database. Then create a TableDef object and set the connection string and source table name: set the TableDef object’s Connect property to specify the full path to the external table and set its SourceTableName property to the name of the external table. This method works for databases created in Microsoft Access versions 1.x, 2.0, 7.0, and 8.0.

The following example links an external Microsoft Access table and then displays a message box as confirmation. Notice that the connection string begins with just a semicolon (;) rather than a source database type. It isn’t necessary to specify a source database type in your connection string when you access data from external databases that use Microsoft Jet. This example assumes you want to link the Accounts table in a Microsoft Access database named AP.mdb, which is located on the server and shared folder \\Access\Data.

Sub LinkAccessTable()
	Dim dbs As Database
	Dim tdf As TableDef

	' Open the Microsoft Access database.
	Set dbs = CurrentDb

	' Create a TableDef object.
	Set tdf = dbs.CreateTableDef("Linked Accounts Table")

	' Set the connection string to specify the full path to the file that
	' contains the table you want to link.
	tdf.Connect = ";DATABASE=\\Access\Data\AP.mdb"

	' Set the SourceTableName property to the name of the table you want to access.
	tdf.SourceTableName = "Accounts"

	' Append the TableDef object to the TableDefs collection to create a link.
	dbs.TableDefs.Append tdf

	' Display the confirmation message.
	MsgBox "Finished linking " & tdf.SourceTableName & ".", 0
End Sub

Tip   Instead of specifying the connection string and source table name by setting properties of the TableDef object, you can specify them by using the connect and source arguments of the CreateTableDef method. You can also use the Microsoft Access user interface to link external data. For more information on using the CreateTableDef method or the user interface to link external data, see “Linking External Tables” earlier in this chapter.

Opening an External Microsoft Access Table

To open an external Microsoft Access table directly, create a Database object and set the arguments of the OpenDatabase method to specify the full path to the external table, whether to open the table exclusively, whether to open it with read/write or read-only permissions, and the source database type.

The following example opens an external Microsoft Access table directly from a Microsoft Access database and then creates a Recordset object from the table. Notice that source database type is a zero-length string (""). It isn’t necessary to specify a source database type in your connection string when you access data from external databases that use Microsoft Jet.

Sub OpenAccessTable()
	Dim dbs As Database
	Dim rstAccounts As Recordset

	' Open an external Microsoft Access database named AP.mdb
	' without exclusive access and with read/write permissions.
	Set dbs = OpenDatabase("\\Access\Data\AP.mdb", False, False, "")

	' Create a Recordset object from the Accounts table.
	Set rstAccounts = dbs.OpenRecordset("Accounts")
End Sub