We help IT Professionals succeed at work.

Porting Access data to SQL

wrichard
wrichard asked
on
Medium Priority
278 Views
Last Modified: 2010-03-19
I need to create a scheduled activity that will get data from multiple access databases (.mdb files) and place them in an sql database.  Both the sql database and each of the access databases will have the same table layouts.  Is there a way to do this from inside of SQL 6.5 or do you have to use an outside tool?  If an outside tool is needed, do you have any recomendations?
Comment
Watch Question

Author

Commented:
Edited text of question
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Please point me to some documentation.  

Here is some more insight on my challenge....
I'm going to have multiple .mdb files uploaded nightly.  I need to import the data from the .mdb file to SQL on a scheduled basis every morning.  The .mdb files will have dynamic names.  I was thinking about using VB to access the data in access and then import it into SQL.  What is your opinion?
Would it be easier to use VB to automate the process or your original suggestion,
The Access export and bcp load can be contained in a command file that the scheduler can kick off.

Please direct me to some documentation if you can.

Thanks,
wrichard

Commented:
Wrichard,

I'm assuming you're using SQL Server 6.x. In either the hardcopy or SQL Books Online, look in Administrator's Companion, Chapter 11 for bcp documentation. bcp syntax is given in TSQL Reference, in the section Utilities and Executables.

I'm assuming that you know how to export a file from Access. If not, let me know.

Given that your Access mdb files are going to have dynamic names, VB might be a good choice.

The other option that I thought of over lunch would be to use a separate Access db (kind of the way you are thinking about using VB). This separate Access db would link both the uploaded mdb tables and the SQL Server tables. You could run an insert query to move the data from mdb to SQL.

One of the things to think about is how many records will be in a table, and whether speed is an issue. If you have more than 5,000 or so records, bcp will probably be faster (if you can drop and rebuild the indexes). That's because there's an option to do the bulk copy without logging each record. It goes FAST!

If there aren't too many records in any one table, then it's probably easier to build and maintain the VB/Access solution.

So, bottom line.

Because of the dynamic file names, you need to use either VB or Access.

If you have a lot of records, and speed is an issue, AND you can drop and restore SQL indexes, then bcp is the way to go. (Be sure to set 'Select Into/Bulk Copy' option to TRUE.)

Otherwise use VB or Access to do an insert query.

How's that?

Zimmy
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.