delete table via VB

[GirlBlonde]

i want to delete a errors table that i get upon importation of data. this will be a regular occurance so i want to delete it each month after the user has imported their table otherwise they will just build up of tables. i have the below code but does not seem to be working. any ideas would be helpful.

many thanks

CurrentDb.Execute "DROP Sheet1$_ImportErrors MyTable"


Dim mySQL As String
mySQL = "DELETE * FROM Sheet1$_ImportErrors"
DoCmd.RunSQL mySQL


Energy & persistance conquer all things!

 

[ck1999]

Try this

Sub test()
Dim dbs As Database
'Return reference to current database.
Set dbs = CurrentDb

DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
End Sub

You code just deletes all the records


ck1999

 

[JerryKlmns]

How about this?

CurrentDb.Execute "DROP TABLE [Sheet1$_ImportErrors]

 

[GirlBlonde]

hi thanks very much for that worked great!

Energy & persistance conquer all things!

 

[GirlBlonde]

hi

sorry to bother again but what if i have the occasion when there is no table to delete. ie no import errors? can i write into the code an if statement so that if it exists then delete it if not then dont worry about it?

i am not that great on Vb only know basic stuff. i used the code
CurrentDb.Execute "DROP TABLE [Sheet1$_ImportErrors]"

many many thanks

Energy & persistance conquer all things!

 

[genomon]

Well, there is the "Is Nothing" construct you can use in conjunction with the "IF...THEN"....

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[JerryKlmns]

You could ignore the error if it happens

Sub DeleteImportErrors()

On Error Resume Next
CurrentDb.Execute "DROP TABLE [Sheet1$_ImportErrors]"
Err.Clear
End Sub

 

[ZmrAbdulla]

This too will help

Public Function DelTable(tblName As String)
Dim tbd As TableDef
	For Each tbd In CurrentDb.TableDefs
		If tbd.Name = tblName Then
		CurrentDb.TableDefs.DELETE tblName
	End If
Next
End Function

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[JerryKlmns]

A non-error messing approach

Sub DeleteImportErrors2()

If DLookup("[Name]", "MSysObjects", "[Name]='Sheet1$_ImportErrors'") & vbNullString <> vbNullString Then
    CurrentDb.Execute "DROP TABLE [Sheet1$_ImportErrors]"
End If
End Sub

 

[TheAceMan1]

. . . or:

[blue]   Dim Cri As String
   
   Cri = "[Name]='Sheet1$_ImportErrors'"
   
   If Not IsNull(DLookup("[Name]", "MSysObjects", Cri)) Then
       CurrentDb.Execute "DROP TABLE [Sheet1$_ImportErrors]"
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886