Automatically Close then Reopen DB

[asjohnson]

Does anyone know how VBA can automatically close then re-open the same DB?

 

[RickSpr]

You can't do this from within VBA in Access, because as soon as you close the database the code stops running.

However, why would you need to do this anyway? Are you just trying to close all the forms and reports and open the startup form again? You can do these things in code. You might also have to reset some module-level variables if they would alter the function of your application. Rick Sprague

 

[asjohnson]

Actually, I have a replicated DB. I read on Microsoft's site that described when replicating using VBA, that you should close then re-open the DB to see design changes. Am I mistaken?

 

[RickSpr]

Well, I don't know anything about using replication, but that still doesn't mean you can close and open in code.

Your question has come up before (not in connection with replication), and I did a good bit of research looking for a method or procedure that could close and reopen the current database. I wasn't able to figure out a way. I'm not sure there isn't a way, but I'm normally pretty good at figuring these things out.

I couldn't find anything in the Help docs about needing to close/open in order to see design changes. Why do you need to do this in code? The replicas will have to be synchronized anyway, before they see design changes, so that doesn't seem to be an issue. As for your Design Master, why can't you just close and open it manually? Again, I haven't done anything but play with replicas once, a couple of years ago, so maybe I'm just ignorant. Rick Sprague

 

[asjohnson]

You're definitely not ignorant, I am. I did more research on this. MS suggests that you create a separate utility to that compacts and synchronizes databases. This give you more control. By doing this in VB, instead of VBA, you can compact the DB before synchronization, thus no need of closing then re-opening the DB to see changes.