How do I import an .sql file into Access 2007?

Question

How do I import an .sql file into Access 2007?

Answer 1

You don't really.  You can use Access as a front end for SQL Server, but it won't run SQL commands (your .sql file) directly itself.  Your options are to translate what is in the .sql to Access or create an ODBC connection from Access to a SQL engine.  The SQL engine can be a server running something like SQL Server 2005 or you can run SQL Express 2005 on the same machine.

The best way really depends on what is in the .sql file.  If is a simple query, you will be better off just doing the translation to an Access query.  However, the .sql file could be a complex as creating a entire database including triggers, constraints, data validation, stored procs, indexes, etc-  in which case you might have to run it on a SQL engine or give up some functionality in the code.

FYI, SQL Express 2k5 is free and you can download it from MS.  You can link it to Access following this:

http://www.aspfree.com/c/a/Microsoft-Acc...

Answer 2

If you want the result generated from a sql statement into your access database, you could try Data Transfer Service (DTS).  You can learn how to do this at:

http://articles.techrepublic.com.com/510...

You can DTS from SQL to Access without any problems.  

I have SQL Express with the Tool kit installed and was able to follow this instruction on how to create a dts session with SQL Express.

http://mobiledeveloper.wordpress.com/200...

Answer 3

I take it that ".sql" file is a query file saved from the SQL Query Editor rather than an SQL Datafile?

These can be opened in notepad. Copy all the content into you clipboard.

In access, click on "Create">"Query Design"

In the Query Design window, click on "Close" to close the show table pop up. Click on the big "SQL" in the top left hand window to change the view to SQL View.. Delete any content and paste the SQL that you copied earlier..

T-SQL is not exactly the same as Access SQL.. You may need to alter your code a little, if you click on "!Run" if it can't run the query, it'll tell ou what the errors are.