Slow data access on Network(LAN)

[GGerard]

Hello

I have just started using an application on a network(LAN) with multiple
users



I have the front end installed on different computers and the back end
(data) installed on the host computer of the network so that each computer
running the application links to the data located on the host.



When one user is logged on everything seems to work good ; access to the
data is normal.

When a second user logs on, the speed of access to the data, especially for
the second user, is greatly reduced.

It takes a long time to requery any forms for the second user.



For both the front end and the back end the following options are set:



Under Tools / Options /Advanced



DDE operations is Enabled ( check mark on "Enable DDE
refresh")

Default open mode is set to "Shared"

Default record locking is set to "No Locks"

Open databases using record-level locking is checked



I do not have the word "excl" written in the application's icon command line



The front ends are all MDE and are opened as Runtime



Any idea where I should look next?



I am using MSAccess 2000


Thanks

G.Gerard

[MGFoster]

Re: Slow data access on Network(LAN)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There may be various reasons. One that is common is each user is
pulling a large recordset when they open a form. This will slow down
the processing. To correct this set up a search form, or routine on the
main form, that will allow the user to select one, or only a few,
records at a time.

An example: In the form's Header place a TextBox and a CommandButton.
Name the TextBox "txtSearch, " label it "Search for Employee ID," and the
CommandButton "cmdSearch, " label it "Find." Then create the following
routine:

Private Sub cmdSearch_Click ()

' Set up the SQL to use whatever tables/columns you
' are using to fill your form.
Const SQL = "SELECT account_id, employee_id, sales " & _
"FROM AccountsReceiva ble " & _
"WHERE employee_id = "

If Not IsNull(Me!txtSe arch) Then
' This will pull all the records for requested employee_id
Me.RecordSource = SQL & Me!txtSearch
End If

End Sub
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAolpYechKq OuFEgEQJKJwCgw/7SLUozGjELnT7rf uaF9iMVbkIAnR4i
revW7eJyS0OTlhq gjO+c/KeE
=K/Sp
-----END PGP SIGNATURE-----


GGerard wrote:[color=blue]
> I have just started using an application on a network(LAN) with multiple
> users
>
> I have the front end installed on different computers and the back end
> (data) installed on the host computer of the network so that each computer
> running the application links to the data located on the host.
>
> When one user is logged on everything seems to work good ; access to the
> data is normal.
>
> When a second user logs on, the speed of access to the data, especially for
> the second user, is greatly reduced.
>
> It takes a long time to requery any forms for the second user.
>
> For both the front end and the back end the following options are set:[/color]
[color=blue]
> Under Tools / Options /Advanced
> DDE operations is Enabled ( check mark on "Enable DDE
> refresh")
> Default open mode is set to "Shared"
> Default record locking is set to "No Locks"
> Open databases using record-level locking is checked
>
> I do not have the word "excl" written in the application's icon command line
>
> The front ends are all MDE and are opened as Runtime
>
> Any idea where I should look next?
> I am using MSAccess 2000[/color]

[Albert D. Kallal]

Re: Slow data access on Network(LAN)

since one user performs ok, then obviously the application CAN perform ok.

when you experience the above, the delay has to do with ms-access switching
into multi-user mode. MS-access also tries to delete the locking file.

There is a HUGE dance that occurs when the file is being opened..and this
dance continues when you try and open a form.

The simple solution here is to force the connection to remain open at all
times. Thus, in your start up code, simply open up a reocrdset, or simply
minimize a form that is attached to any table in the back end. Try
this...you see the response become the same for 1 user...or 5 users...

This trick..and number of things to check can be found here

http://www.granite.ab.ca/access/performancefaq.htm

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal