BA 371 VB.Net Assignment 3: SQL Interpreter for Access

Although MS Access offers a small text-based interface for running SQL commands, it lacks an SQL script interpreter; i.e., a facility that can read an SQL script and execute its statements. In this assignment you'll write the VB.Net code for such an interpreter. Later in the course you will use this interpreter to run the case study's SQL database schema creation scripts against a (blank) Access database.

Click here to download a demo version of the program that runs against an Access 2003 database.
Click here to download a demo version of the program that runs against an Access 2007 database.
Click here to download a demo SQL script file.

To run the program:

1. Create a blank database in Access, save it and terminate your Access session.
2. Start the downloaded program. It will prompt you for both the SQL script and the database file  (*.mdb in Access 203; *.accdb in Access 2007).
3. If the program reports that it cannot open either the script file or the database, make sure you are providing it the proper paths to the files.
4. After successfully running the program, open the database in Access and check the 'relationships' view to see what the program just created.

Requirements:

• Program the interpreter as a command-line (console!!) application.
• The interpreter should prompt for both the file containing the SQL script and the file containing the Access database.
  
• The interpreter should echo (print to the screen) each SQL statement before running it against the database.
• The interpreter should catch any errors thrown by the database engine and print them to the screen, immediately following the statement causing the error.
• The interpreter does not have to handle SQL comments.
• The interpreter does not have to 'undo' any previously executed statements when it runs into an error condition.
• The interpreter should only open and close a connection with your database once (open at the start of the program and close when finished executing SQL statements).
• The interpreter should never terminate without first closing a previously opened database.
• Code the behavior of the program such that the command window in which the interpreter runs stays on the screen even when triggered from Windows Explorer (note: this does not have to be slick! Just include something so that it takes an action from the user to make the window disappear).