Efficiency question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 30 Apr 2005 01:37 GMT

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.

Reply to this Message

Mark - 30 Apr 2005 07:50 GMT

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

> 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
[quoted text clipped - 15 lines]
> Thanks for any advice.
> Mark.

Reply to this Message

Mark - 30 Apr 2005 09:01 GMT

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.

Reply to this Message

Douglas J. Steele - 30 Apr 2005 11:27 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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
[quoted text clipped - 52 lines]
>> Thanks for any advice.
>> Mark.

Reply to this Message

Mark - 30 Apr 2005 21:13 GMT

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

> I'd recommend checking whether tdf.Connect <> "" (or, perhaps better,
> Len(tdf.Connect) > 0), but other than that, it looks as though it should
[quoted text clipped - 56 lines]
> >> Thanks for any advice.
> >> Mark.

Reply to this Message