Drop Table function 2

[DHSBW]

Hi:

The function below deletes a specific table with the user supplying the table name, and deletes it if it does. But the function returns an error "table not found" if the table name has a space in it. How can I get it to accept the space? Thanks for the help in advance.


Function DeleteTables(sTableName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb()
DoCmd.SetWarnings False
For Each tdf In dbs.TableDefs

If tdf.Name = sTableName Then
CurrentDb.Execute "DROP TABLE " & sTableName

 

[genomon]

Try this:

CurrentDb.Execute "DROP TABLE '" & sTableName & "'"

Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[DHSBW]

Thanks for the reply. Now I'm getting a "syntax error in drop table or drop index" message.

 

[genomon]

Oops. So much for my stroke of genius.
Pretty sure the syntax error is mine.
Sorry can't be of more help....


Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[Ed2020]

You need to enclose the table name in square brackets:

CurrentDb.Execute "DROP TABLE [" & sTableName & "]"

The looping through each tabledef is also pointless.

The following should work for you:

Public Sub DropTable(ByVal strTableName As String)
    CurrentDb.Execute "DROP TABLE [" & strTableName & "];", dbFailOnError
End Sub

Please do not feed the trolls.....

 

[jadams0173]

Another approach if you need to test if the table exists before dropping it.

Function DoesTableExist(TableName As String) As Boolean
Dim rs      As ADODB.Recordset

[blue]'uses the schema to see if the temptable currently exists.  Only looks for the specific
'table name.  Doesn't loop thru all tables.
'Values in the array are  Array(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
'Since we are looking for a TABLE NAME we populate the 3 constraint and leave the rest empty. 
'conn = ADODB connection [/blue]

Set rs = conn.OpenSchema(adSchemaTables, Array(Empty, Empty, TableName, Empty))
    DoesTableExist = Not (rs.BOF And rs.EOF)
    rs.Close
Set rs = Nothing

End Function

Then you could pass the function the table name and drop based on the value of DoesTableExists.

If DoesTableExist(YourTable) then
[blue]'Drop table[/blue]
end if

 

[genomon]

WOW!
jadams inadvertently solved a problem I never even posted.
Thanks, and a star!


Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[jadams0173]

Glad I could help. Thanks for the star.

 

[DHSBW]

Thanks Ed. Got it to work.