So you can't just incorporate that one table in your db and have that user set a link to it?
One very simple approach is to have a permanent table in your backend with the same structure as the user's table. Then just delete records and repopulate. I recommend this over repeatedly deleting and creating table.
The first INSERT SELECT SQL shown requires that the fields all be in the same order in the design of both tables (the display order in datasheet view is irrelevant), which is nice if you have a lot of fields to fill, otherwise have to explicitly reference the fields as shown in the alternate SQL.
Code:
Public Sub GetData()
Dim strPathBackend
Dim strPathOtherDB
strPathBackend = "path\filename.accdb"
strPathOtherDB = "path\filename.accdb"
CurrentDb.Execute "DELETE FROM tablename IN '" & strPathBackend & "'"
CurrentDb.Execute "INSERT INTO tablename IN '" & strPathBackend & "' SELECT * FROM tablename IN '" & strPathOtherDB & "';"
'or this
CurrentDb.Execute "INSERT INTO tablename(field1, field2) IN '" & strPathBackend & "' SELECT field1, field2 FROM tablename IN '" & strPathOtherDB & "';"
End Sub
However, if the table has attachment or multi-value or OLEObject fields, either have to not include those fields or code gets a lot more complicated.
This is not the approach I was originally thinking of that involved setting OLEDB connection in VBA.