"table already exists"

[davethewelder]

I am running a query in a macro in which the first two queries create two
tables and the third query compares two table on an outer join and makes a
resultant dataset in a table. I have SetWarnings = No.
When I run the query I now get a message ""table already exists" and it
halts the macro. I tried clearing the table with a delete qry and changing
the create table to a append qry but I still get the error message. I am
using Access 97 for this.

Has anyone experienced this before?

Any help would be greatly appreciated.

Davie

 

[Wayne-I-M]

hI

Use something like this - change the text in CAPITALS


Private Sub BUTTONNAME_Click()
Dim davestable As TableDef
For Each davestable In CurrentDb.TableDefs
If davestable.Name = "TABLENAME" Then
CurrentDb.TableDefs.Delete davestable.Name
End If
Next
DoCmd.SetWarnings False
DoCmd.OpenQuery "QUERYNAME"
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "TABLENAME"
End Sub

 

[aida mowers]

I am running a query in a macro in which the first two queries create two
tables and the third query compares two table on an outer join and makes a
resultant dataset in a table. I have SetWarnings = No.
When I run the query I now get a message ""table already exists" and it
halts the macro. I tried clearing the table with a delete qry and
changing
the create table to a append qry but I still get the error message. I am
using Access 97 for this.

Has anyone experienced this before?

Any help would be greatly appreciated.

Davie

 

[davethewelder]

Wayne, it worked a treat. Thanks for your help.

Davie

 

[John W. Vinson]

I am running a query in a macro in which the first two queries create two
tables and the third query compares two table on an outer join and makes a
resultant dataset in a table. I have SetWarnings = No.
When I run the query I now get a message ""table already exists" and it
halts the macro. I tried clearing the table with a delete qry and changing
the create table to a append qry but I still get the error message. I am
using Access 97 for this.

Has anyone experienced this before?

Any help would be greatly appreciated.

Davie

A couple of things here. A Delete query deletes records out of a table (all
the records if it has no criteria) but it leaves the table intact.

More importantly though - in a well designed app MakeTable queries will be
very rare. Why do you feel that you need to create THREE new tables??? What
can you do with these tables which cannot be done using a select query on the
original source of your data?

 

[davethewelder]

John, having thought about what you said regarding exporting from the select
query and I looked at the "Output To" function which worked perfectly. This
will save me making tables to export.

The original fault I now think is a local problem as I sometimes also get
strange errors using VBA but all disapear when I reboot.

Thanks again for your help.

Davie