I've got a script having a couple of 100s of sql queries that I have to execute with an Access DB. Since performing sql queries in Access is IMO quit awkward, you are able to only execute individually and doesn't recognize comment-lines beginning with '--', I must determine if there are available an simpler method of doing this. It is possible to good option to MS Use of manage an Access DB ?
I recommend writing a application or PowerShell script to see queries from the file and execute from the Access database. Read line by line disregarding lines starting with your comment delimiter.
Two decent options:
Option 1: Write a C# program which will make use of the DAO libraries to automate Access and execute the claims programmatically.
dao.DBEngineClass dbengine = new dao.DBEngineClass(); dbengine.OpenDatabase(path, null, null, null); dao.Database database = dbengine.Workspaces.Databases; database.Execute(sql, null);
Option 2: Write a VBA module within the database that is going to do exactly the same factor while using
Either in situation you can put this code in the loop that reads your claims and executes them 1 by 1.
How about establishing links from an MS-SQL database towards the access database and running the scripts through MS-SQL? Presuming you are not doing changing table structures, you ought to be OK.