Re: ODBC Connection returns no Data

Hello Bob, thank you for the quick and deep answer.
AR System is the Remedy Action Request System, a Ticket-
(Problemmanagement-) System.
It's right, that recordsets open even without data, but the SQL-Commandtext
should return data.
If I paste the Commandtext in the MS Query SQL Editor via Excel, the String
returns data. But in the VBA-Code, rs.EOF returns true.
But I can enumerate the recordset-fields an if I change a fieldname in the
command text, there is an error, that ADO can't find the field.
We had an update of the AR System Frontend and the ODBC Driver version
changed. Before the update, the same code worked. OK, I should contact
the support at first, but I don't understand, why ADO does'nt work. And Yes,
I think, Excel uses the same driver. That is, what I don't understand.
I tried to change the CursorType, the CursorLocation, I tried to get the
Recordset via connection.Execute, I tried the Connection string via
a ODBC DSN name, I tried to specify only one fieldname without
"WHERE"-Clause, I tried different parameters in the connection string, I
tried different
parameters in the "Open"-methods
I have no idea anymore.
Here some Code:

Dim conConn As New ADODB.Connection, rsODBC As New ADODB.Recordset

strConnect = "DRIVER={AR System ODBC Driver};UID=" & sUser & ";PWD=" & sPW &
";SERVER=NotTheServer;ARAuthentication=;ARNameReplace=1;ARUseUnderscores=1;ARServer=XYZ;TABLE=TableName"

strRs = "SELECT Field1, Field2_, Field3 from TableName"

conConn.ConnectionString = strConnect
conConn.Open

rsODBC.Open strRs, conConn

'following returns "EOF!"
If rsODBC.EOF Then MsgBox "EOF!"

Do While Not rsODBC.EOF
... code, that is not executed
rsODBC.Open.MoveNext
Loop

....

Thank You in advance
Frank

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:u7ABWY6$HHA.1184@xxxxxxxxxxxxxxxxxxxxxxx
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: ODBC Connection returns no Data
    ... I'm connecting to an odbc datasource (here: AR System ODBC Driver) ... in a Microsoft Access XP Database with VBA. ... Connection String, the command string and a recordset, open the ...
    (microsoft.public.data.ado)
  • 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)