I know this can be done, but I forgot how.

I have Access 2007. I'd like to enter the SQL statements to define a
table, rather than use the wizard or design view.

Once I have the table designed, I'd also be able to again see the
definition of a table (or perhaps another table) and copy that SQL code.

How do I do this?

Thanks.

MikeB wrote:

I don't know if this is of any help.
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Salad wrote:
I don't think so, that says "The Microsoft Access database engine does
not support the use of CREATE TABLE, or any of the DDL statements, with
non-Microsoft Access database engine databases. Use the DAO Create
methods instead."

Since I want to create the tables with the MS Access engine (is it still
Jet?), that does not apply.

Salad wrote:

SOrry, I think I misread your reference. It is indeed the SQL, but what
I can't figure out is where to get to enter it in Access.

I guess I am a little puzzled about your question. There are two simple ways
to execute SQL in Access (1) open the New Query window in the Query Builder
and change to SQL View, enter or copy in the SQL (DDL), and click the
exclamation point to run it, or (2) use either the RunQuery or DB.Execute
statements in VBA code.

Am I missing something?

To be sure, most experienced Access hands do use the visual interface to
create tables and queries, or the features of Data Access Objects in Visual
Basic for Applications code. Most of those who come from other backgrounds
and wish to use the approaches they learned there eventually decide that, if
they are going to use Access, the "Access way" is worth a try. I don't have
a feel for how many stick with the SQL DDL approach and how many move on to
the "Access way" exclusively.

Larry Linson
Microsoft Office Access MVP





"MikeB" <mpbrede (AT) gmail (DOT) com> wrote

People has given you instructions for using DDL to generate the table. I
wanted to address the second part of your request ("Once I have the table
designed, I'd also be able to again see the definition of a table (or
perhaps another table) and copy that SQL code.")

There's nothing built into Access that will provide this information for
you, but there are 3rd party add-ins you can get that will. Unfortunately, I
don't use them myself, so I can't recommend a specific one for you.


"MikeB" wrote in message news:4d57176e$1 (AT) news (DOT) x-privat.org...

I know this can be done, but I forgot how.

I have Access 2007. I'd like to enter the SQL statements to define a
table, rather than use the wizard or design view.

Once I have the table designed, I'd also be able to again see the
definition of a table (or perhaps another table) and copy that SQL code.

How do I do this?

Thanks.

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in
news:8roqdhFuodU1 (AT) mid (DOT) individual.net:

I think that anyone who tries to stick with DDL is going to be
frustrated, as DDL doesn't support all the features that DAO and the
interactive Access do. On the other hand, because of the "ADO Wars,"
DAO doesn't support all the features of Jet/ACE that are actually
controllable with DDL statements run via ADO (or in SQL 92 mode
within Access itself). The most significant of these is for
CONSTRAINTs, which can be used to do things that are not otherwise
doable with field- or table-level validation rules (such as checking
a group of records as part of the validation of a field).

This is all very unfortunate, but if MS wanted to fix it, they
could. Ideally, they'd make DAO support everything that DDL does and
they'd make DDL support everything that DAO does. Even if they did
only one half of that, it would be a lot better than the current
situation.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

MikeB <mpbrede (AT) gmail (DOT) com> wrote in
news:4d57176e$1 (AT) news (DOT) x-privat.org:

The only way I know to do that is to use the ADOX Catalog.

But as someone who is in his 15th year of professional Access
development, I've never found any need to do that -- I've only read
about it.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/