replace the same table name in access

[Jason]

Dear all,

I want to use macro to import the text data to access. But when I run the
macro, it added the new table in the access and didn't replace the same table
name data. How can I do to replace the same table name data ?

Thanks
Jason.

 

[Steve Schapel]

Jason,

Try using the DeleteObject action in your macro to remove the existing
table before doing your import.

 

[Jason]

Hi Steve,

I had already used DeleteObject and it worked. But when the access is closed
abnormally and the DeleteObject was run. Then I run the access again and
DeleteObject will cause error because the table was deleted.

Could I check the table is existed or not before issue DeleteObject?
What's command I need to use? (I don't want to use on error statement)

Thanks & Regards,
Jason

 

[Steve Schapel]

Jason,

No, as far as I know you can't do this with a macro. I think there are
2 choices. One is to use a VBA procedure instead, which would allow you
to check for the table's existence first. The other is to set up a
MakeTable Query to make a table of the same name, and use an OpenQuery
action in your macro, before the DeleteObject action, to run the
MakeTable. If the table doesn't exist, it will now. If it does exist,
the MakeTable will overwrite it. Either way, it doesn't matter, because
it's getting deleted anyway.

 

[Jason]

Steve,

Thank you very much for your help.
One more question, how to check the existence object using VBA?

Thanks,
Jason.

 

[Chris Reveille]

Here is something from Eric Butts

If you are intent on using a macro then you can use DLookUp
function for
your condition, example:

Condition

Action
----------------------

-------------------
DLookUp("[ID]","[the table name]")
CopyObject


If the table name "[the table name]" does not exist then it
will skip the action. But of course if the table does
exist and it has no data in the field "ID" it also will be
skipped. The preferred method is to use VBA
code:

Function test(tdfName As String) As Integer
On Error GoTo ErrorCheck
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(tdfName)
Set tdf = Nothing
Exit Function
ErrorCheck:
If Err.Number = 3265 Then
test = 1
Else
test = 0
End If
Set tdf = Nothing
End Function

And then call the function from your Macro.

Chris