Access: How to make IF THEN stment to del table if table exists?

G

Guest

I'm a newbie to ACCESS. I'm using ACCESS 2002:

I have a one database in which I am trying to set up a push the button do
everything system. That didn't work because I have the import, export, and
check everything stages.

So I wrote code with 5 steps parts and it has 2 buttons.

Button 1:
Deletethetable[tablename]
ImportTable
OutputTable
Repeat steps 1-3 for all 21 counties.
Run Confrim Query to make sure tables came out right.

Button 2:
Delete ImportError files.

The import error deletion is the problem because it stops if there were no
import errors. So how do I say something like:

If the ImportError-table is there, then delete it?

I tried
If Object.Exists!Table!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists acTable "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists(EARNINGS_ImportErrors) = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

None of them worked.

Help!
 
G

Guest

Dim db As Database
Dim tbl As TableDef
Dim strObjectName as String
strObjectName = "EARNINGS_ImportErrors"
Set db = CurrentDb()
For Each tbl In db.TableDefs
If tbl.NAME = strObjectName Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
Set db = Nothing
Exit Sub
End If
Next tbl
Set db = Nothing


jmonty
 
C

chris.nebinger

I usually just include error handling and ignore any error:


On Error Resume Next
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
On Error Goto 0
'OR
On Error Goto LineName



You could also:

If Dlookup("[Name]","Msysobjects","[name] = 'Earnings_ImportErrors'")
<> null then
'Delete it
End If

Chris Nebinger
 
G

Guest

In Sharon's original form (She is the Jedi and I am the Padawan) she had the
very command you mention. The reason why I don't just do that is because I
want to see why certain errors are occuring before we delete them, and then
just wipe the whole group.

[email protected] said:
I usually just include error handling and ignore any error:


On Error Resume Next
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
On Error Goto 0
'OR
On Error Goto LineName



You could also:

If Dlookup("[Name]","Msysobjects","[name] = 'Earnings_ImportErrors'")
<> null then
'Delete it
End If

Chris Nebinger

I'm a newbie to ACCESS. I'm using ACCESS 2002:

I have a one database in which I am trying to set up a push the button do
everything system. That didn't work because I have the import, export, and
check everything stages.

So I wrote code with 5 steps parts and it has 2 buttons.

Button 1:
Deletethetable[tablename]
ImportTable
OutputTable
Repeat steps 1-3 for all 21 counties.
Run Confrim Query to make sure tables came out right.

Button 2:
Delete ImportError files.

The import error deletion is the problem because it stops if there were no
import errors. So how do I say something like:

If the ImportError-table is there, then delete it?

I tried
If Object.Exists!Table!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists acTable "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists(EARNINGS_ImportErrors) = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

None of them worked.

Help!
Click to expand...
Click to expand...
 
G

Guest

And THANKS!!!!! :)

[email protected] said:
I usually just include error handling and ignore any error:


On Error Resume Next
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
On Error Goto 0
'OR
On Error Goto LineName



You could also:

If Dlookup("[Name]","Msysobjects","[name] = 'Earnings_ImportErrors'")
<> null then
'Delete it
End If

Chris Nebinger

I'm a newbie to ACCESS. I'm using ACCESS 2002:

I have a one database in which I am trying to set up a push the button do
everything system. That didn't work because I have the import, export, and
check everything stages.

So I wrote code with 5 steps parts and it has 2 buttons.

Button 1:
Deletethetable[tablename]
ImportTable
OutputTable
Repeat steps 1-3 for all 21 counties.
Run Confrim Query to make sure tables came out right.

Button 2:
Delete ImportError files.

The import error deletion is the problem because it stops if there were no
import errors. So how do I say something like:

If the ImportError-table is there, then delete it?

I tried
If Object.Exists!Table!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists acTable "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End If

If Object.Exists "EARNINGS_ImportErrors" = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists(EARNINGS_ImportErrors) = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

If Object.Exists!EARNINGS_ImportErrors = True Then
DoCmd.DeleteObject acTable, "EARNINGS_ImportErrors"
End Sub

None of them worked.

Help!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stopping a code to delete a table 2
Table Link from SQL 13
Import Excel To Access 3
Better Way to Delete Import Error Tables? 2
MouseUP is firing when Form is Loaded 3
Test if DB or table exist 7
Run-time error 3061 excel to access 1
Error Handling Problem 1

Top