Re: ODBC Connection returns no Data

Hello Bob,
thank you very much. At least only one question: can I also work directly
against the ODBC driver with vba code? I have experience with
API-Programming. Do you have one tip how to begin?

Thanks an bye
Frank

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:OmmDShCAIHA.4956@xxxxxxxxxxxxxxxxxxxxxxx
news.microsoft.com wrote:
Hello Bob, thank you for the quick and deep answer.
AR System is the Remedy Action Request System, a Ticket-
(Problemmanagement-) System.

OK, so it's a proprietary database for which an ODBC driver is supplied

It's right, that recordsets open even without data, but the
SQL-Commandtext should return data.

?
Oh! I think you mean that the statement you've supplied for the
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.

MS Query works directly against the ODBC driver.
ADO adds an extra layer: the OLEDB Provider for ODBC databases. I suspect
your new ODBC driver is not interfacing well with the new driver version.
You should definitely contact support. They will likely need a better
repro script than the one you provided below (which is OK since it's
unlikely any of us have a copy of this AR system to enable us to attempt
to reproduce the problem)

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,

Nothing to do with your problem, but this is bad practice. You should
remove the "New" keyword from these Dim statements and use Set to
initialize these variables, i.e.,

Dim conConn As ADODB.Connection
Set conConn = New ADODB.Connection
Dim rsODBC As ADODB.Recordset
Set rsODBC = New ADODB.Recordset


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: ODBC Connection returns no Data
    ... so it's a proprietary database for which an ODBC driver is supplied ... in the command text, there is an error, that ADO can't find the field. ... MS Query works directly against the ODBC driver. ... Dim conConn As New ADODB.Connection, ...
    (microsoft.public.data.ado)
  • Re: ODBC Connection returns no Data
    ... I don't think you can in VBA, unless you want to try using ODBCDirect ... Why not roll back the odbc driver to the version that works until you ... Dim conConn As New ADODB.Connection, ... header is my spam trap, so I don't check it very often. ...
    (microsoft.public.data.ado)
  • Re: vb e transaction with ms access
    ... It suppose to work faster with OLEDB just for one reason - ADO doe NOT work ... ODBC driver between ODBC driver and the code. ... The clients have an open connection for the duration of the ...
    (microsoft.public.vb.database.ado)
  • Topspeed 2 SELECT statement : Concatenate multiple fields
    ... What is the syntax for concatenating fields within a SELECT statement ... Topspeed 2.0 ODBC driver via an ADO 2.7 Recordset object? ...
    (comp.lang.clarion)
  • Re: Which is the best provider for SQL Server 2000 and VB6
    ... OLEDB Provider for SQL Server is the best choice. ... Second - ADO does NOT work with ODBC driver directly and in a case if you ...
    (microsoft.public.data.ado)