Hello,
Background: Once upon a time I had a database for my business unit built using SharePoint Lists. Due to business requirements, we do not allow deletion of records for employees that depart the business unit (either good/bad reasons). Once we hit 1,000+
records we began experiencing random issues: some managers were getting "disconnected" from SharePoint after editing an employee record, others were attempting to view their team employee roster and were first presented with an empty team (after
a short time, the records populated properly). We are unable to use link databases through the common network share (performance is unacceptably bad); however, I discovered I could essentially use SharePoint as the "network share" and have fairly
acceptable performance. (No more lists)
Our database now operates as a split front-end/back-end (FE/BE) using the SharePoint UNC (\\XXXX@SSL\DavWebRoot\etc) to point at the back-end.
- One issue I noticed early on was that the front-end somehow became "locked" immediately after the app started (welcome screen, or just about any form that did a DLookUp/CurrentDb, displays); I never found the issue behind this, but it seems to
have now totally vanished.
- There are times where the FE can't seem to open the BE until the user has accessed a SharePoint site (authenticates automagically via AD). Then, all is well.
- Editing records is SLOW, moreso than it used to be when it was based on SharePoint lists. This is likely due to the requirement that I need to track (audit) changes made to employee records; it's currently set up as a Data Macro (which
the F/E automatically executes quite dutifully) that inserts a record into the audit table for every employee record with audited fields that change (not every field is audited). It is also required for the user to notate the reason for the change, so the
F/E UI captures this note and runs another query to update the notes field on the employee record. It took me about 20 minutes today to edit 30 records. There are other data macros that also track modification time and username for every record change.
Can anyone make suggestions on improving performance in this scenario?
- Again, using the actual network share is unacceptably slower than the SharePoint via UNC. Plus, IT threatens us with fire and brimstone for having databases on the network share.
- One thing I attempted was the trick of having the B/E database opened via OpenDatabase() and keeping that reference up. It didn't seem to have much of an impact.
