[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Porting Access data to SQL

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?
0
wrichard
Asked:
wrichard
  • 2
  • 2
1 Solution
 
wrichardAuthor Commented:
Edited text of question
0
 
zimmyCommented:
You can't do it from inside SQL 6.5. It doesn't read Access databases. But you don't need an outside tool, either.

The easiest way is to do a file export from Access to text files, and then use bcp to load the text files into SQL Server tables.

The Access export and bcp load can be contained in a command file that the scheduler can kick off.

If you need more details, I can point you to the documentation.

Hope this helps.

Zimmy
0
 
wrichardAuthor 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
0
 
zimmyCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now