Re: ODBC Connection returns no Data

Frank wrote:
Hello,
I'm connecting to an odbc datasource (here: AR System ODBC Driver)

What is "AR System"?

with ADO
2.8 in a Microsoft Access XP Database with VBA. I define the
Connection String, the command string and a recordset, open the
connection and open the recordset. All these Actions complete without
error. But the recordset contains no data. The loop "While Not
rs.EOF" is not being entered. I can enumerate the Fieldnames of the
recordset, the state of the recordset and the connection are "Open".
But the command "rs.MoveNext" fails with the error "Driver not
capable".

?
That is not the message one would receive if the recordset is empty. You
should receive a message that says something about BOF and EOF being
True
You had better show us your code.

The same command string (SELECT...), pasted in an Excel MS
Query, returns data. I think the ODBC driver version changed but has
anyone here an idea? Why can a recordset be opened successfully, but
returns no data?



Let's get rid of this first misconception: data not being returned will
not prevent a recordset from opening. It is a common practice to open a
recordset using a query that retrieves no records when all one wants to
do is add records to the data source. You need to check EOF immediately
after opening the recordset to see if it contains records, because a
recordset's cursor will always point at the first record if records
exist. If you've navigated through the recordset or deleted records from
it, then you need to check both BOF and EOF to determine if it still
contains data.

I don't understand this bit about "the ODBC driver version changed " -
isn't the Excel MS Query using the same ODBC driver that your VBA code
is using? Why is the ODBC version relevant?




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: MDAC 2.8 vs MDAC 2.7
    ... > yield better performance than a less efficient native OLE DB provider. ... > "Val Mazur" wrote in message ... It is not just because it is ODBC driver. ... there are no records in a recordset or it is a bug. ...
    (microsoft.public.data.ado)
  • RE: Protocol Error in TDS Stream vs Network Packet Size
    ... We reported this problem and finally we confirmed that it is a bug. ... For MSQuery, ODBC driver is used which has a problem in retrieving large ... the field definition for the recordset is too large to fit in one ...
    (microsoft.public.sqlserver.connect)
  • Re: Endlos-Furmular im ungebundnen Formular
    ... Aber wie kann ich Einträge in ein leeres, ungebundenes Endlosformular eintragen lassen per VBA? ... Unter Umständen könnte ein Disconnected Recordset für Dich von Interesse sein. ... Wenn der Benutzer die Bearbeitung beendet hat, stellst Du die Connection wieder her und machst einen BatchUpdate. ...
    (microsoft.public.de.access)
  • Re: MDAC 2.8 vs MDAC 2.7
    ... Stability of the provider depends on quality of the code inside of it and ... It is not just because it is ODBC driver. ... >> If just changing of the connection string to use OLEDB provider solves ... But do you get an actual recordset opened in your case? ...
    (microsoft.public.data.ado)
  • Re: MDAC 2.8 vs MDAC 2.7
    ... an extra layer over an efficiently written ODBC driver can yield ... better performance than a less efficient native OLE DB provider. ... It is not just because it is ODBC driver. ... >>> there are no records in a recordset or it is a bug. ...
    (microsoft.public.data.ado)