Append tables from mdb to SQL Server 2005

[Guest]

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.

 

[Guest]

Well there are several things missing from your brief description that are
important to the discussion. What version of SQL Server are you using?
2000? 2005? How big are the tables you are trying to import? Do the input
tables have fixed formats? Is this a one time shot or is this a recurring
task? Do the input tables have fixed formats?

If these are large tables and this is a recurring task I would look to
writing DTS packages. Yes you can call a DTS packages from Access and get a
return value however the initial learning curve is somewhat steep. DTS is a
SQL 2000 term and its been changed in 2005. I haven’t had the pleasure of
this task in SQL 2005 yet but I know it’s coming.

This should get you going in the right direction. If you would like
further direction on this just post your questions.

Good luck,
RJ

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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