Solved

Porting Access data to SQL

Posted on 1998-07-29
4
236 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?
0
Comment
Question by:wrichard
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:wrichard
ID: 1089305
Edited text of question
0
 

Accepted Solution

by:
zimmy earned 30 total points
ID: 1089306
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
 
LVL 1

Author Comment

by:wrichard
ID: 1089307
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
 

Expert Comment

by:zimmy
ID: 1089308
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question