Accessing a Database from VBA

Posted by Rob Jackson on October 17, 2001 1:32 AM

I need to access a FoxPro and an Oracle database from Excel using VBA. I am relatively familiar with SQL but I don't understand fully how to write SQL statements in to my VBA code. Anyone got any suggestions or advice?

Posted by Leroy on October 17, 2001 2:39 AM

Rob,

Are you using DAO to access your DBs? If so, I always find OpenRecordset method to be quite useful (and simple).

eg: Set rec = db.OpenRecordset(Script, dbOpenSnapshot, dbSQLPassThrough)

I don't know whether you need it - but from the start:

1. Dim a database and recordset variable
eg: Dim db As Database
Dim rec As Recordset

2. Open ODBC connection
eg: Set db = OpenDatabase("", 0, 0, ConnectString) 'Open the database (See help for valid ConnectString)

3. Build SQL statement into a variable
eg: Script = "select dummy from dual"

4. Open the RecordSet
eg: Set rec = db.OpenRecordset(Script, dbOpenSnapshot, dbSQLPassThrough)

5. This gives you a recordset you can loop through like a cursor (See help). The field "dummy" in eg would be retrieved by: Var = rec![dummy]

6. Remember to close objects rec and db when finished ie: rec.Close, db.Close

Hope this makes some sort of vague sense and is of some help to you.

Posted by Rob Jackson on October 17, 2001 3:12 AM

Thanks Leroy, just what I was looking for.