Re: ADO Recordset

I may have spoke too soon. I plugged the string into the ODBC Connect String
box, and it worked the first time. When I saved and closed and re-ran the
query, I'm now getting a "function sequence error".

There has got to be something wrong with the Microsoft ODBC for Oracle
driver, since I can connect to the Oracle database with TOAD and/or MySQL
with no problem.



"Douglas J. Steele" wrote:

You have to loop through the recordset.

rs.Open strSQL, cn

MsgBox rs.RecordCount

Do While Not rs.EOF
MsgBox rs.Fields(0)
rs.MoveNext
Loop

rs.Close

However, if what you've got as the connection string is working for you, you
should be able to use

ODBC;Driver={Microsoft ODBC for Oracle};SERVER=penprd0;uid=myuid;pwd=mypwd;

as the Connect string for your pass-through query. (It has the added benefit
that you won't need a DSN)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Kirk P." <KirkP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:493CB8AA-BBE3-40E9-AF09-E0143BC6F1AD@xxxxxxxxxxxxxxxx
I'm retrieving records from a back end Oracle database. Normally I set up
a
DSN and use the Microsoft Oracle ODBC driver and get what I need via a
pass
through query. For whatever reason, setting up this DSN isn't working, so
I'm pursuing another option.

This code gets me into the Oracle database (the number of records returned
is correct), but how do I get it to actually display the records in a
query?

Sub ConnectTest2()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM ec_currencies"

cn.CursorLocation = adUseClient

cn.Open "Driver={Microsoft ODBC for Oracle}; " & _
"SERVER=penprd01; uid=myuid;pwd=mypwd;"

rs.Open strSQL, cn

MsgBox rs.RecordCount

rs.Close
cn.Close

End Sub



.

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... This is not passthrough query sql. ... and should be translated into ODBC SQL. ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Dev Ashish Relink Tables
    ... If it's not an ODBC connection, ... Dim i As Integer, strDBPath As String, strTbl As String ... Dim tdf As TableDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using IF to find whether a Merge Field contains a substring
    ... If this is a one-off, and you have either Excel or Access, I would probably ... Dim strConnection As String ... ' Construct an ODBC connection string ... ' Construct a Query string. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Dev Ashish Relink Tables
    ... strTbl if it exists. ... If it's not an ODBC connection, ... Dim i As Integer, strDBPath As String, strTbl As String ...
    (microsoft.public.access.modulesdaovba)