Go to Top

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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

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:

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:

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:

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

69 Responses to "Easy ADODB Recordsets and Commands in Access"

  • Alistair
    November 21, 2017 - 9:24 pm Reply

    Hi Juan,

    I used some your methods to move most of the front end queries from access to TSQL resulting in huge performance gains, so on behalf of my users Thank you!

    I have one issue…

    I am in the process of integrating a third party database into our system which uses a nText field to store a CHR(13) delimited list. When I try to get this as part of the ODBC easy routine and a stored procedure this field is blank.

    I can get the value only by doing a lookup on a linked table.

    Is there any thing I need to do to return ntext fields?

    Thanks,

    on a local linked table

  • Time
    May 2, 2016 - 9:27 pm Reply

    i find it hard to edit my ADODB, the text entries are saved successfully to issue_bk database table, but I cannot edit the booksinformation database table for Qty in Stock after minus Number of Books borrowed ..i hope someone here or u understand the difficulties im facing n help me out… thanks.

    Private Sub cmdissue_Click()

    Dim db As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    db.Open “librarydtbase”, “admin”, “”
    rs.Open “select * from issue_bk”, db, adOpenKeyset, adLockOptimistic

    If Not (txtlibid.Text = “”) Then
    rs.AddNew
    rs(“LibraryID”) = txtlibid.Text
    rs(“CustName”) = txtcname.Text
    rs(“ISBN”) = txtisbn.Text
    rs(“BookName”) = txtbkname.Text
    rs(“Qty”) = txtNoBooks.Text
    rs(“DateOfIssue”) = txtLDate.Text
    rs(“DateOfReturn”) = txtRDate.Text
    rs.Update
    txtlibid.Text = “”
    txtcname.Text = “”
    txtisbn.Text = “”
    txtbkname.Text = “”
    txtinstock.Text = “”
    txtNoBooks.Text = “”
    txtLDate.Text = “”
    txtRDate.Text = “”
    Else
    MsgBox “Required Fields cannot be empty!”, , “Error”
    End If

    db.Close
    Set db = Nothing

    db.Open “librarydtbase”, “admin”, “”
    rs.Open “select * from booksinformation”, db, adOpenKeyset, adLockOptimistic

    rs.MoveFirst
    Do While Not rs.EOF
    If Val(txtisbn.Text) = rs(“ISBN”) Then
    If rs(“InStock”) > Val(txtNoBooks.Text) Then
    rs(“InStock”) = rs(“InStock”) – Val(txtNoBooks.Text)
    MsgBox “Issue Book Successful”, , “Book Issued…”
    Else
    MsgBox “No Stock Available!”, , “Error!”
    End If
    End If
    rs.Update
    rs.MoveNext
    Loop
    End Sub

  • CCA
    February 22, 2016 - 7:51 am Reply

    Hola. Saludos desde Chile. Junto con agradecer su maravillosa pagina les escribo porque no me resulta un procedimiento que quiero hacer es algo asi:

    Dim rsInstAca As ADODB.Recordset
    Dim rsInstAca2 As ADODB.Recordset
    Dim strSQL As String
    strSQL = “exec InsertarInstitucionAcademica ‘” & Me.TBInstitucionAcademica & “‘”
    OpenMyRecordset rsInstAca, strSQL, , rrlockoptimistic, True

    Set rsInstAca2 = rsInstAca.NextRecordset
    With rsInstAca2
    Me.TBIdInstitucionAcademica = .Fields(0)
    End With

    El error que aparece es Bloque with no declarado. El procedimiento almacenado es el siguiente.
    ALTER PROC [dbo].[InsertarInstitucionAcademica]
    @Institucion nvarchar(255)
    AS
    BEGIN
    INSERT INTO dbo.InstitucionAcademica (InstitucionAcademica) VALUES (@Institucion)
    SELECT SCOPE_IDENTITY()
    END

    Agradecido de antemano.

    Saludos,

  • Ingrid
    November 16, 2015 - 1:18 pm Reply

    Thank you for posting this easy to use code. I’ve got a problem though, perhaps you could point me in the right direction?

    I use the OpenMyRecordset to get the results from a stored procedure from SQL Server 2014 in Access 2013. One of the fields is of type nvarchar(max). If I link to the table directly in Access, this field shows up fine, but with the OpenMyRecordset the contents of the field are garbled. Any idea how I can fix this?

  • thuc
    September 27, 2015 - 4:33 pm Reply

    hi, thank you for share code, It is very great. May you show me how to close connection?
    thk a lot!!!!

    • Juan Soto
      September 28, 2015 - 4:06 am Reply

      You close the connection in ADODB by closing the connection object. In the code above we use the variable con, so to close the connection use:
      con.close

      Join me this Tuesday on 9/28/15 for our FIRST Access lunchtime! For more details please see:
      http://accessusergroups.org/lunch/event/access-lunchtime-2015-09-29/

      Regards
      Juan

  • Jennifer
    November 24, 2014 - 8:03 pm Reply

    Juan,

    I need to convert an ADP to Access 2013. I’m trying to execute a stored procedure on a form using a textbox as a parameter. (Note: The stored procedure and form work in the ADP.) I don’t know how to apply the ADODB recordset to a multi-column listbox, and I’m getting a type mismatch error with what I think is correct. Also, I programmed in a MsgBox showing record count to see if the recordset was pulling something, which shows a record count of -1. Here’s my code:

    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

    If con.State = adStateClosed Then
    ‘con.ConnectionString = conConnection & “User ID =” & ReadGV(“UserID”, strText) & “;Password=” & ReadGV(“Password”, strText)
    con.ConnectionString = “ODBC;Description=WILMAT0;DRIVER=SQL Server;SERVER=Wilmat0.gate.USF.EDU;Trusted_Connection=Yes;DATABASE=psych”
    con.Open
    End If

    Set rs = New ADODB.Recordset
    With rs
    .ActiveConnection = con
    If bolClientSide Then
    .CursorLocation = adUseClient
    Else
    .CursorLocation = adUseServer
    End If
    .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
    .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
    .Open strSQL
    If .EOF And .BOF Then
    NoRecords = True
    Exit Function
    End If
    End With

    End Function

    The following runs when the user clicks the “Find Student” button after entering in text into the EnterText textbox. It should run the stored procedure and load the listbox, which contains LastName, FirstName, and StudentID.

    Private Sub FindStudent_Click()

    ‘ Uses OpenMyRecordset Function is EasyADODB module
    Dim strSQL As String
    Dim rs As ADODB.Recordset

    Dim strEnterText As String
    strEnterText = Me!EnterText

    Dim intRecCount As Integer
    intRecCount = 0

    strSQL = “exec dbo.GRAD_sp_lkpSearchStudentListbox @EnterText= ” & “‘” & strEnterText & “‘”

    If Me!EnterText Null Or Trim(Me!EnterText) “” Then

    OpenMyRecordset rs, strSQL

    MsgBox “Record count is ” & rs.RecordCount ‘ just checking to see if anything is returned

    Me!StudentList.RowSource = rs ‘ this is probably incorrect because I get a type mismatch error and code breaks here

    Me!StudentList.Requery

    intRecCount = Me!StudentList.ListCount
    Me!RecordCount = intRecCount & ” number of record(s)”

    Set rs = Nothing

    End If

    Me!RecordCount.Visible = True

    End Sub

    Can you help me?

    Jennifer

    • Juan Soto
      November 24, 2014 - 8:23 pm Reply

      Hi Jennifer,

      I’m sorry to hear you are having problems migrating your ADP project. As a consulting firm, we can certainly help you with this issue, would you want someone to contact you about it?

      Sincerely,
      Juan

      • Jennifer
        November 25, 2014 - 12:06 am Reply

        Thank you for the offer, but I think I can get the rest as I’ve migrated another of our ADP project. However, the Student Search screen in this Access DB is giving me problems. I normally use pass-through queries for stored procedures, but the query for the listbox isn’t picking up the EnterText textbox as a parameter. I was trying your examples with ADODB to see if that would work, but I can’t figure out how to assign a recordset to a listbox’s row source.

        Can you tell me what’s wrong with the code in my previous post?

        Thank you,
        Jennifer

        • Juan Soto
          November 25, 2014 - 3:25 am Reply

          Hi Jennifer

          As a rule we don’t offer detailed consultation on the blog, may I suggest using UtterAccess.com for help? They are great at helping out people.

          Thanks
          Juan

    • thuc
      September 27, 2015 - 11:29 pm Reply

      Me!StudentList.RowSource = rs

      try
      set me.StudentList.recordset=rs

  • Ignacio PdeA
    October 14, 2014 - 5:49 pm Reply

    Congratulations Juan for your great blog!

    I have one problem and I guess you are the one to ask for help.
    I’m working with an Access Database 2003, being managed from VS and ADODB.

    Schematically my situation is the following:

    I open a recordset #1 (use static and batchOptimistic)
    get data for first record,
    open recordeset #2 select * where data is related to #1,
    update some fields in both tables,
    use batchupdate for each recordset
    close recordset #2
    move next in recordset #1
    Loop

    My problem is that the Access database is growing too fast, and it reaches 2 GB after 20.000 records, so it gives “Invalid Argument” error and I have to close everything, compress and Repair, and restart the process.

    Is there any way to avoid this grow and be able to process 100.000 records one shot?

    Thank you for your help and best regards,

    Ignacio PdeA
    Lebanon

  • Tim
    October 5, 2014 - 6:28 pm Reply

    Juan, Please help if possible. I look for answer for days and you my last hope 🙂 I am moving to MS SQL Back end and rewriting all queries with Pass Thru to Stored Procedures. My problem is that a lot of my queries using values from list box values as a criteria in the middle of the query construction and I need to find a solution where one SP with parameters from list box will be used further in other stored procedures. How can I do that? The only way I see that is to use VBA to construct SP with parameters from access but how can I use the results in other Stored Procedures? Breaking my head…

    • Juan Soto
      October 5, 2014 - 8:41 pm Reply

      Hi Tim!

      Thanks for liking our blog! Your best bet will probably be to use a temp table in your stored procedures to hold temporary results, thus avoiding having to call another stored procedure, or create a table in your database that stores temp results by UserID, avoiding having to rewrite your procedures. I will blog about this in our LinkedIn group MS Access with SQL Server, please join us there as well!

      • Tim
        October 19, 2014 - 3:07 pm Reply

        Thank you Juan! Can you please let me know when you will post something about how to create temp tables so I can learn it. I need to start migrating to MSSQL my application ASAP. Best Regards, Tim.

  • Udo Schleucher
    August 26, 2014 - 8:49 pm Reply

    Great Work! After searching for hours on a solution to start a stored procedure by VBA you finally saved my day!
    Thanks a lot!!

  • John Gorman
    July 17, 2014 - 10:22 pm Reply

    Hi Juan,

    Thanks for the post, it has been a big help. I’m having trouble though returning a recordset from a stored procedure. Do I need to duplicate the sql code that’s in the stored procedure and use the OpenMyRecordset function? I’ve tried ‘EXEC storedprocname param1’ as the strSQL string but it errors out at .open strSQL.

    Any and all help appreciated,

    All the best,
    John

  • Select & Count - Access
    July 16, 2014 - 5:36 pm Reply

    […] in a ADO recordset object How To Retrieve a Record Count From an ADO Recordset by Ryan Farley Using ADODB recordsets and command objects in Access ADODB RecordCount – Access World Forums Recordset returning a record count of -1 (even though there […]

  • z
    September 26, 2013 - 7:43 pm Reply

    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

    • Ben Clothier
      September 26, 2013 - 8:09 pm Reply

      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.

  • Isabel
    May 21, 2013 - 2:37 am Reply

    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

  • david
    February 28, 2013 - 2:53 pm Reply

    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.

    • Ben Clothier
      March 1, 2013 - 7:10 pm Reply

      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.

  • John W. Colby
    February 6, 2013 - 3:54 am Reply

    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

  • Ed Lindoo
    December 27, 2012 - 5:33 am Reply

    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

    • Juan Soto
      December 27, 2012 - 6:11 pm Reply

      Ed,

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

      Regards
      Juan

  • Jb
    October 17, 2012 - 5:01 pm Reply

    Can we have transactions within transactions (other proc) with the same con.Begintrans?

    • Ben Clothier
      October 17, 2012 - 7:54 pm Reply

      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.

  • Jb
    October 17, 2012 - 2:05 pm Reply

    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

    • Ben Clothier
      October 17, 2012 - 7:45 pm Reply

      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():

      Hope that helps.

  • Thako
    September 2, 2012 - 5:08 pm Reply

    there is any sample database which showing this methods please?

    • Juan Soto
      September 4, 2012 - 6:21 pm Reply

      Unfortunately no

  • Blacktorres
    August 30, 2012 - 1:12 pm Reply

    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

  • Dick Weber
    August 19, 2012 - 5:42 pm Reply

    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

    • Juan Soto
      August 19, 2012 - 6:30 pm Reply

      Thanks Dick!

  • vishal
    August 12, 2012 - 5:29 am Reply

    I can not get my OpenMyRecordset, rs, strSql…

    • Juan Soto
      August 14, 2012 - 10:05 pm Reply

      Hello, can you provide more details?

  • chris smith
    August 10, 2012 - 8:40 pm Reply

    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….

    • Juan Soto
      August 10, 2012 - 11:35 pm Reply

      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

  • chris smith
    August 10, 2012 - 8:13 pm Reply

    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

  • chris smith
    August 10, 2012 - 5:28 am Reply

    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

  • keith
    June 5, 2012 - 1:21 pm Reply

    Juan! Nice piece!
    Just what I was looking for and nice and clear.

    • Juan Soto
      June 5, 2012 - 8:50 pm Reply

      Thanks Keith!

  • chris
    June 4, 2012 - 9:07 pm Reply

    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…

    • Ben Clothier
      June 4, 2012 - 10:13 pm Reply

      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.

    • Ben Clothier
      May 23, 2012 - 7:08 pm Reply

      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.

  • Jurgen
    April 30, 2012 - 7:06 pm Reply

    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?

    • Juan Soto
      April 30, 2012 - 7:16 pm Reply

      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.

  • Xpertmind
    April 2, 2012 - 8:33 pm Reply

    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?

    • Juan Soto
      April 2, 2012 - 8:36 pm Reply

      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

      • Xpertmind
        April 2, 2012 - 9:39 pm Reply

        Hey Juan it works but i need to test it on another system first. thanks for that

      • Xpertmind
        April 2, 2012 - 9:50 pm Reply

        ..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?

        • Juan Soto
          April 2, 2012 - 10:46 pm Reply

          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.

          • Xpertmind
            April 2, 2012 - 11:02 pm

            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.

  • Vince
    April 2, 2012 - 8:02 pm Reply

    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.

    • Juan Soto
      April 2, 2012 - 8:13 pm Reply

      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

  • alan elger
    February 23, 2012 - 3:44 pm Reply

    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?

    • Juan Soto
      February 23, 2012 - 5:35 pm Reply

      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

      • PC
        March 17, 2012 - 3:10 pm Reply

        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.

  • John
    November 9, 2011 - 9:19 am Reply

    What is ReadGV function in your code?

  • Gerry
    October 15, 2011 - 7:05 pm Reply

    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?

    • Juan Soto
      November 9, 2011 - 8:19 pm Reply

      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

Leave a Reply

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

 

Contact Us
[gravityform id="16" title="false" description="false"]