Creating Recordsets from External Tables

You can also use VBA to create an ADODB recordset from any Access table, even one outside the current database. The basic idea is the same as in Chapter 7, where you need to define a connection to the table before creating a recordset. For example, the boilerplate code for defining a recordset from a local table starts out something like this:

Dim cnnl As ADODB.Connection Set cnnl = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnl <etc...>

The only problem in that code is the CurrentProject.Connection is a reference to local tables. When the table from which you want to create a recordset exists outside the current database, you need to use a different connection. When the table is in an external Microsoft Access database (MDB) file, use the following syntax to define a local recordset, changing only the arguments shown in italics:

'Build a recordset from foreign .mdb database. Dim CnnStr As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" CnnStr = CnnStr + "User ID=Admin;" CnnStr = CnnStr + "Data Source=path"

Dim cnn2 As New ADODB.Connection Dim MyRecordSet As New ADODB.Recordset MyRecordSet.ActiveConnection = CnnStr MyRecordSet.Open "Select * FROM [table/query]"

where path is the full path and filename of the external database file, and table/ query is the name of a table or query within that table. For example, the following code creates, in the current database, a recordset named MyRecordSet that contains all the records from CCSecureQry in the external database C:\

SecureData\MOMSecure.mdb:

'Build a recordset from foreign .mdb database. Dim CnnStr As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" CnnStr = CnnStr + "User ID=Admin;"

CnnStr = CnnStr + "Data Source=C:\SecureData\MOMSecure.mdb"

Dim cnn2 As New ADODB.Connection Dim MyRecordSet As New ADODB.Recordset MyRecordSet.ActiveConnection = CnnStr MyRecordSet.Open "Select * FROM [CCSecureQry]"

After this procedure executes, the current database will contain an ADODB recordset named MyRecordset that contains the contents of the external CCSecureQry query. The recordset will be invisible, as always. You'll need to use VBA code and ADODB recordset syntax to access data in the recordset.

See Chapter 7 for more information on creating and using ADODB recordsets.

0 0

Post a comment