ADP best practice: importing

[Guest]

I have an Access ADP project, linked to SQL Server 2000 SP3.

I need to import data from another Access database (.MDB) into the ADP / SQL
Server. Apparentely I cannot link MDB tables from my ADP project.

What is an easy way to import this data into an existing SQL Server table?

I have looked at the TransferSpreadsheet action, but unfortunately the data
isn't in an Excel file, it is in an Access MDB.
I need to import an entire table, no need to filter rows. Also, I don't want
to give "CREATE TABLE" privileges on the SQL Server to the Access user.

PS: I'm an SQL Server programmer, very junior in Access.

 

[Roger Carlson]

If you're a SQL Server programmer, you must be familiar with DTS. Why not
use that?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Joe Fallon]

1. Try DTS.
2. Set up a linked server. It will work fine against an .mdb.

 

[Guest]

Thanks to both of you.
I failed to mention that the mdb belongs to the users, and I want to let
them import the mdb data into the adp by themselves.
I don't want to give them any priviledges on the SQL Server. So they would
be unable to start a DTS package.

I'll try the 2nd suggestion, tell them to move the mdb onto the server via
FTP, and they'll have a button in the ADP kicking off a sp that will load the
data from the (now local) mdb as a linked server.

 

[Sylvain Lafontaine]

Then the easiest way might be to use ADO objets to open your mdb databases
and then transfert data to the SQL-Server via read/write operations beetween
two recordsets.

S. L.