Drop a table before creation in SQL?

[John Favaro]

I've decided to try to learn how to create a database in Access 2003 in
SQL. So the first step is to create a few tables. I have an example for
creating a table, so that works fine.

Before creating the table, however, I want to "delete the table if it
already exists." The usual strategy, so you can just run the file to
recreate the database after modifying things.

I think you're supposed to use the DROP statement, and I've seen it used
like

DROP MyTable IF EXISTS

in SQL.

But Access doesn't like my DROP statement no matter how I vary it. Keeps
talking about syntax errors in the drop statement. I can't find whatever
error it is worried about.

Sorry to bother with such a basic question, but ... where can I get the
straight info on this?

Thanks,

John

 

[KC-Mass]

look here
http://support.microsoft.com/kb/180841

 

[John Favaro]

Thanks - I think I see where my misunderstanding came from:

I tried to combine two SQL statements in one data definition query, like
this:

DROP TABLE MyTable
CREATE TABLE MyTable( ...

I discovered that it lets me have either one of those SQL commands in
the query, but not both. The "syntax error" I was continually
encountering was simply Access bumping into the second SQL statement
after the first one.

Doesn't Access allow you to have a file of SQL commands that you feed
into it? Surely that must be possible - I must not be looking in the
right place, or misunderstanding how to combine multiple commands.
Thanks for any insights.

 

[John W. Vinson]

Doesn't Access allow you to have a file of SQL commands that you feed
into it?

No.

Access is NOT SQL/Server, and the Access dialect of SQL is not the same as
T/SQL.

As a rule, Access developers use the user interface or VBA code (the
CreateTable method frex) instead of DDL queries. DDL queries are now allowed
(they didn't used to work at all!) but they're far from being routinely used.

One thing I've done is to store SQL scripts (single queries only) as Memo
fields in a table, and use VBA code and the CreateQuerydef() method to pull
out the SQL and execute it, but that's very different from the way you're used
to in SQL.

 

[Peter Hibbs]

Perhaps this site will help :-
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

Peter Hibbs.

 

[David W. Fenton]

DDL queries are now allowed
(they didn't used to work at all!)

Eh? The 2nd Edition of The Jet Database Engine Programmer's Guide,
which was for Jet 3.5, gives examples DDL alongside DAO, though DAO
is preferred, as DAO supported all table features while DDL did not.

What works in DDL with Jet depends on what interface you use. In the
Access UI using standard ANSI 89 SQL mode, lots of standard DDL will
not work, whereas in ANSI 92 SQL mode, more of the commands work. In
code, DAO works in only SQL 89, while ADO gets you SQL 92.

 

[David W. Fenton]

Doesn't Access allow you to have a file of SQL commands that you
feed into it?

Jet does not allow multiple SQL statements in a single saved
QueryDef and you cannot execute multiple SQL statements in a single
DAO .Execute command.

The reason for this is because Jet is not a server database so
there's no server process to serialize the commands. While this
would not be an issue with a single-user application, it would be
very problematic for multi-user apps, where sending multiple
statements could lead to all sorts of deadlock issues.

 

[John Favaro]

Peter,

Thanks - that looks extremely useful, much appreciated.

John

 

[John Favaro]

DavidF, and also JohnV,

Thanks VERY much for straightening this out for me. I now get the
difference between Access/Jet and a "classic SQL server" environment.

In this case, I think it's best to play into Access's strengths and use
the UI facilities it offers for building and managing my databases, and
worry about playing with SQL in different DB contexts.

Thanks again to both of you,

John