ADO - ADO Connection Modes and Access Permissions

ADO (ActiveX Data Objects) provides several ways for an application to connect to and interact with a data source. When establishing an ADO connection, two important concepts are connection modes and access permissions. Connection modes determine how the connection can be used, particularly whether the application can read data, modify data, or share the connection with other users or processes. Access permissions, on the other hand, determine what operations the connected user or application is actually authorized to perform.

Understanding these concepts is important when developing database applications because simply establishing a successful connection does not necessarily mean that the application has permission to perform every database operation.

1. What Is an ADO Connection Mode?

The ADO Mode property of the Connection object specifies the permissions available through a connection. It controls how the connection can be used for reading and writing data and whether the connection can be shared.

The basic syntax is:

Connection.Mode = adModeRead

The mode can be set before opening the connection. For example:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Mode = adModeRead
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\College.accdb"

Here, the connection is opened in read-only mode.

2. Why Connection Mode Is Important

Connection mode provides an additional level of control over how an application interacts with a data source.

For example, imagine an application that is designed only to display customer information. There is no reason for that application to modify customer records. Opening the connection in read-only mode can help prevent accidental modifications through that connection.

Similarly, an application that needs to update records must use a mode that permits writing.

Connection modes can therefore be useful for:

  • Controlling read and write behavior

  • Preventing unintended data modifications

  • Sharing connections

  • Defining how the connection can be accessed

  • Improving application-level control over database operations

However, connection mode should not be confused with database security permissions. A connection mode does not replace authentication or authorization provided by the database system.

3. Common ADO Connection Modes

ADO provides several ConnectModeEnum values.

adModeUnknown

adModeUnknown indicates that the mode is not yet known.

It is commonly used as the default value.

cn.Mode = adModeUnknown

The provider may determine the appropriate mode when the connection is opened.

adModeRead

adModeRead opens the connection for read-only access.

cn.Mode = adModeRead

An application using this mode can retrieve information but is not intended to modify the data through that connection.

A typical example would be a reporting application that only displays database information.

adModeWrite

adModeWrite provides write access.

cn.Mode = adModeWrite

This mode is useful when the application needs to modify information in the data source.

Depending on the provider and data source, read operations may also be possible, but applications should not assume identical behavior across all providers.

adModeReadWrite

adModeReadWrite provides both read and write access.

cn.Mode = adModeReadWrite

This is a common choice for applications that need to retrieve existing information as well as insert, update, or delete records.

For example:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\College.accdb"

The application can then execute operations such as:

cn.Execute "UPDATE Students SET Marks = 85 WHERE StudentID = 101"

provided that the underlying database permissions also allow the operation.

4. Shared Connection Modes

ADO also defines modes related to sharing a connection.

These become particularly relevant when multiple users or processes may access the same data source.

Some of the important values include:

adModeShareDenyNone
adModeShareDenyRead
adModeShareDenyWrite
adModeShareExclusive

adModeShareDenyNone

This allows other users or processes to share the connection without specifically denying read or write access.

It is appropriate when concurrent access is expected and the provider supports the requested sharing behavior.

adModeShareDenyRead

This prevents other users or processes from reading the data source through the relevant sharing mechanism.

It is useful in situations where exclusive control over reading is required.

adModeShareDenyWrite

This prevents other users or processes from writing to the data source.

It can be useful when an application needs to protect a data source from simultaneous modifications.

adModeShareExclusive

This requests exclusive access to the data source.

cn.Mode = adModeShareExclusive

An exclusive connection can be useful for operations that require the application to have sole access to a resource. However, whether the request succeeds depends on the underlying provider and data source.

5. Combining Connection Modes

ADO connection modes can be combined using logical operations.

For example:

cn.Mode = adModeReadWrite Or adModeShareDenyWrite

This combines read/write access with a sharing restriction.

The exact combinations that are supported can depend on the OLE DB provider.

This is important because ADO acts as a data-access layer, while the actual database behavior is ultimately controlled by the underlying provider and data source.

6. Connection Mode and Database Permissions Are Different

One of the most important concepts to understand is that ADO connection mode is not the same as database authorization.

Consider an application connecting to a SQL Server database.

The application may specify:

cn.Mode = adModeReadWrite

This requests read/write access through the ADO connection.

However, suppose the database account used by the application has only SELECT permission.

In that situation, the application cannot successfully perform an UPDATE, INSERT, or DELETE simply because the ADO connection was opened using adModeReadWrite.

The database server's authorization rules ultimately determine whether the operation is permitted.

Therefore:

ADO Connection Mode
        +
Database/User Permissions
        =
Actual Accessible Operations

Both aspects need to be considered.

7. Authentication vs. Authorization

Connection security also involves two related concepts: authentication and authorization.

Authentication answers:

"Who are you?"

For example, a database server may authenticate a user using a username and password.

Authorization answers:

"What are you allowed to do?"

For example, the authenticated user might be permitted to:

  • Read tables

  • Insert records

  • Update records

  • Delete records

  • Execute stored procedures

ADO connection mode deals with how the connection is opened and accessed, while the database's security system determines the user's actual privileges.

8. Example of Read-Only Access

Suppose a school application only needs to display student information.

A read-only connection can be requested:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Mode = adModeRead

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Students.accdb"

The application could execute:

cn.Execute "SELECT * FROM Students"

But attempting to modify the database may fail:

cn.Execute "UPDATE Students SET Marks = 90 WHERE StudentID = 10"

The exact result depends on the provider and data source, but the read-only mode indicates that the connection is not intended for modification.

9. Example of Read/Write Access

An administrative application may need to display and modify student records.

In that case:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Mode = adModeReadWrite

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Students.accdb"

The application can retrieve information:

cn.Execute "SELECT * FROM Students"

It can also request modifications:

cn.Execute "UPDATE Students SET Marks = 95 WHERE StudentID = 10"

Again, successful execution depends on the permissions granted by the underlying database.

10. When Connection Mode Is Set

The Mode property generally needs to be configured before the connection is opened.

For example:

Set cn = New ADODB.Connection

cn.Mode = adModeReadWrite
cn.Open connectionString

Changing connection-related properties after the connection has already been opened may not always be permitted or may have provider-specific behavior.

Therefore, it is good practice to configure the connection before calling Open.

11. Provider Dependency

ADO works with different OLE DB providers and data sources. Therefore, connection-mode behavior is not completely independent of the provider.

For example, a mode that works with one provider may behave differently with another provider because the underlying data source may have different sharing and permission mechanisms.

This means developers should consider:

  1. Which database is being accessed.

  2. Which OLE DB provider is being used.

  3. What permissions the database account has.

  4. Whether the database supports the requested sharing mode.

  5. Whether the connection mode is appropriate for the application's purpose.

12. Connection Mode and Multi-User Applications

Connection modes become particularly important in multi-user applications.

Suppose several users are accessing the same database simultaneously. If one application requests exclusive access, other applications may be prevented from accessing the data source depending on the provider.

For normal multi-user applications, unnecessarily requesting exclusive access can create problems.

For example:

cn.Mode = adModeShareExclusive

should not be used casually in an application where many users need to access the same database.

Instead, applications should generally use a sharing mode appropriate for concurrent access.

13. Connection Mode and Security

Connection mode can contribute to safer application design, but it should not be considered a complete security mechanism.

For example, opening a reporting application's connection as read-only can reduce the possibility of accidental modifications.

However, sensitive database operations should still be protected through proper database permissions.

A secure architecture should therefore use multiple layers:

User Authentication
        ↓
Database Authorization
        ↓
ADO Connection Mode
        ↓
Application-Level Controls
        ↓
Database Operations

Each layer serves a different purpose.

14. Advantages of Using Appropriate Connection Modes

Using an appropriate connection mode provides several benefits.

Reduced accidental modification

A read-only connection can help prevent an application designed for reporting from modifying data.

Better resource sharing

Sharing modes can help control how different users or processes access a data source.

Improved application design

Different applications can request different access levels according to their requirements.

Better control over database operations

Connection modes allow developers to communicate the intended access behavior to the underlying provider.

Support for multi-user environments

Proper sharing modes can help applications work more predictably when multiple users access the same resource.

15. Important Limitations

Connection modes should not be treated as a replacement for database security.

They do not independently determine whether a database user has permission to perform an operation.

For example, the following does not automatically grant database privileges:

cn.Mode = adModeReadWrite

It only specifies the requested mode for the ADO connection.

The underlying database account still needs the necessary privileges.

Similarly, a sharing mode may not be fully supported by every provider.

16. Best Practices

When working with ADO connection modes, developers should follow these practices:

  1. Choose the least permissive mode appropriate for the application.
    If an application only reads data, a read-only connection may be preferable.

  2. Set the mode before opening the connection.
    Configure the connection before calling Open.

  3. Do not confuse connection mode with database authorization.
    Database permissions remain essential.

  4. Avoid unnecessary exclusive access.
    Exclusive modes can interfere with other applications and users.

  5. Consider the provider.
    Different OLE DB providers can implement connection and sharing behavior differently.

  6. Use database security for actual authorization.
    Sensitive operations should be controlled through database accounts, roles, and permissions.

  7. Test connection behavior with the actual data source.
    Provider-specific behavior should be verified rather than assumed.

Conclusion

ADO connection modes provide a mechanism for specifying how an ADO connection should access and share a data source. Modes such as adModeRead, adModeWrite, and adModeReadWrite define the intended access level, while sharing modes such as adModeShareDenyNone, adModeShareDenyRead, adModeShareDenyWrite, and adModeShareExclusive influence how the connection interacts with other users or processes.

The most important distinction is that connection mode does not replace database permissions. ADO can request read/write access, but the underlying database still decides whether the authenticated user is authorized to perform a particular operation. Understanding both connection modes and database permissions is therefore essential for building reliable, secure, and multi-user ADO applications.