Delete tables in another database via VBA

[Presto]

Access 2003. two databases: Update.mdb and Mydatabase.mdb

I use Update.mdb on the back end to import new data into tables. I then have
to delete 5 tables in the "front end" Mydatabase.mdb, then copy the new
tables into it. I know how to use the maketable queries to copy the new
tables into Mydatabase.mdb, but I'm not sure how to delete the tables of
another database first.

Using an example of table1 table2 table3 table4 table5 Can someone help me
with the VBA code to delete??

 

[Douglas J. Steele]

To delete tables from the front-end, you'd use

CurrentDb.TableDefs.Delete "table1"
CurrentDb.TableDefs.Delete "table2"

etc.

My question to you would be why do you have tables in the front-end?

 

[Presto]

Good evening Mr Steele,

I used the wrong word.. It's not set up as the typical frontend/backend.
sorry. I consider it my "front end" because this is the one the users see.
Origionally I had the tables linked and it works nicely like that, but if
the network is not available, the db doesn't work. I have to copy those
tables into the second db.

From the first database using vba, I need to delete the tables in the second
database then run all the maketable queries.

 

[Douglas J. Steele]

To delete tables in another database, you'd use

Dim dbOther As DAO.Database

Set dbOther = OpenDatabase("F:\Folder\File.mdb")
dbOther.TableDefs.Delete "table1"
dbOther.TableDefs.Delete "table2"
dbOther.Close
Set dbOther = Nothing

On the other hand, you could simply delete the data from the tables and
append the new data to them, as opposed to deleting the tables and creating
new tables.

 

[Presto]

Thank you again Doug.

That is exactly what I was looking for, however, I will try both suggestions
and see which one will best suit our needs. Thanks for the help with the
code.
I'll let you know how it goes.

Have a great weekend!!

Presto.

 

[Presto]

Will something like this work?

Dim dbOther As DAO.Database
Set dbOther = OpenDatabase("F:\Folder\File.mdb")
dbOther.DoCmd.SetWarnings False
dbOther.DoCmd.RunSQL "DELETE * FROM Table1"
dbOther.DoCmd.RunSQL "DELETE * FROM Table2"
dbOther.DoCmd.RunSQL "DELETE * FROM Table3"
dbOther.DoCmd.RunSQL "DELETE * FROM Table4"
dbOther.DoCmd.RunSQL "DELETE * FROM Table5"
dbOther.DoCmd.SetWarnings True

dbOther.Close
Set dbOther = Nothing

 

[Douglas J. Steele]

My preference is to use

CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table1", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table2", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table3", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table4", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table5", dbFailOnError

I prefer the Execute method for two reasons:

1) You don't get the annoying "You're about to delete n rows of data..."
prompts.
2) Because of the dbFailOnError parameter, a trappable error will be raised
if something goes wrong.

You could also use

Dim dbOther As DAO.Database

Set dbOther = OpenDatabase("F:\Folder\File.mdb")
dbOther.Execute "DELETE * FROM Table1", dbFailOnError
dbOther.Execute "DELETE * FROM Table2", dbFailOnError
dbOther.Execute "DELETE * FROM Table3", dbFailOnError
dbOther.Execute "DELETE * FROM Table4", dbFailOnError
dbOther.Execute "DELETE * FROM Table5", dbFailOnError
dbOther.Close
Set dbOther = Nothing

 

[Presto]

Thank you VERY much Mr. Steele!
the following code works perfectly to update all my tables.
________________________________________________________
Private Sub btnUpdateAll_Click()
On Error GoTo Err_btnUpdateAll_Click

Dim stDocName1 As String
Dim stDocName2 As String

' The following code deletes all the data in tables on the front end
' Code provided by Doug Steele - thank you.

Dim dbfrontend As DAO.Database

Set dbfrontend =
OpenDatabase("\\servername\sharename\foldername\Mydatabase.mdb")
dbfrontend.Execute "DELETE * FROM tblTable1", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable2", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable3", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable4", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable5", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable6", dbFailOnError
dbfrontend.Execute "DELETE * FROM tblTable7", dbFailOnError
dbfrontend.Close
Set dbfrontend = Nothing

' This runs a macro for all DELETE and INSERT queries on back end
stDocName1 = "UpdateTableData"
DoCmd.RunMacro stDocName1

' This runs a macro for all Append queries on front end
stDocName2 = "AppendFrontEndTables"
DoCmd.RunMacro stDocName2

Exit_btnUpdateAll_Click:
Exit Sub
Err_btnUpdateAll_Click:
MsgBox Err.Description
Resume Exit_btnUpdateAll_Click
End Sub
------------------------------------------------------

I also have individual buttons that update specific tables instead of doing
all of them.
A shorter version of the code is used.

In order to reduce the repetitive code for the following on each button:
Dim dbfrontend As DAO.Database
Set dbfrontend =
OpenDatabase("\\servername\sharename\foldername\Mydatabase.mdb")
Can it be done ONCE at the beginning of the form's module? Set the path ONCE
then
reference the path for each button?

My preference is to use

CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table1", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table2", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table3", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table4", dbFailOnError
CurrentDb.Execute "DELETE FROM [F:\Folder\File.mdb].Table5", dbFailOnError

I prefer the Execute method for two reasons:

1) You don't get the annoying "You're about to delete n rows of data..."
prompts.
2) Because of the dbFailOnError parameter, a trappable error will be
raised if something goes wrong.

You could also use

Dim dbOther As DAO.Database

Set dbOther = OpenDatabase("F:\Folder\File.mdb")
dbOther.Execute "DELETE * FROM Table1", dbFailOnError
dbOther.Execute "DELETE * FROM Table2", dbFailOnError
dbOther.Execute "DELETE * FROM Table3", dbFailOnError
dbOther.Execute "DELETE * FROM Table4", dbFailOnError
dbOther.Execute "DELETE * FROM Table5", dbFailOnError
dbOther.Close
Set dbOther = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Will something like this work?

Dim dbOther As DAO.Database
Set dbOther = OpenDatabase("F:\Folder\File.mdb")
dbOther.DoCmd.SetWarnings False
dbOther.DoCmd.RunSQL "DELETE * FROM Table1"
dbOther.DoCmd.RunSQL "DELETE * FROM Table2"
dbOther.DoCmd.RunSQL "DELETE * FROM Table3"
dbOther.DoCmd.RunSQL "DELETE * FROM Table4"
dbOther.DoCmd.RunSQL "DELETE * FROM Table5"
dbOther.DoCmd.SetWarnings True

dbOther.Close
Set dbOther = Nothing