Access 2003 SP2 make table

[UncleOli]

A make-table query is asking for confirmation before it deletes the current
table, even with "record changes," "document deletions," and "action queries"
cleared under Tools > Options.

I am running Access 2003 SP2 with the Post-SP2 hotfix (kb 902445) installed
for a previous problem. The operating system is Windows XP Professional SP2.
I did not test the query before I installed the Post-SP2 hotfix.

I haven't been able to find any other reference to this problem in the
Microsoft Knowledge Base.

Any help to solve this would be much appreciated.

Thank you,
Oli
03.26.06 09.50 hst

 

[Brian]

If you are calling the query programmatically, then wrap it like this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryName"
DoCmd.SetWarnings True

 

[Brian]

I just tested it, and it does the same thing to me, also in A2003 SP2. I
guess it could be considered a bug, although I suspect it is by design, since
this all begs the larger question of why one would want to use a make-table
query in a production environment. It would make sense only if the structure
(not the content) of the table is different each time it is created, or if it
is used only once.

I never run into the issue because the only time I use make-table queries is
during development. Once I have a table structure defined, I never again
create/delete the table, but instead use a delete query or DoCmd.RunSQL
statement (e.g. DoCmd.RunSQL "Delete * from Table1") to flush the contents
(not delete the table itself), and then use an append query to re-populate it
instead of a make-table query. In the production environment, I simply call
the delete query on the line before I call the append query. Then, when done
with the temporary date, call the delete query again - all on

DoCmd.OpenQuery "ClearTable"
DoCmd.OpenQuery "AppendRecords"
'DoStuff here
DoCmd.OpenQuery "ClearTable"

I've never considered that a workaround.

 

[UncleOli]

Actually, I usually use the same logic you do. In this case, however, the
table I'm using has an AutoNumber field that I need to reset to "1" after
each use, and the make-table query was the simplest way to do it.

Thanks again for the feedback,
Oli
03.26.06 16.18 hst

 

[Brian]

That's a bit of a pain, then. Programmatically, you could delete the records,
then do a compact/repair, which would reset the autonumber to 1.

If you're really ambitious, you can use the Seed property to reset the
AutoNumber back to 1 after the delete, but it involves a little VBA coding -
all probably more work than clicking the button one extra time to delete the
table. This link has the VBA procedure:

http://www.access-programmers.co.uk/forums/showthread.php?=&threadid=45181

 

[UncleOli]

Thank you. I am still working to resolve my problem, and you have certainly
suggested helpful ideas.

Oli
03.27.06 09.11 hst