If you have multiple databases in your application, you may need to switch
to a different mdb file at times. The most often recommended method is
to Shell out to the new database in another instance of Access and then
Quit the current instance from code. Depending on the machine and
amount of available resources, you may want to reuse the same Access instance
for other databases.
Access 2000 & Access 97:
Michael Kaplan has made
available a COM Addin that allows you to shut down an Access database and optionally open another one. No Access 2000 application should have to use Sendkeys
statement, ever again! (IMO).
The only solution is to use SendKeys to pass the new database path
to the OpenFile dialog. Beware of the associated
bug and the fact that any sensible VBA developer will tell you not to
use SendKeys in any application.
Sub sOpenMDB(strInMDB As String)
On Error Resume Next
SendKeys "%FO" & strInMDB & "~"