[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Porting Access data to SQL

Posted on 1998-07-29
4
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:wrichard
ID: 1089305
Edited text of question
0
 

Accepted Solution

by:
zimmy earned 120 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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