Please can you advise me on a number of recurring problems that we are experiencing using Access databases. At our organisation we have recently migrated from Access 97 to Access XP, and we have a large number of access databases. We have converted them to the Access XP format and have since found a number of problems with them in a multi-user environment.
The first one is the error "The database has been placed in a state by user 'Admin' on machine '<machine name>' that prevents it from being opened or locked." I have read a previous MS Knowledge base article 248967, but this applies to MS Access 2000 only. How can we get around this?
A second is that mail merging always prints out dates in American format (mm/dd/yyyy), whereas it used to print out in UK format (dd/mm/yyyy) under Access 97. Is this standard now? We have to add formatting switches to the mail merge fields to handle this conversion whilst Access 97 had no such issues. All machines have UK regional settings.
The message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." appears in multi-user databases, when we generate reports from queries (using the toolbar). There is a MS knowledge base article, 283228, which states this is normal practice - do we therefore have to create external mail merged word documents to create the reports we desire - or is there another way.
Royal College of Nursing of the United Kingdom.
I recently worked at a Trust that converted its '97 databases to XP. I don't know if our experiences would be relevant to yours, but here goes.
All our databases created temporary tables on the fly for reports and so forth. Every user has to have a seperate front end for this to work. I don't know if all your users are accessing the same copy of the database, but if they are that needs to change, NOW.
Our databases also interracted owth other Office components, in particular Word. All mailmerging was controlled from within Access. This caused problems, as XP has an enhancement, it won't let you create a mailmerge data file twice if hte second one differes in any way from the first. You don't seem to have this problem.
Long and complex databases appear to convert successfully then go strangely wrong. Don't convert them. Create a blank XP database and import all the objects from '97. If you have the time give admin ownership of evrything and create a new security file for the XP version .