Easy ADODB Recordsets and Commands in Access

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.

No Comments on “Easy ADODB Recordsets and Commands in Access”

  1. Gerry

    Hi,

    Love you blog, its very clear. I am new to SQL server and I am trying to use your scripts to access a Stored Procedure on SQL SErver 2008 with MS Access 2003.

    I am getting a few syntax errors int he code and am wondering is your code from a later version of MAS Access? IF so what version please?

    Reply
    1. Juan Soto Post author

      Hi Gerry,

      The code samples are just that, samples that I grab from development meant to convey the general idea of the post and may not work for you without specific changes. If you reply back with more specifics I would be more than happy to respond.

      Thanks
      Juan

      Reply
  3. alan elger

    Hi, thanks for this…first class. I have started to replace my lnked tables and its looking good. One question though if you dont mind. How / can you use a dLookup without a linked table?

    Reply
    1. Juan Soto Post author

      Hi Alan

      The answer is you don’t. DLookup is not a recognized SQL Server function. Instead use a recordset, for example if you need to lookup company name from tblCompanies using CompanyID:

      StrSQL = “Select CompanyName From tblCompanies Where ConpanyID = ” & lngCompanyID
      OpenMyRecordset rs, strSQL

      Reply
      1. PC

        In the free code section, if you click here to open the code, you can see the code and copy it into a module as you explain.

        The code however uses a call “ReadGV” that is not provided and therefore causes the code to fail.

        Reply
  4. Vince

    How do I connect to ODBC without having to authenticate each time. When the sub procedure is run, it prompts me for the ODBC connection.

    Reply
    1. Juan Soto Post author

      You need to supply the user name and password in the connection string. At our firm we use a custom login form to store the credentials for the session and use them to connect with SQL Server.

      Thanks
      Juan

      Reply
  5. Xpertmind

    Please pardon my ignorance, but i don’t seem to see any connection string to sql server in the code or maybe i’m missing something?

    Reply
    1. Juan Soto Post author

      In the connection screen you will see:
      User ID=johndoe;Password=password

      Replace “johndoe” and “password” with you’re own credentials.

      Hope that helps
      Juan

      Reply
      2. Xpertmind

        ..please why does my database that i developed in access 2010 complains of “unrecognise file format” when i try to open it in access 2007 despite the fact that they are both .accdb?

        Reply
        1. Juan Soto Post author

          Unfortunately Access 2010 is not compatible with 2007, you will need to have 2007 users install Access 2010 runtime in order to use your software.

          Reply
          1. Xpertmind

            Thanks Juan, but i still have a challenge connecting to my server from another system, it’s still complaining of ODBC issue, which i believe should not be.

  6. Jurgen

    What about the difference in SQL/T-SQL? You can switch between connections but what if there is a difference in the syntax of a recordset?

    Reply
    1. Juan Soto Post author

      Jurgen,

      Most likely that will not be the case since the two data sources are compatible, i.e., if you developed with SQL Server you will most likely be able to use the same SQL since their are minor version changes with TSQL among SQL Server versions.

      Reply
    1. Ben Clothier

      The short version is that they tried to update ADO to be compatible for 64-bit code but in process broke compatibility with older Windows client.

      However, there is now a fix for this problem. On your Windows 7 computer, verify that you’re using Service Pack 1. Install the fix as listed here:
      http://support.microsoft.com/kb/2640696

      Once installed, update your ADO reference on your Windows 7 SP 1 to use ADO 6.1 and recompile your Access application. That will then run on your older Windows without problems.

      Reply
  8. chris

    Hi Juan,

    I currently run an access database that connects to SqlServer 2000 using adodb exclusively. I want to upgrade the SqlServer to 2012, the problem is that i am having a problem getting a definitive answer of if my adodb code will still work on sqlserver 2012 as the back end.

    Can you shed any light on this? Any help is appreciated…

    Reply
    1. Ben Clothier

      The answer is that yes, it’ll be compatible. We recently upgraded a client to SQL 2012 and did not need to update our code or connection strings.

      Do be aware, though, that SQL Server team has decided to deprecate ADO provider for SQL Server starting with SQL 2012. What it usually means is that on the 3rd version after the announcement of deprecation, it will be removed from SQL Server and they recommend ODBC instead. In case of SQL Azure, you must use ODBC if you want to connect Access with SQL Azure. So this is something you have to keep in mind and plan ahead for next five years.

      Reply
  10. chris smith

    Dear Juan

    I’ve just moved an MS Access 2010 database to SQL 2005. The main form is a continuous form and so needs to be bound to work. Using a name Access query based on the SQL tables is slow when I try and retreive 10,000s of records.

    I tried ADO which provided all the speed I needed by when moving away from an edited record the message always appears:

    “The data was added to the database but the data won’t be displayed in the form because it doesn’t satisfy the criteria in the underlying recordset”.

    I’ve tried various things including an MS hotfix, setting the Me.UniqueTable property and creating a view in SQL on which to base the form. All my tables have unique keys and I can create a working solution if I create a named MS Access query and bind my form to that but then the performance is dreadful.

    My connection code looks like this:

    Set cn = New ADODB.Connection
    With cn
    .Provider = “Microsoft.Access.OLEDB.10.0”
    .Properties(“Data Provider”).Value = “SQLOLEDB”
    .Properties(“Data Source”).Value = “ERNIESQL2005”
    .Properties(“User ID”).Value = “sa”
    .Properties(“Password”).Value = “fishheads”
    .Properties(“Initial Catalog”).Value = “OccultTest”
    .Open
    End With’

    Set rs = New ADODB.Recordset
    With rs
    Set .ActiveConnection = cn
    .Source = strSQL
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    End With
    Set Me.Recordset = rs

    How can I get rid of that error message?

    yours in desperation

    Chris

    Reply
  11. chris smith

    Dear Juan

    an update on my earlier email: I’ve put your connection code above along with your free code in place of mine that I sent in previous positing.

    But when I try and edit any field in the form it is blocked with a status bar message:

    Field [fieldname] is based on an expression and can’t be edited.

    I feel I’m 99% of the way to a fast editable recordset based on multi table query of sql 2005 tables but I’m just missing something!

    Chris

    Reply
  12. chris smith

    Juan

    actually I’ve spotted in using your code above that I didn’t use your special line for making the recordset editable but when I use that then I’m back to my original message that:

    The data was added but the data wont be displayed in the form because is doesn’t satisfy the criteria in the underlying record source.

    The row I’ve just edited then disappears until I reopen the continous form whereapon it shows my edit just fine.

    I don’t understand why the underlying recordset isn’t editable when I’ve set the locks as per your line: OpenMyRecordset rs, strSQL, rrOpenDynamic, rrLockOptimistic,True

    And if I switch to a name access query it works (just slowly).

    So close….

    Reply
    1. Juan Soto Post author

      Hi Chris,

      Sorry to hear you are having issues with that approach. To be honest I shy away from using the method described because it’s not usable for editing data, (as you have discovered). Go ahead and use either a linked table or a view, NOT an Access query since it will run too slow.

      Keep in mind if you do use a view with multiple tables as your form source, (Access thinks it’s a table when you link it), you will need to address these two issues:
      — The view will require an Index for editing data, see this post on that: http://accessexperts.net/blog/2010/03/03/using-sql-server-views-with-access-index-needed-for-editing-data-2/

      — If you have a multi-table view and you allow delete records you will need to create a delete trigger on the view so that SQL Server knows which table to delete the records from.

      Good Luck!
      Let me know how it goes.
      Juan

      Reply
  14. Dick Weber

    Juan:

    I followed your suggestion on the Easy ADODB and I am very grateful;
    I was replicating all of that code in every module that used record sets.

    Also discovered the execution of command line for a stored procedure
    with parameters which makes calling and passing the parameters
    a lot easier than what I was doing before. I was using the Command object
    and laboriously creating and setting the values to each parameter with
    separate commands. Never realized I could just specify as follows:

    Exec QueryName Param1, Param2

    Thanks so much for your BLOG and
    your LOVE of Access and SQL Server.

    Dick Weber

    Reply
  15. Blacktorres

    Hi Juan,

    I came across this website a few months ago and immediately subscribed to your blog. Here is my question:

    I am using an Access 2010 adp with a SQL Express 2008 R2 database and I am new on VBA programming and using ADO.
    I have a bound form with a subform and I want to bind the output (recordset) of a stored procedure to some text controls on the main form.
    I am using the following code and can’t get rid of the Run-time Error 3704: Operation is not allowed when the object is closed.

    The code I use:

    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    StrSQL = “EXEC stp_Bereken” & Me.Param1.Value & “,” & Me.Param2.Value & “”
    rs.CursorLocation = adUseClient
    rs.Open StrSQL, cnn, adOpenStatic, adLockOptimistic

    With rs
    If .RecordCount = 0 Then
    MsgBox “NO records”
    Else
    MsgBox “records”
    End If
    End With

    The output of ?CurrentProject.Connection:
    Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=MY-PCSQLEXPRESS;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Provider=SQLOLEDB.1

    Can you point me in the right direction?

    Many thanks

    Reply
  17. Jb

    I try to use OpenMyRecordset with transactions but it seems not to work.
    con.BeginTrans
    sql=”insert into…;SELECT SCOPE_IDENTITY() as ID”
    OpenMyRecordset()
    But i have to make OpenRecordset() before con.BeginTrans but then ‘INSERT INTO’ is not part of the transaction. ExecuteMyCommand() can’t use the SELECT SCOPE…
    Thx

    Reply
    1. Ben Clothier

      You should be able to use BeginTrans before opening the recordset based on multiple statement SQL. Looking in our code, it seems that this is best done using ADO methods directly. Here’s snippet from one of our code where do a SCOPE_IDENTITY():

          strSQL = "INSERT INTO tbl... (...) VALUES (...); SELECT SCOPE_IDENTITY() AS NewID"
    If Con.State = adStateClosed Then
        OpenMyConnection
    End If
    Set rs = Con.Execute(strSQL)
    Set rs_Value = rs.NextRecordset
    With rs_Value
        NewID = !NewID
    End With

      Hope that helps.

      Reply
    1. Ben Clothier

      Support for nested transactions is a mixed bag. For example, SQL Server ‘nominally’ supports nested transaction but in actuality, it’s not that simple (e.g. a rollback sets @@TRANCOUNT back to 0 regardless of how many BEGIN TRANSACTION there were previously. Furthermore, nested transaction is not necessarily available across all possible providers/drivers. In general, it’s probably best to avoid nesting the transaction and more especially spanning it across more than one scope. It’s OK to have a stored procedure that might use named transaction & SAVE/ROLLBACK to allow for partial rollback within its body but you shouldn’t want to design something that spans beyond this single stored procedure, else you would get additional errors if transaction is dropped to the floor.

      I hope that helps.

      Reply
  19. Ed Lindoo

    Juan,
    I have a 2010 Access DB that connects to a network 2005 SQL server via ODBC. From my primary DB i have created an accde database to be used on multiple computers, all with ODBC setup on them to the network SQL.

    On my PC both the accdb and accde work fine. On the other computers I do not have full Access installed just the runtime with service pack 1.

    On these other computers the 2 queries that are on a form just open up blank. I suspect that you can’t just copy an accde to another computer, set up the ODBC links via admin tools and expect it to work. I’m thinking that the links probably need to be relinked.

    Is there a way to do that in an accde?

    Ed

    Reply
    1. Juan Soto Post author

      Ed,

      It seems you may have a security issue on your hands. Make sure users have rights to the tables in question.

      Regards
      Juan

      Reply
  20. John W. Colby

    Juan,

    I love what yuou are trying to accomplish however my company (the borg of IT companies) blocks google docs. This means that anything you write which tells me to read some function from google docs becomes black box with missing internals. I was sent to this page from http://accessexperts.net/blog/2012/04/04/part-2-linking-tables-using-a-sql-server-table/. The function OpenMyRecordset is not available to me and therefore the fireworks promised in RelinkAllTablesADOX Procedure never goes off.

    Sigh.

    It’s always something.

    jwc

    Reply
  21. david

    Hello Juan thanks for your reply to my last request, I have successfully managed to open recordsets and attach them to my forms using your code! Now i am trying to use your ExectuteMyCommand to update a local table with data from a sql server, but am not succeeding.

    Reply
    1. Ben Clothier

      David,

      You can’t do heterogeneous queries (mix local tables with remote tables) when using ADO/SQL Server. Access makes it very easy to do heterogeneous queries so you might want to just use an Access query for this case. If the amount of data is large and running an Access query takes too long, consider alternatives such as marshaling the content of the table as XML document to the SQL Server.

      Reply
  22. Isabel

    Hi Juan – I am tring to use your code where you call the function “OpenMyRecordset”. I have already defined my connection string as a constant:

    Public Const c_strODBCConnection As String = “ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=SQD-FTCL1;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=BDBDiagnosticSamples;”

    (I use this constant earlier in creating a pass through query which worked fine:
    Call CreatePassThrough(m_SQL, strSbFormSourceQry, c_strODBCConnection, False) ) but now when I use it in my version of your function shown below:

    Public Function OpenMyRecordset(rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset

    Dim con As New ADODB.Connection

    If con.state = adStateClosed Then
    con.ConnectionString = c_strODBCConnection
    con.Open
    End If

    the code stops at the con.open line with an error message of:
    run-time error’-2147467259 (80004005)’:
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    I am at a loss of what to do – can you suggest anything?
    I am using Windows 7 Enterprise SP1 and am working in an new accdb in which I am trying to re-create an adp so that we use ODBC in preparation for adp’s no longer working. I have created a dsn which I used to link all my SQL server tables with.
    thanks

    Reply
  23. z

    Your article refering the ReadGV/WriteGV/etc… is, frankly, worthless to many of us where the IT-Departments block all such offsite applications and storage.
    Worse yet, Google being a bit backwards, has discontinued their applicaitons for us non-business folks making access to this information just that much harder.
    -z

    Reply
    1. Ben Clothier

      z,

      Do you realize that the ReadGV/WriteGV discussed in another article are actually using local table in the front-end? Thus the comment about unable to use those functions, which is for different article is not really relevant here. This would still work for an Access application with a local backend hosted within the LAN.

      In fact, OpenMyRecordset and ExecuteMyCommand would still work for a local backend, too without needing it to be offsite.

      I’m sorry to hear about your frustrations with Google and IT department. I’m sure that you will be able to find a solution if you look for one.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

XHTML: You can use these tags <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>