Hi Guys,
I recently split my database into 2: back-end (tables) and front-end (forms and rest). The reason for doing so was that back-end will be shared by multiple users and they'll be working on it at the same time. Split itself went well - fast and without any surprise. What surprised me is what happened next - my database (the front-end part) started to work significantly slower! I didn't expect it. There's especially 1 form, which is designed to calculate a new project end date based on several chosen steps (it analyzes the relations between steps and their duration). Previously, before I split my db, it took about 1 minute for my script to calculate and find the end date (it's quite complicated process) and this was acceptable. Afterwards, it takes about 20 minutes to do so! This is way over the top! How can I decrease the time to more reasonable time?
I must say that the script heavily uses the functions I wrote to retrieve a certain information - for example to retrieve step's duration from related table. The function opens dao's recordset, takes out the data and closes the dao.database afterwards. I think this might be problem as that function runs several times during the process and I heard that it's better to always keep connection to back-end database. And I think now that statement
kills the connection every time, right?Set db = Nothing