Excuting sql sripts on a Access DB

Question

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 ?

Answer 1

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.

Answer 2

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.

Answer 3

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.