Efficiency question

[Mark]

I need to transfer records from a utility database to temp tables in the
frontend of a frontend/backend system. Later, these are appended or
updated into the backend database.

My question is whether it is more efficient to empty the temp tables via
delete queries, link to the utility database, and run append queries to
import the records into the temp tables, OR is it better to use
DeleteObject on all the temp tables in the frontend database, and then use
TransferDatabase to import the tables from the utility database (these are
also named temp...).

Specifically, I'm wondering whether there's any difference in terms of
eventual bloating of the frontend, time for processing, or other meaningful
differences which I might not be aware of. Using the
DeleteObject/TransferDatabase approach does the delete and append queries
necessary to empty and then fill the frontend's temp tables.

Thanks for any advice.
Mark.

 

[Mark]

Sorry about the last sentence:
.... I meant that those queries would not be needed using the
DeleteObject/TransferDatabase approach .

 

[Mark]

I'm thinking that maybe I don't need to use either approach ...
If the Temp tables are always linked to utility.mdb, then the data is
already "in" the frontend database and available for updating/appending to
the backend tables.

One concern, however, is for other procedures which refresh, or even change
the links of my main tables (swapping backends for different regions, e.g.),
how do I specify the different paths for the main tables and temp tables?

perhaps?)

BackendPath = "(some path)"
TempPath = "(path to utility database)"
For Each tdf In Tdfs
On Error Resume Next
If tdf.SourceTableName <> "" And tdf.SourceTableName <>
"Temp*" Then
tdf.Connect = ";DATABASE=" & BackendPath
tdf.RefreshLink 'Refresh the link

ElseIf tdf.SourceTableName <> "" And tdf.SourceTableName =
"Temp*" Then
tdf.Connect = ";DATABASE=" & TempPath
tdf.RefreshLink 'Refresh the link

End If
Next 'Goto next table

..... does this correct?
Thanks,
Mark.

 

[Douglas J. Steele]

I'd recommend checking whether tdf.Connect <> "" (or, perhaps better,
Len(tdf.Connect) > 0), but other than that, it looks as though it should
work.

 

[Mark]

Thanks Doug, and I did change it using your suggestion: Len(tdf.Connect) >
0).
Mark.