ADO Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
Thanks - I like the 2nd option much better!

Douglas J. Steele said:
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)
Click to expand...
 
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.
 
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)
Click to expand...

BTW, Doug, I used your code to convert to DSN-less connection
strings yesterday, and it worked like a treat.

I did have to make a couple of changes, based on assumptions in your
code:

1. your code assumes all linked tables are ODBC. In my app, there
are links to several different data sources, including two different
ODBC data sources and at least 3 MDB back ends.

2. your code doesn't differentiate ODBC sources.

So, I had to eliminate all non-ODBC sources and all non-SQL Server
sources. I just did it with ad hoc changes to the conditionals in
the inner loop that populated the array listing the tables, so it's
not a generalized solution, but I thought you might have an idea
about a way to generalize to incorporate some of those features. At
least, I think, it would be better to only bother with ODBC links.
 
David W. Fenton said:
BTW, Doug, I used your code to convert to DSN-less connection
strings yesterday, and it worked like a treat.

I did have to make a couple of changes, based on assumptions in your
code:

1. your code assumes all linked tables are ODBC. In my app, there
are links to several different data sources, including two different
ODBC data sources and at least 3 MDB back ends.

2. your code doesn't differentiate ODBC sources.

So, I had to eliminate all non-ODBC sources and all non-SQL Server
sources. I just did it with ad hoc changes to the conditionals in
the inner loop that populated the array listing the tables, so it's
not a generalized solution, but I thought you might have an idea
about a way to generalize to incorporate some of those features. At
least, I think, it would be better to only bother with ODBC links.
Click to expand...

Glad you liked it.

Good suggestions, but there's only so much you can do in an explanatory
example. Addendum 3 near the bottom of the page does (sort of) discuss the
scenario you're describing, and shows how to only worry about ODCB links,
but essentially leaves the rest as an exercise for the reader. <g>
 
XML is a simple and flexible system for defining data formats. This is completely platform independent and adopted everywhere for representing complex documents and data structures. For data transmission on web, its having significant contribution.
Now days it's been the major use of XML to store both rowset (single table) and hierarchical (multiple-table) data in it.



http://www.mindfiresolutions.com/Retrieving-dynamic-Xml-based-record-set-333.php



Kirk wrote:

ADO Recordset
20-Sep-07

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.Connectio
Dim rs As New ADODB.Recordse
Dim strSQL As Strin

strSQL = "SELECT * FROM ec_currencies

cn.CursorLocation = adUseClien

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

rs.Open strSQL, c

MsgBox rs.RecordCoun

rs.Clos
cn.Clos

End Sub

Previous Posts In This Thread:

ADO Recordset
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.Connectio
Dim rs As New ADODB.Recordse
Dim strSQL As Strin

strSQL = "SELECT * FROM ec_currencies

cn.CursorLocation = adUseClien

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

rs.Open strSQL, c

MsgBox rs.RecordCoun

rs.Clos
cn.Clos

End Sub

You have to loop through the recordset. rs.Open strSQL, cn MsgBox rs.
You have to loop through the recordset

rs.Open strSQL, c

MsgBox rs.RecordCoun

Do While Not rs.EO
MsgBox rs.Fields(0
rs.MoveNex
Loo

rs.Clos

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

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 MV
http://I.Am/DougSteel
(no e-mails, please!


Re: ADO Recordset
Thanks - I like the 2nd option much better

:

I may have spoke too soon.
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


:

Re: ADO Recordset
Afraid I have no experience with Oracle

You can check
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx
and/or http://www.connectionstrings.com/?carrier=oracle to see whether
there's a different connection string you can use. You have no choice but to
use ODBC, and remember to put ODBC; in front of whatever they suggest

--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(no e-mails, please!



Re: ADO Recordset


BTW, Doug, I used your code to convert to DSN-less connection
strings yesterday, and it worked like a treat.

I did have to make a couple of changes, based on assumptions in your
code:

1. your code assumes all linked tables are ODBC. In my app, there
are links to several different data sources, including two different
ODBC data sources and at least 3 MDB back ends.

2. your code doesn't differentiate ODBC sources.

So, I had to eliminate all non-ODBC sources and all non-SQL Server
sources. I just did it with ad hoc changes to the conditionals in
the inner loop that populated the array listing the tables, so it's
not a generalized solution, but I thought you might have an idea
about a way to generalize to incorporate some of those features. At
least, I think, it would be better to only bother with ODBC links.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: ADO Recordset

Glad you liked it.

Good suggestions, but there's only so much you can do in an explanatory
example. Addendum 3 near the bottom of the page does (sort of) discuss the
scenario you're describing, and shows how to only worry about ODCB links,
but essentially leaves the rest as an exercise for the reader. <g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# And The Little Iterator That Could
http://www.eggheadcafe.com/tutorial...32-0ae26adaa533/c-and-the-little-iterato.aspx
 
Back
Top