| re: Append Vs Make Table - Explain this?
For one thing, Access databases are simply much slower when they're shared.
The speed goes down noticeably as soon as the second person opens the
database. For another thing, you may have proplems with something calle
Operational Locks if one or more users is on Windows 98, and one or more is on
a Windows 2000 workstation. Operational Lock problems can introduce severe
slowdowns.
On Tue, 29 Mar 2005 06:18:22 GMT, "Mark" <mark.reeed75@ntlworld.com> wrote:
[color=blue]
>I have a FE/BD 2002 DB on a XP pro platform.
>I know this is ugly but it works for me......
>A text file is produced from our Oracle WMS. (Average 20k records)
>A command button deletes all records from a 1 field input table and then
>opens a form bound to this table.
>Results from the oracle query are pasted into the input table.
>When the form is closed, a destination table is cleared of all records and
>then each record from the input table is split up and then appended to the
>destination table.
>
>My problem has been, when only one user is logged into the FE, the whole
>process take a couple of seconds. When more than 1 user is logged in, the
>process takes several minutes.
>
>Steps I have taken:
>I created a hidden form on the FE which opened on startup and was bound to a
>small table in the backend.
>I have changed the subdatasheet names to none on all tables in the BE
>I changed the query property "Use Transaction" to no on the above queries.
>Made sure the database was opened with no locks
>
>Only the bound form on startup made any difference which was minimal.
>
>Today, I changed the append query to a make table query so that the table is
>overwritten each time. This has done the trick and the process is down to a
>few seconds with multiple users logged in.
>
>My question is.... why? I ran each query independently and it was the append
>that was slow. Surely an Append would be quicker than making a new table and
>populating it with the same number of records?
>
>I look forward to your thoughts,
>
>Mark
>[/color]
| 
