Keep a Connection Open to the Back End Database While Your Application Runs

[fishhead]

I have 2 backend .accdb files that are linked to my front end application.

If there are no users other than me on the network the application works quite fast
as soon as 1 or 2 users log in the speed reduces substantially. I'm certain this is not a network issue as i've beefed up our network substantially.

Someone in this forum suggested (a while ago) that i should keep an open connection to the BE while the application runs. I also have this article explaining how to do this. https://www.fmsinc.com/microsoftacce...dDatabase.html

The problem is i have limited knowledge on how to follow these instructions and need some help with this.
Is there anyone in the group that would be willing to go through this with me?



regards,

[isladogs]

All you need to do is have a small linked table open but hidden whilst the database is open.
You could open that table (in the background) using your startup form or an autoexec macro.

As you have 2 BE files, you need two tables kept open - one from each db

If you have a settings table with one record (or a small number of records) that will be needed anyway, use that.
If not create a new linked table which can serve as your open connection.
That's it.

EDIT:
Just to confirm - each user need their own local copy of the FE on their own workstation

[fishhead]

thanks Colin, i'll try what you suggest with fingers crossed!

[fishhead]

HI Colin,
i created a form that has the control source from both BE dbases and added it to the autoexec macro (hidden)as you suggest. this form has two subforms (one for accounts receivable and the other for accounts payable). the idea is when the user opens the main form it shows both the AR & AP for a file with a couple of extra fields calculating the profit and taxes etc.

Normally this form takes a long while to open (again only when other users are on the network) and i suspect this is because the AP & AR tables each have +18000 records in them that need to be sorted by the invoice number. (meaning a specific invoice number may only produce 2 AR lines and 2 AP lines for example). This Query is what's taking the time to open (again only when others are on the network).

Long story short having the new "dummy" form (that is linked to a query with both these tables )open hidden via the autoexec macro doesn't seem to make any difference in speed.
Do you have any other suggestions i can try?

tks

[isladogs]

I did say to use small tables. Ideally one field & one record. Maker sure it stays open (or in your case, both tables stay open)
Once that is in place, open a query or form using another linked table(s). It should usually be faster but not always as other factors may be causing greater delays
See e.g. https://stackoverflow.com/questions/...inked-database

[fishhead]

HI Colin,
i recreated a new form with a table from both BE dbases (each with only 1 record in the table as you suggested) . if there is any increase in speed it is not noticeable. The bottom left corner of the screen shows "....calculating...." during the lag.

the new form is keeping both BE dbases open (again two tables each with one record each) and the "calculating" form has two BE tables each with +18,000 records to sort through before the FE application gets updated. Can you suggest anything else to try to speed things up? (reminder this only seems to be happening when others are on the network. If it's only me the FE application is nice and fast : )

tks,

[isladogs]

There are many possible reasons for poor performance in a split database.
I suggest you next try the DAO.OpenDatabase method in the FMS link you provided

There are many other tips on both the FMS & Allen Browne's websites