Deleting tables

[Wavequation]

I have some code which needs to check for the existence of, then delete a
table, if found. What is the best way to test if a table exists?

 

[Dirk Goldgar]

I have some code which needs to check for the existence of, then delete a
table, if found. What is the best way to test if a table exists?

You might more simply just delete the table and trap the error that will be
raised if it doesn't exist/

 

[Daniel Pineault]

Here is some code to get you off the ground. You can adapt it as Dirk said
to trap the error if the table does not exist.

'---------------------------------------------------------------------------------------
' Procedure : DelTbl
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Delete the specified table
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTable Name of the table to be deleted
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-May-24 Initial Releas
'---------------------------------------------------------------------------------------
Function DelTbl(strTable As String) As Boolean
On Error GoTo Error_Handler

DoCmd.SetWarnings False 'Disable prompts to confirm deletion
DoCmd.DeleteObject acTable, strTable
DoCmd.SetWarnings True 'Reenable prompts

If Err.Number = 0 Then
DelTbl = True
Exit Function
End If

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: DelTbl" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
DelTbl = False
Exit Function
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Jeff Boyce]

You've described "how" you are trying to do something (i.e., "code ... [to]
delete a table"), but not "what".

Do you really need to delete the table definition and contents, or only the
contents, preserving the structure?

Regards

Jeff Boyce
Microsoft Access MVP

 

[Stuart McCall]

I have some code which needs to check for the existence of, then delete a
table, if found. What is the best way to test if a table exists?

Paste this into a standalone module (not class module) :

Public Function IsTable(TableName As String) As Boolean
Dim tdf As DAO.TableDef

For Each tdf In DBEngine(0)(0).TableDefs
If tdf.Name = TableName Then
IsTable = True
Exit For
End If
Next
End Function

To try it out:

Debug.Print IsTable("MyTableName")

To perform the delete:

If IsTable("MyTableName") Then
CurrentDb.TableDefs.Delete "MyTableName"
End If

 

[GB]

Wondering, though I haven't done my homework, would TableDefs return a query
name as well? I.e., what if the name fed to the function is a query instead
of a table, would it still act appropriately and say that the name was not a
table assuming no table has the same name as a query?

 

[Stuart McCall]

Wondering, though I haven't done my homework, would TableDefs return a
query
name as well? I.e., what if the name fed to the function is a query
instead
of a table, would it still act appropriately and say that the name was not
a
table assuming no table has the same name as a query?

<snip>

No, TableDefs can never contain a Query name. In answer to your question,
yes it will 'act appropriately' and return False for a query name (if as you
say no table name matches a query name).

If ever you need to determine whether a Query exists, use this:

Public Function IsQuery(QueryName As String) As Boolean
Dim qdf As DAO.QueryDef

For Each qdf In DBEngine(0)(0).QueryDefs
If qdf.Name = QueryName Then
IsQuery = True
Exit For
End If
Next
End Function

 

[John W. Vinson]

Wondering, though I haven't done my homework, would TableDefs return a query
name as well? I.e., what if the name fed to the function is a query instead
of a table, would it still act appropriately and say that the name was not a
table assuming no table has the same name as a query?

No, the TableDefs collection consists only of (local or linked) Tables;
Queries are not part of that collection. So unless you passed the function the
name of a table (even if that matched the name of a query!) it would return
False.

 

[Stuart McCall]

Hi John

Seems I'm getting a little rusty. Thanks for helping out.

 

[GB]

Thanks both, that was going to be my next question, how to determine if a
query exists, i.e. if there was a similar querydefs "object". Thanks again.