Switch databases in Access.

[Olav]

Hi,
I have a system based on 25 different mdb databases where most of the data is located on a SQL server.
The main database is a protected database with a login dialog. After the login dialog, the user is asked to select a sub-system.
Each sub system is a stand-alone database, some are protected, and some are not, but they may still require the login name to work.

Due to the strange fact that running a mdb database from a remote computer with Access 2007 is at least 5 times slower than with Access 2003, the mdb files have been stored locally on all clients. Since the total size of the mdb files is ~200MB the files has to remain splitted to minimize waiting times during a database update/repair. (60+ clients trying to download a 200MB file simultanously on a 10Mbit network while working on external files may make some users rather furious...)

Currently we do the switching between databases by using the macro function "SendKeys" with the parameters like ^(o)u:\app\startup.mdb%(o), but some users have problems with this command. -The open dialog does not contain the filename, and sometimes the wrong directory is opened.

I have tried %(fo)[databaseName].mdb%(o), but for some users the file menu appers, and the macro stops.

I have also tried %(fo)[databaseName].mdb~, but this causes the macro to loop.

Is there a VBA code that can do the same thing as the Macro function SendKeys ^(o)u:\app\startup.mdb%(o) without sending key-strokes?

I have tried Application.FollowHyperlink "U:\app\startup.mdb", but this causes a new Access window to open without the necesary permissions.

I have also tried Application.OpenCurrentDatabase "U:\app\startup.mdb", but this shows the error message Run-time error '7867': You already have the database open." -The same error occur if I try to open a database that does not exist.

I need to open the new database in the same access-window to prevent the permission error to occur, but at the same time, it must be possible to open multiple instances of the same database on one computer.

-Olav

 

[gemma-the-husky]

sendkeys "keysequence"

eg

sendkeys "{esc}" sends an escape character

 

[Olav]

Yes, this works for some users, but not all. when I use the the command sendkeys "^(o)U:\app\startup.mdb%(o)", some computers stops after "^(o)" and ignores the rest.
^(o) = Ctrl + o, %(o) = Alt + o

Does there exist a function that will do the same as the key sequence: Ctrl + o (open file dialog) enter "U:\app\startup.mdb" in the file name field and Alt + o (Click the Open button)?
I have also tried Application.OpenAccessProject "U:\app\startup.mdb", but I get an error saying that the database is already open.

This does does not work either:
Public Function OpenStartup()
Application.CloseCurrentDatabase
Application.OpenAccessProject "u:\app\startup.mdb"
End Function

 

[Olav]

BUMP - Still not solved.

The Sendkeys action seems to fail on users with multicore processors.
I do not know if this is the reason for the function to fail.

I only need help with vba code to replace the sendkeys function.
Scenario:
One user running multiple instances of Access 2007
User clicks on a button, and the following pseudo code is performed:
Menu command: File -> open
Enter file name: otherDatabase.mdb
Click Ok

-No new instances of Acces should be created. Access 2007 should only open a new database in the active Access instance.

The following code works on the oldest computers: Sendkeys ^(o)u:\app\otherDatabase.mdb%(o)