DDL script in Access 1

[klm2klm2]

I'm accustomed to large RDBMSs where you can write a script with many DDL commands (create table, drop index, etc). MS Access seems to only allow one DDL statement to be executed at a time, and it has to be submitted interactively. Please tell me how to feed a DDL script with many DDL statements into Access for unattended processing without having to learn VBA and write a program.

Thanks,
Kim

 

[jfischer]

The Access database engines do not support SQL scripting, i.e., a stored procedure. You can get the same effect of scripting by coding each DDL statement as a separate Query. Then, use a series of "OpenQuery" statements in a Macro to execute them in the order you want.

 

[chrissie1]

if you use sql-server with access projects you can use stored procedures and in-lin function like in sql-server.

but you need MSDE or sql-server installed somewhere Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[klm2klm2]

Dear jfischer,

Thanks for the suggestion. Do you know of the OpenQuery statement can be executed from a VB program (not VBA)?

Thanks,
Kim

 

[jfischer]

You can use Automation to open an instance of Access from VB, and then invoke the OpenQuery statements through Access. For example:

Dim acc As Access.Application

Set acc = New Access.Application
acc.OpenCurrentDatabase myAccessDbPathAndFilename
'Delete the existing table first, if needed
'acc.DoCmd.DeleteObject acTable, "tblMyTable"
acc.DoCmd.OpenQuery "qryMyTable_DDL", acViewNormal
acc.DoCmd.OpenQuery "qryMyTable_Append1"
acc.DoCmd.OpenQuery "qryMyTable_Update2"
acc.CloseCurrentDatabase
On Error Resume Next
Set acc = Nothing