SQL Select very slow from the moment a second user is connected

[alfalf99]

Hello,

I have to manage an Access application, and I am faced with a strange issue

Let me explain

this app used by several users contains 2 files backend and frontend
* Backend file is a .mdb (access 2003)
* Frontend is also a .mdb
* Users all have Access 2010 installed on their computer.
* Backend file is on the local network


* Frontend is file is deployed on computer of each user

Application contains a lot of forms and fields (too many...)

When a user connects to the frontend and click on a specific button a procedure is launched and several sql requests (simple selects) are executed (to the linked tables of the backend file ), then forms are updated. It works like a charm.

BUT, from the moment another user connect to the app (laucnhc its own frontend), so 2 users connected, answer times are very slow for the 1st person, for the same action (click on the concerned button), 10 seconds instead of 1/2 second, while the second person is just connected and do nothing !!

I believed it was the network, but I don't understand why it is very slow why the second user do nothing

I have read a lot of posts and documents on this kind of problems, I have made several modification
* Migrate in Access 2010 (backend and forntend)
* keep an active link between backend and frontend
* Check Access client option (share mode enabled for example) but the problem is still present

Perhaps it is the way to do the request select (db.openRecordset with the sql select in VBA code), but it works great with one user...

Hope someone will be able to help me

Thank you in advance

[June7]

SELECT queries do not need to be 'executed'. What do you mean forms are 'updated'? Are you using UNBOUND forms?

I have a split db with multiple simultaneous users and never had this issue.

[ItsMe]

I use SQL in DAO OpenRecordset methods. It is probably faster (better performance) to reference a Named Query Object.

The fact that the query performs at different speeds causes me to believe it has to do with recordlocking. Perhaps using dbOpenSnaphot is an option. Be careful using this because it is not a dynaset and your recordset will not recognize changes made at the table layer by other users until after a new snapshot is retrieved. Also, snapshots are not updateable.

Another thing to consider is that as soon as a user opens their front end file and the FE has linked tables. When a dynaset is opened, an laccdb or an ldb file is created to keep track of record locking, even if no records are currently locked (dynaset opened optimistic). Any attempt to open a dynaset will first ask the FE file to read the ldb file (of the BE) and determine what is OK to do and not OK to do.

[alfalf99]

There is several forms, and there is an action me.myform1.Requery, me.formé.requery, etc...
And in the Form_current() procedure which is executed, there is some action on soem fileds, for example
Me.Bremark.ForeColor = vbRed

I am sure there is a config issue, or a bad implementation problem, but which one, very difficult to says for me...

[alfalf99]

Thank you for your comment.
I have tried this option, without any success. I think the same about locks, but difficult to understand since only one user has some activity. It remains the acces to the ldb file indeed.
I have tried to locate the BE on a basic computer (not our busy file manager server, as currently), but still the same behavior.

NB: additional information : I have debugged the code, and it is long in the instruction db.openRecordset (select to a linked table) as explained in the intial post.

Could you tell me how I can keep an alive connection with the linked tables ?
currently, there is this in the code (in the main form)
'activation of permanent link
DoCmd.OpenForm "UNLOCK", acNormal, , , , acHidden

and in the form "UNLOCK:
Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("T_UNLOCK")
End Sub

and the "T_UNLOCK" table is a linked (empty) table

Is it correct ?

Thank you

[ItsMe]

I do not see anything in your last post that indicates you ARE NOT using linked tables or at least local tables in your DAO.

I would consider your query structure and the need to fire new dao recordsets in the On Current event.