asked on

Cannot navigate a form connected using ADODB

A have an Access form and have connected it to an sql server table using ADODB by setting the form's recordset property to the ADO recordset. The first record comes up fine, but when I navigate to the next record, my ADO recordset property goes to a state of 0. While the form displays the next record in the set, the form's OnCurrent event won't fire.
What is closing my recordset? I'm not doing any requering or anything?

Anders Ebro (Microsoft MVP)

Not exactly an answer I guess, but a warning. I have tried a few times to use ADODB recordsets instead of DAO recordsets, but never got it work well. Its just too unstable, and there are various small gotchas, where they just perform differently than DAO recordsets.

Try adding a filter or a sort, and you will likely see what I mean.

Scott McDaniel (EE MVE )

Can you show the code you're using to set the Form's Recordset, and the code you're using to create the Recordset? You have to create that recordset correctly, or you can run into some issues.

Mel Brooks

ASKER

The connection variable and recordset variables are saved in a public module

Public cnContacts as New Adodb.connection
Public rsContacts as New Adodb.recordset

In the Main form Open Event:
cnContacts.ConnectionString = strdbConnection
cn.Contacts.CursorLocation = adUseClient
cnContacts.OPen

On a combobox after update event:
str = "select * from Contacts where ClientID = " & ComboboxName
Set frm = Contacts_SubForm.Form
If rsContacts.STATE = 1 Then
rsContacts.Close
Set rsContacts = Nothing
End If
rsContacts.Open str, cnContacts, adOpenDynamic, adLockPessimistic
Set frm.Recordset = rsContacts

The subform populates with the records, but when navigating to the next record , rsContacts state changes to 0 even though the form displays the data of the next record

Fabrice Lambert

Looks like a variable scope issue.
Where are they declared ?

Please, post the full code of your form's open and combobox's after update events, there are not enough informations in those snipets.

Scott McDaniel (EE MVE )

Have you seen this article about using an ADO recordset with an Access form:

https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/bind-a-form-to-an-ado-recordset

That article mentions two caveats:

-- The underlying ADO recordset must be updatable via ADO.
-- The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

Does your recordset adhere to those caveats?

ASKER CERTIFIED SOLUTION

Mel Brooks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

re: "This wasn't the solution I wanted as its tedious and time consuming to write the code for this"

Which is why, on your other post, several of us recommended using linked tables and bound forms.

Dale

Mel Brooks

ASKER

I thank everyone for their comments. I just want to mention that my form was originally setup using linked tables, but we recently found that a memo field in the table would not take more than 8000 characters even the though the sql column is set as varchar(max). We're not sure if this has always been the case or a recent driver update started causing the problem.

Dale Fye

What SQL Server driver are you using?

Mel Brooks

ASKER

Not totally sure because we have well over 100 machines, mostly Windows 10. I believe the majority are using SQLNCL11, but some may be using SQLNCL10. I know that on my development machine I'm using SQLNCL11, and I got the ODBC error when trying to paste more than 8000 characters into memo field with the ODBC link.