DAO / RDO

[Andyroo]

I have VB version 5 professional edition. I am trying to ultimately connect to an ORACLE database via ODBC, but an access database via ODBC would do for now.

Can this be done with DAO or do you have to use RDO.

This next bit of code works fine with an access file specified via a path, but what about ODBC?

'Dim db As Database
'Dim rs As Recordset
'Dim sSQL As String

OpenDatabase( _
"c:\ProgramFiles\DevStudio\Vb\Biblio.MDB")
sSQL = "Select * from Titles"
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

rs.MoveFirst
While Not rs.EOF
List1.AddItem rs.Fields(0)
rs.MoveNext
Wend
rs.Close

You can use either DAO or RDO if you like but as far as I'm aware microsoft are discontinuing RDO in favour of ADO(Activex Data Object) which is what I use all the time.
To use ADO to connect to an ODBC source(regardless of the database) I do the following
Dim cnn as ADODB.connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=;uid=";pwd="
cnn.Open
Where the DSN is the data source name you have set up in your ODBC in Control Panels
The uid and password are used if there is a username and password set up in your database
All these values should NOT be inclosed in quotes.
Obviously there are a lot more options that your ADO Connection can have but I hope this gives you a start.

If you really want to use DAO then I do it the following way
Set db = OpenDatabase("", dbDriverNoPrompt, True, "ODBC;UID=;PWD=;DSN=")

Then set the values for the DSN,UID and PWD as I explained for ADO.
Cheers
Tony