Access 2003 SP2 make table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
If you are calling the query programmatically, then wrap it like this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryName"
DoCmd.SetWarnings True
 
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.
 
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
 
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
 
Thank you. I am still working to resolve my problem, and you have certainly
suggested helpful ideas.

Oli
03.27.06 09.11 hst
 
Back
Top