delete a table with VBA

[Guest]

How do I write a statement with VBA to do the following: If table A exists then delete Else do nothing
I currently using a macro to delete the table, but it the table does not exist I get an error

Thanks in advance

 

[CSmith]

Hi,

Here's an example that loops through all tables:

Dim ThisDB As Database
Dim Conta As Container
Dim iCounter As Integer

Set ThisDB = CurrentDb
Set Conta = ThisDB.Containers("Tables")

If Conta.Documents.Count <> 0 Then
For iCounter = 0 To Conta.Documents.Count - 1
If Conta.Documents(iCounter).Name = "tblYourTableName" Then
ThisDB.Execute "Drop Table tblYourTableName"
Exit For
End If
Next
End If

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, Von Dutch, etc - 60% off

How do I write a statement with VBA to do the following: If table A

exists then delete Else do nothing?

 

[Naresh Nichani MVP]

Hi:

Here is one more way

Dim db as DAO.Database
Dim tdf as DAO.TableDef
Dim strTableToDelete as string

strTableToDelete = "ThisTable" 'replace with table to delete
Set db = CurrentDB
On Error Resume Next
Set tdf = db.TableDefs(strTableToDelete)
On Error Go To 0
if not tdf is nothing then
db.TableDefs(strTabletoDelete).Delete
End if
Set db = Nothing

Regards,

Naresh Nichani
Microsoft Access MVP

How do I write a statement with VBA to do the following: If table A

exists then delete Else do nothing?

 

[Tim Ferguson]

If table A exists then delete Else do nothing?

strSQL = "DROP TABLE MyTable"
db.Execute strSQL, dbFailOnError

AFAIK, there is no error if MyTable does not exist; only if there is a
syntax in the code (and with only two words it's not hard to get right).

HTH


Tim F

 

[Dirk Goldgar]

strSQL = "DROP TABLE MyTable"
db.Execute strSQL, dbFailOnError

 

[Marshall Barton]

strSQL = "DROP TABLE MyTable"
db.Execute strSQL, dbFailOnError

AFAIK, there is no error if MyTable does not exist; only if there is a
syntax in the code (and with only two words it's not hard to get right).

Right Tim! But, if you don't want to know about an error,
you can't use dbFailOnError ;-)

 

[Tim Ferguson]

Right Tim! But, if you don't want to know about an error,
you can't use dbFailOnError ;-)

Actually, I was wrong and Dirk is right. There is an error <3376, Table
'nosuchtable' does not exist> with and without dbFailOnError, which can be
trapped with On Error. The full syntax looks like it has to be this:

On Error Resume Next
CurrentDb().Execute "drop table nosuchtable"

If Err.Number <> 0 Then
Debug.Print Err.Number & ", " & Err.Description

Else
Debug.Print "OK"

End If


which offends my sense of neatness (!) if nothing else. Shame we don't have
a Tables built-in query that could be checked in pure sql:

SELECT COUNT(*) >0 AS ItsThere
FROM Tables WHERE TName = "NoSuchTable"

All the best


Tim F

 

[Dirk Goldgar]

Actually, I was wrong and Dirk is right. There is an error <3376,
Table 'nosuchtable' does not exist> with and without dbFailOnError,
which can be trapped with On Error. The full syntax looks like it has
to be this:

On Error Resume Next
CurrentDb().Execute "drop table nosuchtable"

If Err.Number <> 0 Then
Debug.Print Err.Number & ", " & Err.Description

Else
Debug.Print "OK"

End If


which offends my sense of neatness (!) if nothing else. Shame we
don't have a Tables built-in query that could be checked in pure sql:

SELECT COUNT(*) >0 AS ItsThere
FROM Tables WHERE TName = "NoSuchTable"

All the best


Tim F

Tim -

Such a query can be written, but it has to make use of the officially
undocumented structure of the MSysObjects table:

SELECT Count(*) AS ItsThere
FROM MSysObjects
WHERE ((MSysObjects.Name="NoSuchTable")
AND (MSysObjects.Type=1));

(That doesn't find linked tables, though; for that you have to add
Type=6.)

Even though I doubt that structure is likely to change in any way that
would invalidate such a query, I don't think it's worthwhile to use this
instead of DAO or ADOX methods.

 

[Marshall Barton]

Actually, I was wrong and Dirk is right. There is an error <3376, Table
'nosuchtable' does not exist> with and without dbFailOnError, which can be
trapped with On Error.

Interesting!

I was under the mistaken impression that Execute never
generated an error without dbFailOnError. But, now that I
try it, I get an error if the query can not even start
executing such as an invalid SQL statement, nonexistent
table, etc. Makes sense now and I'm glad Dirk has us
straightened out now ;-)

 

[TC]

(snip)

I was under the mistaken impression that Execute never
generated an error without dbFailOnError. But, now that I
try it, I get an error if the query can not even start
executing such as an invalid SQL statement, nonexistent
table, etc. Makes sense now and I'm glad Dirk has us
straightened out now ;-)

I believe the full affects of dbfailonerror, are not well understood.

Say you execute an action query that tries to update several records. Say
that some of those records can be updated, but some of them can not (eg. due
to duplicate keys).

o WITH dbfailonerror, the action query terminates as soon as any row can
not be updated.

o WITHOUT dbfailonerror, the action query continues regardless, updating
those records that it can update, & silently ignoring the ones that it
can't.

In A97 & later, the action query IS NOT wrapped in an implicit transaction.
So, if any records WERE updated, those records will REMAIN updated,
regardless of whether dbfailonerror was used, or not.

Not trying to SHOUT - just a few too many emphases to do >like this<.

Apologies if this is all old news to everyone

TC

 

[Tim Ferguson]

Such a query can be written, but it has to make use of the officially
undocumented structure of the MSysObjects table:

That's why I wished for a _built-in_ query!

Even though I doubt that structure is likely to change in any way that
would invalidate such a query, I don't think it's worthwhile to use this
instead of DAO or ADOX methods.

I did see a www page[1] where MS have promised not to change Jet in such a
way as to break old code, it was taken to include functionality like this.
Still, I agree with you that it's not advisable to rely on it.


[1] Sorry, long time ago and I didn't keep the reference


All the best


Tim F