Solved

Porting Access data to SQL

Posted on 1998-07-29
4
207 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
Comment Utility
Edited text of question
0
 

Accepted Solution

by:
zimmy earned 30 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now