Copy tables but only within a linked database

[Guest]

Thank you all for any suggestion that you can provide.

Question:
In a front end/back end database configuration how can a table be copied
from another table within the back end database and then re-linked to the
front end database?
When I issue the commands below, the table gets copied to the Front End
database instead of within the Backend database where I need it to be..

More explanations about the database are further down, below the code example.

Current CODE
'-- Deleting Main Table so that Autonumber ID# can start from 1 again when
importing new data. This table is originally a linked table

DoCmd.DeleteObject acTable, "tblProperties"

'--- Copy empty table from Template - (This ends up being a local table).
DoCmd.CopyObject , "tblProperties",acTable, "tblPropertiesTemplate"

'--- Populate empty table with append query
stDocName = "qryPropertiesGenerateNew"
DoCmd.OpenQuery stDocName, acNormal, acEdit

_________________________________________________

Background Details about the database.
I have a database in which some tables are periodically repopulated with new
data downloaded from the internet. Users then select and manipulate such
data independently from one another in the local machine.

To avoid rapidly escalating Autonumber IDs, (as it would happen if the
content of the tables were first deleted and then repopulated with an append
query), I simply delete the tables and then copy brand new tables from empty
templates.

This works fine, as long as Forms/Queries and Tables are contained within
the same database on each user PC.

In order to continue development, I have now split the database so that I
can provide users with updated Front ends without affecting the data they
have manipulated on their own PC. Each PC therefore has a Front end and a
Backend database in the same folder.

The problem is now that at import time, when the linked tables are deleted
and then recreated by copying them from the templates, the recreated tables
end up in the Front end database instead than in the linked database.

At this point the user sees everything normal, but he is actually using data
from new tables within the front end instead of linked tables in the back
end.
As a result, the next time I provide the user with a new Front end database,
those local tables get overwritten.

Any suggestion that you can provide is appreciated.
Gino

 

[Dirk Goldgar]

Comments inline ...

Thank you all for any suggestion that you can provide.

Question:
In a front end/back end database configuration how can a table be
copied
from another table within the back end database and then re-linked to
the front end database?
When I issue the commands below, the table gets copied to the Front
End database instead of within the Backend database where I need it
to be..

More explanations about the database are further down, below the code
example.

Current CODE
'-- Deleting Main Table so that Autonumber ID# can start from 1 again
when importing new data. This table is originally a linked table

DoCmd.DeleteObject acTable, "tblProperties"

I don't think you want to execute the above statement. That will delete
the linked table in the front-end, but you're going to want to use that
linked table again, so you may as well leave the link in place.

'--- Copy empty table from Template - (This ends up being a local
table). DoCmd.CopyObject , "tblProperties",acTable,
"tblPropertiesTemplate"

You could use CopyObject, but you have to specify the name of the
back-end database as the (currently missing) first argument. However,
I'd recommend using TransferDatabase instead, since then you don't have
to explicitly delete the back-end table first. Try this:

Dim strBackendPath As String

strBackendPath = "<path to the back-end>"
' you can extract the path from the linked table, if you want.

Docmd.TransferDatabase _
acExport, _
"Microsoft Access", _
strBackendPath, _
acTable, _
"tblPropertiesTemplate", _
"tblProperties"

' Refresh the table link. This may not be necessary,
' but I don't see how it could hurt.
CurrentDb.TableDefs("tblProperties").RefreshLink

'--- Populate empty table with append query
stDocName = "qryPropertiesGenerateNew"
DoCmd.OpenQuery stDocName, acNormal, acEdit

I'd probably use the DAO Execute method instead, to avoid having to
respond to the confirmation prompt (or else having to turn warnings
off):

CurrentDb.Execute "qryPropertiesGenerateNew", dbFailOnError

Note that the last two steps could be done using the same database
instance:

'ALTERNATIVE FORM
With CurrentDb
.TableDefs("tblProperties").RefreshLink
.Execute "qryPropertiesGenerateNew", dbFailOnError
End With

 

[Guest]

Thank you for the suggestions as well as for the additional tips.
Gino