Solved

Running sql script and loading flat file data into MSAccess 2007 database

Posted on 2012-03-28
6
391 Views
Last Modified: 2012-08-14
Hello,

 I have a huge sql script which needs to be executed in MS Access database to load data.
 
 Question 1:
 Which is the best way to execute the script given that its close 999MB?

 Question 2:
 Is there a way to run it from command line? Would that be faster?

 Question 3:
 I also have data in flat file that needs to be loaded into Access database.
 Will BCP work against Access database? If not, is there a equivalent of BCP to bulk load data into Access database?

 
Thanks!
0
Comment
Question by:sath350163
  • 3
  • 2
6 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 37780180
The maximum size of a sql command in Access is 64k.
If your script is made up of a series of sql commands then they cannot be run as a single script in Access. Access only runs one command at a time.  To run multiple commands you must execute each command separately.

If you would like to explain what you are trying to achieve then maybe someone can suggest an alternative route.
0
 

Author Comment

by:sath350163
ID: 37780194
I have a sql script with insert and update statements that needs to be executed every month against a MS Access 2007 database.
The sql script size is 1GB and have multiple insert and update statements.
To be more specific, my sql script has 15,000 insert statements and 1 million Update statements.

I'm also thinking along the lines of somehow getting the necessary data exported to a CSV file (instead of as sql statements in a script file), and then loading the data from that CSV into MS Access database.
But I want to do the loading from command line.
If this is possible, is there also a way to specify, database name, table name into which data must be loaded, and a way to mention that the first line in the csv file has the column names.

Thanks!
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 37780300
Can I preface all of this by saying that if we had some info on the purpose and context of this process you might get more  useful help.

The only program which knows about the structure of an Access database is Access - or more strictly, the Access database engine (called JET pre-2007 and ACE from 2007 onwards - but it's the same thing) .  So you have to be calling a program which includes a reference to this engine.  The most obvious choice for this is Access itself but you could write a program in VB or other .net language.
Also, you mentioned BCP n your Q - so does that mean this data is coming from SQL Server?  If so then shouldn't you be looking at using SQL Server Integration Services  to do the transfer directly.
But as for doing the transfer from the command line, the only way I see that happening is if you are calling a program that already knows what to do with the data.

Obviously, I have no knowledge of the circumstances that sent you down the sql commands route.
But for inserting new data, you have mentioned using a csv file, and that would seem to me to a far more feasible approach.  You can set up an 'import csv' process in Access and that can be called via a pre-defined macro from a command line which calls the Access executable.

However,there is no obvious alternative to your 1M updates.  I have never even contemplated setting up a process that involved reading a million sql commands from an external file and executing them one at a time but I am pretty sure that it would take many hours to complete, even assuming there were no resource problems along the way.

The volume of inserts you mention is not large so I can't really imagine why there are so many update commands.  Are you only updating one field in one record in each statement?

I suppose I also need to ask how you are creating the sql commands - how are you sure they will run in Access- have you tested any single sql statement within the Access database?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37781648
...and needs to be run *every month*?!

So I to0 am curious about the need for such a massive process....
0
 

Accepted Solution

by:
sath350163 earned 0 total points
ID: 37862465
I found a way to accomplish this using flat file approach and loading the data using VB Script into MS Access database.
0
 

Author Closing Comment

by:sath350163
ID: 37879998
Solution
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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