Append tables from mdb to SQL Server 2005

[Klatuu]

With some great help, I am getting my upsized adp working. Now here is what
I need next.

The adp is named PM. Another mdb named RP needs to append data from 6
tables in it's linked mdb database to the PM SQL database. It is also
important that we receive a return from SQL so we know the process completed.

The RP application will be converted later.

Suggestions, please.

 

[Klatuu]

Ac2003, SQL Sever 2005
It will be a recurring task.
I have no knowledge of DTS.
The individual mdb tables are not very large, but they will be combinde in
the SQL. That is the purpose. The issue is in the current environment, as
many as 20 currently and expecting growth to 100, users using different back
ends will be submitting data. All are appending to one mdb as a batch.
Guess how slow that is! So we have moved the receiving database to SQL.
As it is, I have figured out a method. I will link to the SQL tables with a
different table name, run the append queries (7 for 7 tables), and drop the
links.

Any thoughts?

 

[RJ]

I laughed at your "guess how slow" comment.

If your import record count is low your methodolgy should be fine. I had a
conversion where I needed to import 100 k+ text records and HAD to learn DTS.
DTS is a great way to handle bulk copying in SQL.

Good luck!
RJ

 

[Klatuu]

Thanks, RJ.
I may get time to look at DTS later, but for expediency's sake, I am going
with the current plan.