When it comes to interacting with SQL Server procedures and data, I use ADODB exclusively in Access. The technology was developed originally to interface with any data source, not just SQL.

When I first started using ADODB recordsets my code looked like this:

Dim rs as ADODB.Recordset
Dim con as ADODB.Connection

Set con = New ADODB.Connection
With con
.ConnectionString = “Provider=SQLOLEDB;Data Source=ServerSQLExpress,1433;Initial   _ Catalog=MyDB;User ID=johndoe;Password=password”
.Open
If .State = adStateClosed Then
MsgBox “Can’t open connection.”, vbInformation, “Error:”
End If
End With

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open “Select * from tblClients”
End With

Now imagine the above lines repeated over and over again in my code and you can imagine, it was a chore. To be honest you don’t have to use the code block, but can also accomplish the same thing by just using the open statement, but I believed there was another way.

What I came up with was a central location to open my recordsets and for executing SQL commands using two function calls: OpenMyRecordset and ExecuteMyCommand.

Advantages to my approach

  • By using one connection object for all calls to the server, be that Access or SQL, I’m keeping the connections down to a minimum. When I open multiple recordsets I’m just using one connection, which stays open while my application is open or when the server disconnects me.  Doing so keeps the number of connections down to a minimum and allows for easy modifications going forward.
  • Easy cut over to SQL: I’ll use OpenMyRecordset and ExecuteMyCommand regardless if I’m using Access or SQL Server. Sometimes I’ll start a project using an Access backend, with the intent of moving it to a SQL Server later. If that’s the case I only have to change the conConnection procedure from the Access connection, (CurrentProject.Connection), to a connection string stored in tblProgramOptions. It makes switching from Access to SQL Server a breeze.
  • Fast Connection to SQL: By using ADODB objects and SQLOLEDB connection strings I bypass the ODBC layer altogether and connect straight to SQL, making my app that much more quicker. I’ll be writing an article on SQLOLDB next.

Free Code

To use the code, first open it here, then copy and past it into a new module.

OpenMyRecordset

When I need to open a ADODB recordset in my code I use this procedure to connect, get my data, and then disconnect. Here is how I would use it:

Private Function TestADODB()

Dim rs as ADODB.Recordset
Dim strSQL as String

strSQL = “Select ClientID from tblClients Where State = ‘IL'”
OpenMyRecordset rs, strSQL
With rs
If .RecordCount = 0 Then
MsgBox “No records returned”
Else
‘Do something here
End If

End With

Set rs = Nothing

End Function

Notice the use of the record count property instead of EOF and BOF, you can only use this property if you specify a client side cursor, which OpenMyRecordset does by default.

If you need to open a full recordset that will allow you to add or edit data then you would use:

OpenMyRecordset rs, strSQL,  rrOpenDynamic,  rrLockOptimistic,True

ExecuteMyCommand

Use this function when you don’t need to return a recordset, when you’re doing an action query or executing a stored procedure:

ExecuteMyCommand "Update tblClients Set State = 'IL' Where ClientID = 100"

I hope this article prompts you to use ADODB with SQL Server instead of DAO, it’s much faster and can provide you with more flexibility.

If you love this article than consider subscribing to my blog here.

I’m also available for speaking engagements and consulting on Access with SQL Server projects, you can contact me here.