Have Geek Brain

Excluding sql scripts in Access DB

I have a script with several hundred sql queries that I need to execute on an access database. Since executing sql queries in Access is a bug IMO, you can only execute one at a time and not recognize comment lines starting with "-", I would like to know if there is an easier way to do this. Is there a good alternative to MS Access for managing DB access?

+1


a source to share


3 answers


Two decent options:

Option 1: Write a C # program that uses the DAO libraries to automate access and execution of programs 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 using the method CurrentProject.Connection.Execute()

.

In any case, you can put this code inside a loop that reads your statements and executes them one at a time.

+1


a source


I would suggest writing an app or PowerShell script to read queries from a file and execute on an Access database. Read line by line, ignoring lines starting with your comment separator.



+2


a source


How to set up links from MS-SQL database to access database and run scripts through MS-SQL? Assuming you are not changing the table structures, you should be fine.

0


a source






More articles:


All Articles