How to select a table to delete from another database

[ravness]

Hi All,

I have three databases; Frontend, Backend, Snapshot.

I want to be able to have a feature in a form in the frontend database where I can delete a table which I select from the Snapshot database.

Any idea how to do this?


I have the following code

'OPEN SNAPSHOT DATABASE
Set oAccess = New Access.Application
oAccess.Visible = False

'RUN TRANSFER CODE IN SNAPSHOT DB
With oAccess
.OpenCurrentDatabase sDBLocation & sDBFilename 'snapshot db
.DoCmd.SetWarnings False
.DoCmd.CopyObject sDBLocation & sDBFilename2, _
"tblEmployeeReport_TEMP_Snapshot", acTable, _
"tblEmployeeReport_TEMP_" & sDateStamp
.DoCmd.SetWarnings True
.CloseCurrentDatabase
End With
oAccess.Quit
Set oAccess = Nothing

I need to change the copy object to delete object without errors please help

 

[HiTechCoach]

To get a list of the table names, you can use:

1) A query on the MSysObjects table tin he snapshot database:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") 
  AND ((MSysObjects.Type) In (1,4)));

2)

Dim obj As AccessObject, db As Object
Set db = OpenDatabase("snapshot.mdb")
 
For Each obj In db.AllTables
  Debug.Print obj.Name
Next obj
 
Set db = Nothing

Note: You really do not need to start a new Access Application instance to do this as you did in your example