How to execute or run SQL scripts in Access
hello friends,
I am aware that it is possible to view SQL versions of queries, but I want to know whether there is a way to run SQL scripts in Access ? If possible, where can I find the syntax to execute or run SQL scripts in Access ?
Any ideas....
Re: How to execute or run SQL scripts in Access
I don't know to execute SQL scripts in Access. But however you can try these four workarounds :
- Export data to an SQL-based RDBMS > run the script > import back to Access.
- Setup Access with ODBC and use an external ODBC-enabled SQL client to update the access date.
- Use a text editor to batch and find/replace the script and put it in VBA format and then use that code in a Macro or Form action
Code:
statement = <line from text file>
DoCmd.RunSQL(statement)
- Write a VBA script that opens a file and executes each line of SQL.
Re: How to execute or run SQL scripts in Access
In the database,
- Create a query in Design View and close the Show Table window.
- From the menu bar, click View >> SQL View.
- You can type and paste your SQL statements into this window.
To add dynamic SQL as a command button on a form, you will need to build a VBA procedure.
Re: How to execute or run SQL scripts in Access
Well, the Jet Engine Access supports some SQL functions, but I don't know which ones are supported and which ones are not.
Eventhough, Access supports a wide variety of database engines; you don't have to use the Jet engine if you need a more versatile back-end. You can connect to an Oracle database.