I have a 90M Access 2000 DB running on a Windows 2003 Server network. The DB runs on the server is NOT split and seems fine with 2 (maybe 3) concurrent users but once more people use it then I need to Compact & Repair it regularly. A couple of times I’ve got a message about ‘can’t find sysmd’ and then have had to revert to a back-up.
I understand the principle of splitting the DB and creating local Front Ends for each user. When I do this the BE is 18M and the FE is 80M (there are many forms and reports with OLE’s, etc). What I was wondering is will splitting it make it more reliable with say, 4-6 users? Also, in order to reduce the size of the FE I was going to separate the specific queries, forms & reports to different departmental specific ‘mini front ends’. Is this a good idea? And if so then would there be any further conflicts if I gave some users Edit or Design rights (most posts suggest making these MDE's) to their own department FE’s?
Apologies for the length but this is my first post…
Any advice gratefully received – many thanks!
I agree with JS - you should try using linked OLE instead of embedded to reduce the size of FE but also among the different versions of access from 97 to access 2003, access 2000 is the most unreliable and buggy version according to my experince with it on different OS(s) even after applying the SPs. If you can update it to atleast access 2002 that might help with reduceing the run time system errors or bugs.