Access 2003 Macro - delete table only if exists

[swhitten]

I have an Access 2003 Macro that includes three delete table steps. I ran into an issue where the first table had already been deleted (user error), so when the user ran the macro, it failed because it could not complete the first delete table step.

Is there some way to get it to move on if it can't find the table to delete? The subsequent macro steps import new data into the tables, so it's fine for them to run if the table doesn't exist.

I looked at the OnError action, but that seems to be only Access 2007, not Access 2003? I don't see any way to put an 'if exists' qualifier on the delete table macro step.

Other ideas? Thanks!

 

[MajP]

Public Function tableExists(tblName As String) As Boolean
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Name = tblName Then
tableExists = True
End If
Next tdf
End Function

 

[swhitten]

MajP,
Thank you for the bit of code. I am a very novice Access user, so this is probably a stupid simple question - where do I put that code? I don't see a way on my macro step to add it. Thanks in advance.

 

[swhitten]

I think I'm making progress.

In VB, I created a Module named TE with the following code in it:

Public Function tableExists(tblName As String) As Boolean

Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Name = tblName Then tableExists = True
End If
Next tdf

End Function


Then in my macro, I removed my DeleteObject line and replaced with a RunCode Action. Down at the bottom of the macro steps screen, I entered the following as Function Name:

tableExists (glimport)

GLImport is the name of my table that I want it to look for and delete it if it exists. If it doesn't exist, I want it to keep rolling through the macro with the other steps.

so in my novice VB/Access brain, what I THINK this should do is feed the name GLImport into the code in the function, then the function should (using the name it was given), delete if exists.

BUT when I try to step through the macro, first I get Error Number 0. When I click Step to move on, it says "Microsoft Office Access can't find the name "glimport" you entered in the exprssion. You may have specified a control that wasn't on the current object..."

The table GLimport does not exist, so it can't find it.

I know I must be missing something. Please help. thanks.

 

[MajP]

I have never wrote a macro so someone else will have to help you out. But in vba to use the function from a subroutine you would do something like

public sub DeleteTables()
if tableExists("glImport") then
do code to delete tables
end if
end sub

 

[swhitten]

I got this working, using your suggestions plus some others. Thanks again!