Access DDL with DAO

[a a r o n . k e m p f]

Team;

Does Microsoft Access (JET) support ANSI standard DDL commands?

It just really honestly blows my mind..

Why don't you guys use them more? I Just love using DDL; maybe it's
because I can generate scripts with this quite easily in SQL
Management Studio.

I just feel like it's a whole lot easier to create 20 objects using
DDL than VB.
Do you guys disagree?

I just don't get it.

Is there any way to get the DDL for a query in Access MDB format?

I mean the literal 'create query' syntax I would assume

thanks

-Aaron

 

[Tom Wickerath]

Aaron,

Does Microsoft Access (JET) support ANSI standard DDL commands?

Yes. You did know that, didn't you? However, the Microsoft Jet database
engine does not support the use of CREATE PROCEDURE, or any of the DDL
statements, with non-Microsoft Jet database engine databases. It also does
not support scripting like SQL Server does, and one can only run a single
SQL-DDL statement at a time in SQL View of a new query, unlike SQL Server,
where you can string many DDL statements together in Query Analyzer. You can,
however, run many SQL-DDL statements, one after the other, in a module with
some really simple VBA code.

The Jet 4.0 SQL Reference that ships with Office 2003 Professional is on
your hard drive in the following location, if you installed MS Office in the
default directories:

"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"

It just really honestly blows my mind..

What blows your mind?

I just feel like it's a whole lot easier to create 20 objects using
DDL than VB.
Do you guys disagree?

No....I agree with you on this statement. However, as I said above, you can
run many DDL statements at once with some really simple VBA code
(Kindergarten Level programming required).

I just don't get it.

You don't get what? I don't think it should be too surprising that a product
targeted to the so-called Information Worker doesn't support scripting. Yes,
it would be nice if it was available.

Is there any way to get the DDL for a query in Access MDB format?

I mean the literal 'create query' syntax I would assume

Will you settle for CREATE PROCEDURE instead?
Syntax
CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

For more information about DDL queries, click Microsoft Access Help on the
Help menu, type "data-definition queries" in the Office Assistant or the
Answer Wizard, and then click Search to view the topic. Easier yet, just
double-click on your copy of JETSQL40.CHM.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________