Open database and switch to it

[Jaye7]

I want to open a different database from my current open database and if it is already open then switch to it.

I found a script that will open the database but if it is already open it will not switch to it.

But my real question is the following and I will get slammed for it as I'm certain it is a stupid question.

Can I open the database without the prompt to input username and password without having to remove the protection for the database.

I can store the username and password in a hidden form when the database opens, so I will always be able to know who the user is and fill tables etc... that require the username to be filled in.

I know that protection is there for a reason, but you never know unless you ask.

I posted this question on the following also.

Open database and switch to it - Access World Forums

 

[xenou]

I found a script that will open the database but if it is already open it will not switch to it.

What is your script?

Offhand I don't know how to check if another DB is open. Seems odd. Why have two databases open? Better would be to connect to the other database as needed - you can link tables and retrieve data without opening a database.

 

[Jaye7]

Hi Xenou,

I actually tested quite a few but found the following which seems to work the best for me, as with the others I tried they seemed to only work sometimes, whereas this works every time.

Dim strInput As String
    strInput = "C:\DBase\KLIKSTORES PURCHASING SS NOT LIVE.accdb" 'This references a text box on my form that has a file path
    Application.FollowHyperlink strInput, , True 'Setting this to True opens the database in another window

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
    
    If fso.FileExists("KLIKSTORES PURCHASING SS NOT LIVE.laccdb") Then
           
            'MsgBox "Access is open!"
            
            Dim OtherDB As Object
            sOther = "C:\DBase\KLIKSTORES PURCHASING SS NOT LIVE.accdb"
            Set OtherDB = GetObject(sOther)
            'OtherDB.Forms!Mainswitch!test1 = "Help"
            
            OtherDB.DoCmd.OpenForm "StorageForm", , , , , acHidden
            OtherDB.Forms!StorageForm!txtPickFilter = Null
            OtherDB.Forms!StorageForm.txtPickListItem = Null
            'OtherDB.CloseAllForms_KlikStores ' close any open forms to avoid errors, must run this from other database
            
            OtherDB.DoCmd.OpenForm "PickFilter"
            OtherDB.Forms!PickFilter!txtReturnToForm = "MainSwitch..Pick"
            OtherDB.Forms!PickFilter!PURCHASING = "Purchasing" 'set this so only close forms when have come from Purchasing Database
                       
            OtherDB.Forms!PickFilter!lblFunction.Caption = "Picking"
            OtherDB.Forms!PickFilter!boxFunction.BackColor = RGB(34, 177, 76)
                        
            Dim MacroRun As String
            MacroRun = "PickFilter_From_Purchasing"
            OtherDB.Application.Run MacroRun
        
            Set OtherDB = Nothing
            
            Else
            
            'MsgBox "not open"
        
    End If

Every script that I saw when googling they kept saying that the following method is the best, but it fails more often that it works.

'Grab the database if open
Set appAccess = GetObject("C:\DBase\KLIKSTORES PURCHASING SS NOT LIVE.accdb") 'this opens a new instance of access
appAccess.DoCmd.OpenForm "MainSwitch"


If Err.Number = 0 Then
MsgBox "Open Already"
Set appAccess = CreateObject("Access.Application")
Err.Clear
appAccess.Visible = True
Else
MsgBox "Not Open"
End If