Solved

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

Posted on 2012-03-28
6
383 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 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

11 Experts available now in Live!

Get 1:1 Help Now