ADP best practice: importing

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
Back
Top