| Microsoft Office 2000/Visual Basic Programmer's Guide | |
When you open an Access database by using the Access user interface, Access 2000 automatically establishes an ADO Connection object for you. To work with the current database by using ADO code that is running in the current database, you use the Connection property of the Access CurrentProject object. The following code sample shows how to access the Connection object for the current database and print its connection string to the Immediate pane.
Sub PrintCurrentConnectString()
Dim cnnDB As ADODB.Connection
' Get connection to current database.
Set cnnDB = CurrentProject.Connection
Debug.Print cnnDB.ConnectionString
Set cnnDB = Nothing
End Sub
The PrintCurrentConnectString procedure can be found in the OpenDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.
By using this same technique, you can set a Connection object to CurrentProject.Connection and then pass that Connection to other ADO objects to open them. For example, you can pass a Connection object as the ActiveConnection argument of the Open method of a Recordset object to open that Recordset object by using a connection to the current database. As an example of this technique, the following code fragment opens a Recordset object on a table in the current database by using the current connection.
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
' Open table-type Recordset object.
.Open Source:=strTblName, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:adCmdTableDirect
' Code to work with Recordset object goes here.
End With
Note If you use the CurrentProject.Connection setting in an Access project file (.adp), it always returns a connection that uses the MSDataShape OLE DB provider. The MSDataShape OLE DB provider behaves differently than does a direct connection through the Microsoft OLE DB Provider for SQL Server. For more information about these differences, see "Using the Connection Property of the CurrentProject Object in an Access Project" in Chapter 16, "Multiuser Database Solutions."