CODEWITHSUNDEEP
databasems-access
Mez
Mez

Reputation: 2857

Excuting sql sripts on a Access DB

I have a script with a few hundreds of sql queries that I need to execute on an Access DB. Since executing sql queries in Access is IMO quit awkward, you can only execute one at a time and does not recognize comment-lines starting with '--', I would like to know if there exist out there an easier way to do this. Is there a good alternative to MS Access to manage an Access DB ?

Upvotes: 1

Views: 618

Answers (3)

John M Gant
John M Gant

Reputation: 19308

Two decent options:

Option 1: Write a C# program that will use the DAO libraries to automate Access and execute the statements programmatically.

dao.DBEngineClass dbengine = new dao.DBEngineClass();
dbengine.OpenDatabase(path, null, null, null);
dao.Database database = dbengine.Workspaces[0].Databases[0];
database.Execute(sql, null);

Option 2: Write a VBA module inside the database that will do the same thing using the CurrentProject.Connection.Execute() method.

In either case you could put this code inside a loop that reads your statements and executes them one by one.

Upvotes: 1

BIBD
BIBD

Reputation: 15384

What about setting up links from an MS-SQL database to the access database and running the scripts through MS-SQL? Assuming you aren't doing altering table structures, you should be OK.

Upvotes: 0

Daniel P. Bullington
Daniel P. Bullington

Reputation: 643

I would suggest writing a application or PowerShell script to read queries from a file and execute against the Access database. Read line by line ignoring lines beginning with your comment delimiter.

Upvotes: 2

Related Questions