appending an Excel worksheet to an existing Access database

[Philmer]

What I would like to know is on excute of the below code how to delete table2 and remake a table to export excel data to?

Private Sub Command1_Click()
On Error GoTo errHandler
Dim mExcelFile As String
Dim mAccessFile As String
Dim mWorkSheet As String
Dim mTableName As String
Dim mDataBase As Database
mExcelFile = App.Path & "\Book1.xls"
mAccessFile = App.Path & "\Db2.mdb"
mWorkSheet = "Sheet1"
mTableName = "Table2"
' Below you may use "Excel 7.0" or 8.0 depending on your installable .
Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
" FROM [" & mWorkSheet & "$]"
MsgBox "Done. Use Access to view " & mTableName
Exit Sub
errHandler:
If Err.Number = 3010 Then
MsgBox mTableName & " already exist." & vbCrLf & _
"Delete " & mTableName & " first or use another table name."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub

 

[BrianB]

Re: appending an Excel worksheet to an existing Access datab

I use something like this that deletes all the records from the table but leaves the structure ready to add new records.

CurrentDb.Execute ("DELETE MyTableName.* FROM MyTableName;")

 

[Philmer]

Re: appending an Excel worksheet to an existing Access datab

BrianB,
I tried your statement


code:
--------------------------------------------------------------------------------

CurrentDb.Execute ("DELETE MyTableName.* FROM MyTableName;")

--------------------------------------------------------------------------------

changed to this

mDataBase.Execute ("DELETE mTableName.* FROM mDataBase;")
but does not work any ideas??

 

[Norie]

What is the actual name of the table?