sql server was too
slow (under resourced for all the activities it was required to do)
This is really what I was worried about happening if I went down that road. Plus, I know nothing about web-based apps. So I've still been using Access.
If the
database is split then you want to use a persistent connection. It makes a bit difference.
I read the article provided by @Shadow9449 and I am really hoping that maintaining an open connection with the backend (at all times) would help with the speed issues. I am a little confused about that actually process though. When I declare some variable (say db) as a DAO.Database and set it the current database, is that point the instant where the connection is established?
Code:
Dim db As DAO.Database
Set db = CurrentDb()
Up until this point, I have been Dim-ing and setting the DAO.Database in the Subs/Forms that I need. Using the OpenDatabase() Method, will all of these "Dim-ing & Setting" commands be redundant? Would if it be better to remove all of my declarations/initializations of db -> CurrentDb(), and do it once the database opens. (Via the AutoExec Macro?) I guess I am unsure if the act of opening the database is the same as setting the variable (or pointer?) db to that CurrentDb().. Also, is there some sort of "Anti-AutoExec" that automatically runs when the application closes? I'd like to be able to close the connection to the database.
The lock file also confuses me. Once the lock file is made, is that current 'version' of the backend 'locked'? Are updates to the backend going to be properly reflected if the lock-file is present? Would I need to introduce a lot of 'Refresh' commands throughout my Subs to ensure I am getting the most current data? Does this introduce any new problems? (Ex: I have some users that never close their local, front-ends. The lock file would exist forever, right?)
Sorry that was so much, this has been a hot-button issue and I'm really excited to increase speed of data transfer!