ADO to mdb joins

J

jmonty

I would like to know, first, if it is at all possible and second, if so, how
to:
1. Join SQL Server 2000 table with an Access 2003 mdb (local) table
2. Append to a local (mdb) table with data from a SQL Server table, etc.
The caveat: Do this NOT using a linked (SQL) table, but using an ADO
connection.
Currently, I am using an (VBA) ADO connection to return an ADO recordset,
then having to loop through that recordset and DoCmd.RunSQL to append the
records to the local table. It seems very inefficient, but it does work.
I am hoping that there is a better way.
Please show me the VBA code.
Help!
 
M

MGFoster

jmonty said:
I would like to know, first, if it is at all possible and second, if so, how
to:
1. Join SQL Server 2000 table with an Access 2003 mdb (local) table
2. Append to a local (mdb) table with data from a SQL Server table, etc.
The caveat: Do this NOT using a linked (SQL) table, but using an ADO
connection.
Currently, I am using an (VBA) ADO connection to return an ADO recordset,
then having to loop through that recordset and DoCmd.RunSQL to append the
records to the local table. It seems very inefficient, but it does work.
I am hoping that there is a better way.
Please show me the VBA code.
Help!
Click to expand...

Get data from SQL Server table w/o linking table: Use an SQL Pass
Through (SPT) query to get SQL data. Create a regular Access query to
read the SPT query and append data to Access table.

You'd not use ADO at all, just the Access queries.
 
P

peregenem

jmonty said:
Not far off from what I was doing
Click to expand...

If it's OK to relax the caveat about an ADO connection, you could do
something like this:

INSERT INTO MyJetTable
(key_col, data_col)
SELECT CustomerID AS key_col, CompanyName AS data_col
FROM [ODBC;Driver={SQL
Server};SERVER=(local);DATABASE=Northwind;UID=sa;Pwd=;].Customers;
 
Top