Hi people.

I have to convert Excel5 Visual Basic macros to excel7.
My macros get data from an oracle database and put the results in an
excel sheet.
Those macros use old query commans like :
       SQLOpen,  SQLExecQuery, SQLRetrieve, SQLClose
but these commands are not supportd any more in excel7. It seems I have
to use DAO language.
I tried to use it but I never managed to get connected. I understood
that there is two different ways to
connect to databases :
        using microsoft Jet and OpenRecordset
        using Direct sql and QueryDef
I tried both of them but in both way, a .mdb file is asked which I don't
have as I don't have and don't
want to have Access.

What I need is some code example to connect to an external database via
ODBC. That code
must be able to get data from the database into excel sheets.

Thanks in advance,

Eric Le Bourvellec

[This followup was posted to comp.lang.basic.visual.database and a copy
was sent to the cited author.]

IE

' Note this is VB4 code, it SHOULD be VBA compatible but it may need
' tweaking

' Dim Variables
Dim dbDatabase as Database
Dim rsRecordset as Recordset

' Open ODBC Database, see the note below about the last parameter.
set dbDatabase = dbengine.workspaces(0).opendatabase("", False, False,
"ODBC;DSN=<Your ODBC DSN>;UID=<User ID For Database>;PWD=<User
Password>;Database=<Database name>")

' Open the recordset (Note the dbSQLPassthrough for ODBC tables)
set rsRecordset = dbdatabase.openrecordset("SELECT * FROM <Table Name>",
dbopensnapshot, dbSQLPassThrough)

while not rsRecordset.eof

        ' Syntax for accessing fields (Cant be used for field with spaces
        ' in, also the field name must be hard coded
        debug.print rsRecordset!<field name>

        ' Or, for when the field name has spaces, or has to be a variable
        debug.print rsRecordset.Fields(<field name>).Value

        ' Move to the next record in the recordset
        rsRecordset.Movenext

wend

' Close Recordset after use
rsRecordset.Close

' Close Database after use
dbDatabase.Close

End code fragment

That will open up the database and open up a recordset which you can go
through.

Notes on the OpenDatabase last Parameter.

You dont need all of the parameters, but here is what each one does

ODBC;                   Specifies an ODBC type Database <Required>

DSN=<DSN Name>;   Specifies which Data Source Name to use (This can be
                        a variable.  If this is omited you will get a list
                        of data sources setup on the machine.

UID=<User ID>;    The User ID you want to log in as.  If either this
                        and/or the password is omited you will get a login
                        box for your ODBC database

PWD=<Password>;   Password for the Database you want to log in to.

Database=<DB Name>;       Database name you want to log in to (The DSN doesnt
                        always have one specified in it).  If this parameter
                        is omited it will use the default one setup in the
                        DSN, or present a login box if it is missing.

Anyway I hope this helps, if you need some more info drop me an e-mail.

Dave Mathias
Analyst Programmer
Microsoft Certifed Product Specialist (Windows 95)

Phone  : +44-(0)1329-282800 (Work)
Phone  : +44-(0)958-272514  (Mobile)
Fax    : +44-(0)1329-827766 (Work)