Difference Between Connection and AccessConnection Properties

This article discusses the differences between the CurrentProject.Connection and the CurrentProject.AccessConnection properties in Microsoft Access 2002 or later.

The CurrentProject.Connection property was introduced in Microsoft Access 2000. It allows developers who are writing ADO code in Microsoft Access to share the same ADO connection that is used by Microsoft Access for the currently open database (Jet or SQL Server database). This keeps developers from having to open and manage separate ADO connections when they need to manipulate the database that is currently open in Microsoft Access.

The CurrentProject.AccessConnection property was introduced in Microsoft Access 2002. Like the CurrentProject.Connection property, it allows developers to share an ADO connection with Microsoft Access. The difference between these properties depends on whether you are using an Access project (that is, a SQL Server database, ADP) or a Jet database (MDB) file in Microsoft Access.

Access Projects (ADPs)

When you are using an Access project (ADP) in Microsoft Access 2002 or later, there is no difference between the CurrentProject.Connection and the CurrentProject.AccessConnection properties. Both properties return an ADO connection that uses the SQL Server OLEDB provider (SQLOLEDB) as the data provider and the Microsoft Access 10 OLEDB provider (Microsoft.Access.OLEDB.10.0) as a service provider.

NOTE: The Microsoft Access 10 OLEDB provider is an OLEDB service provider that was written specifically for use in Microsoft Access. It was not designed to be used in applications other than Microsoft Access, and it is unsupported in other applications.

Here is a typical connection string that is returned by either property for an ADP that is connected to a SQL Server database:

Jet Databases (MDBs)

When you are using a Jet database in Microsoft Access 2002 or later, the CurrentProject.Connection and CurrentProject.AccessConnection properties return different ADO connections. You can use either property when you are writing ADO code in a Jet database. However, there are certain situations where each property is useful. What follows is a breakdown of what each property returns in Microsoft Access 2002 or later, and what you should consider before using these properties.

CurrentProject.Connection

The CurrentProject.Connection property behaves exactly as it did in Microsoft Access 2000. It returns an ADO connection using the Microsoft Jet OLEDB provider (Microsoft.Jet.OLEDB.4.0 provider). For example, here is a typical connection string that is returned for a Jet database that is using the CurrentProject.Connection property: There are several scenarios where you should use CurrentProject.Connection. If your application requires any of the following features, you should use CurrentProject.Connection to return the ADO connection.

• Jet and Replication Objects (JRO) code.
• Microsoft ADO Extensibility for DDL and Security (ADOX) code.
• ADO code that requires Index support for ADO recordsets, such as the Seek method.

The Microsoft Access 10 OLEDB provider does not fully support the required OLEDB interfaces necessary for these features to work. If your application requires these features, you must use a connection created with the Jet OLEDB provider, which is returned by the CurrentProject.Connection property.

CurrentProject.AccessConnection

The CurrentProject.AccessConnection property will return an ADO connection that uses two OLEDB providers. It uses the Jet OLEDB provider (Microsoft.Jet.OLEDB.4.0) as the data provider and the new Microsoft Access 10 OLEDB provider (Microsoft.Access.OLEDB.10.0) as a service provider. For example, here is a typical connection string that is returned for a Jet database that is using the CurrentProject.Connection property: There is one main advantage for using the CurrentProject.AccessConnection property. Microsoft recommends you use CurrentProject.AccessConnection if you intend to create ADO recordsets that will be bound to Access forms. This is because the form will not be updateable unless it is created using the Microsoft Access 10 OLEDB provider, even if the recordset is updateable in ADO.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: