ADO to mdb joins

[Guest]

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!

 

[MGFoster]

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!

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.

 

[Guest]

Not far off from what I was doing, just didn't know how to combine the two.
I wnet to the MS Knowledgebase: http://support.microsoft.com/kb/q112108/
for the example.

I am gonna try it.
Thanx for the help!

jmonty

 

[peregenem]

Not far off from what I was doing

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;