Table already exists, and maketable query will not overwrite

[Big Pat]

Hi,

I have (1) a crosstab query which takes a parameter from a form dropdown, (2) a make-table query based on query 1, which puts the results into a simple table and (3) a report based on the table

When I change the selection in the dropdown and run the make-table query, I get the standard warning that says "You are about to run a make-table query that will modify data in your table". But when I click Yes, it then tells me "Table 'MyTable' already exists.

Shouldn't a make-table query warn me that my table is about to be deleted? Help tells me this:

Table <name> already exists. (Error 3010)
You tried to create or rename a table with a name that already exists in this database. Choose another name, and then try the operation again.

In a multiuser database, this error can also occur if you delete a table, another user creates a table with the same name, and then you try to roll back the deletion of your table. To restore your table, the other user must first delete or rename the new table before you try the rollback operation again.

But this isn't a multiuser database. Although this database does have some back-end tables in common with other databases, this isn't one of them and I'm the only person using this particular front-end.

It's not a big deal as I can manually delete the table and then everything works fine. I just wonder why it behaves like this.

Thanks


Pat

 

[vbaInet]

Are you saying that there's no table with that name in your database already?
Why the make table query anyway?

 

[Ari]

Hi

Use the command to delete the table before running the query.

On Error Resume Next
'Delete table
DoCmd.DeleteObject acTable, nameTable
If Err Then Err.Clear: On Error GoTo if_erro
'Shoot the action query to create the table
Application.CurrentDb.Execute strSQL

sair:
    Exit Function
if_erro:
    MsgBox Err.Description & " / " & Err.Number
    Resume sair

 

[Big Pat]

vbaInet: Yes the table exists. But in my other dbs when I run a maketable query it warns me that the existing table is about to be deleted. In this database it does not give that warning, but tells me that my table already exists. I *know* it does, so I want to delete it and remake it (based on new parameters) so I can run a new report. But it doesn't give me that option.

As to why the maketable in the first place, I was unable to successfully create a report based on the crosstab query and I used someone else's suggestion to make a table from the results and then base the report on that. Maybe not as efficient as an *ideal* method but it's one I could follow IT/software is a side-issue to my main job.

Ari, Thanks for that suggestion. I'm not going to be back in the office for almost a week now, but I'll try that then. I still don't understand why a maketable query would behave differently in this database to any others.

 

[vbaInet]

You have probably turned off the Warnings and forgot to turn it back on in one of your error handlers. Do you have DoCmd.SetWarnings code in your db? Ari's code should get you going.

 

[AConrad00]

I would recommend that instead of a make-table query, you switch to an append-query

first create a delete-query to clear all data from your cross-tab table and then run the append query to add the current data to that table. It will make it so that you do not need to get that "delete table" message and runs faster.

 

[Big Pat]

Thanks for that. The delete-then-append works fine, I assume because I'm deleting records and not the actual table. Still kinda puzzled why my way didn't work, but not gonna stress about it