Re: ODBC Connection returns no Data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I don't think you can in VBA, unless you want to try using ODBCDirect
with DAO (shudder - the Dark Ages) ... You could start searching
through the ODBC stuff in the msdn library, but it's all Greek to me.
Start here: http://msdn2.microsoft.com/en-us/library/ms710252.aspx

Why not roll back the odbc driver to the version that works until you
see what the product support people come up with? It may be that they
already have a hotfix or at least a simple workaround ...

Frank wrote:
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"

--
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: 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
    ... so it's a proprietary database for which an ODBC driver is supplied ... String returns data. ... in the command text, there is an error, that ADO can't find the field. ... Dim conConn As New ADODB.Connection, ...
    (microsoft.public.data.ado)