Access Multi user app, code runs slow when more users are logged in..

[klaas01]

Hello,

I have made an access application which runs a vba update script every 1 minute. The code opens a foxpro database and updates the changed records to the access backend.

The updating process is working fine, but the time needed for the updating is much longer when more than one user is logged in in Access as when there's no other user logged in..even if there's nothing to update...

It seems nothing to do with the foxpro db, because when I run the update process in de development version, the code runs in 5 seconds while it takes more than 1 minute in the live version.

I think it has something to do with record locking, but i'm not sure.

Does anyone experienced such a problem? And does anyone have a solution for the problem?

many thanks

 

[MajP]

Does each user have their own front end on their local computer? How are you connecting to the Foxpro? Is the connection persistent?

 

[klaas01]

Hi Majp,

I'm connecting via odbc; I linked the foxpro tables in access.

The performance is good as long as only one person is logged in in Access..

It looks like access needs a lot of time opening the tables in the backend when other users are logged in.

 

[MajP]

1. Does each user have their own front end on their local computer?
2. Does each front end keep a persistent connection (linked form) to the backend

 

[klaas01]

1. Each user opens the same front end file from a server
2. I don't know what you mean with linked form, but they aren't linked to the back end, the back end only keeps the data. The forms are in the front end and the tables are linked to the back end. (There's no local table in the front end)

 

[klaas01]

ps I've tried to run the update code from a copy opf the front end, so that ther's only one user loged in the update file. This is not making any difference..

 

[MajP]

You should never ever have the same users using the front end on the server. Not only is this probably the cause of the slowness, but it is a very good way to corrupt you front end and possibly the backend tables.

Create each user a front end, and distribute that to them. Without doing that, you are just fishing for other possibilities.

Once you have done that, there may be some additional tricks to speed things up.

 

[MajP]

http://www.fmsinc.com/tpapers/faster/index.html

THis is a very good resource

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

Both have a discussion for persistent recordset locking.

 

[klaas01]

Aha! Now I know what you mean with persistant connection

Problem is solved by having the keepopen form in de front end

Thank you VERY much!