I'm working on a program that needs to edit some objects in an Access database. It also runs a subprogram (long story) that tries to access the underlying JET database while Access still has it open via ODBC.
The problem is that as soon as I start editing Form objects using VBA - for example, using Application.LoadFromText - Access changes the database to exclusive mode. Exclusive mode itself is fine, and I know why it needs it. But I need to be able to switch back to "shared" mode afterwards so that I can run my subprogram.
I've observed that if you use the UI to open a Form in Design mode, Access switches the database to Exclusive. (You can confirm this by trying to open it from another computer.) But when you then close the form designer, Access immediately switches it back to shared mode, which is what I would hope for.
Is there a way to switch it back and forth myself using VBA / COM calls?
I know I can call Application.CloseCurrentDatabase() followed by OpenCurrentDatabase(), but that closes all the windows and upsets the UI, so it's not ideal.
Is splitting the database into a separate front-end with the forms/modules/etc. and back-end with the tables an option? That way if the front-end is locked, the back-end is still accessible. Access has a database splitting wizard for just that.