Append Vs Make Table - Explain this?

[Mark]

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

[Steve Jorgensen]

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" <ma**********@n tlworld.com> wrote:

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

[Trevor Best]

Mark wrote:

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.

What Steve said + you seemed to have missed the point about using a
separate FE/BE, which is really the crux of your problem.

--
[Oo=w=oO]