Read ADO with DAO

[Brian]

Hello all,

I have a current Access database that uses linked tables
to an Oracle db. I am trying to migrate to a DNS-less
enviroment using ADO.

I can successfully created an ADO connection to the Oracle
database and can open the various recordsets. But I would
like to create local tables based upon these ADO
recordsets. I am most familar with DAO.

Is there a way to reference the ADO recordset in VBA with
DAO and create local tables??

Once the local tables are created (using the current
names) all of my queries should still work.

Any pointers would be greatly appreciated

 

[Larry Linson]

You didn't say whether your database is an MDB, MDE, ADP, or ADE. You should
be able to create the tables inside Access, join them in the server DB (you
seem to be as familiar with that as I am, and likely more so), and create an
update query to populate them.

Larry Linson
Microsoft Accesss MF

 

[Brian]

I apoligize for leaving out some detail.

It is a MS Access 97 MDB database, using ADO 2.1

I do not want to use the DAO linked tables at all because
it requires a ODBC connection.

So I am using ADO to create a recordset, but the recordset
is not local, it is in memory. I would like to use ADO to
create a temp table, or rather a local table, of the data.
Then from there use either DAO or queries to manupulate
the data.

This is where my disconnect is, I do not understand how to
save an ADO recordset to the same local database after
opening the recordset.

Thanks for your help.

Brian

 

[Douglas J. Steele]

Why not see whether you can modify the code at
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html to work with
Oracle (my example is for SQL Server)? Then your linked tables won't require
a DSN.

 

[Brian]

Thanks for your suggestion.

The only problem i have with it is that although I would
not be DNS dependent, I still have to have the ODBC
drivers installed on the client machine. Worst yet is that
even if I use the well distributed Microsoft ODBC for
Oracle driver, Oracle 8i requires a TNS.ora file corectly
configured and placed on every machine that would use this
Access Database to connect to the Oracle dB

Because I shape the data quite a bit from five base tables
before it ends up in a report, I wanted to create temp
local Access tables for speed and performance. I could
make several ADO connections and shape the data in memory,
but would prefer local tables.

So far in my research ADO can create persistent local
recordsets, but only in XML or a propriatory format called
called ADGT. Access 97 does not have XML capability and
ADGT is propriatory.

Thanks for your help.