Access to open Access Database and close this database from where I executed the code

[pedie]

Hi is there a way for Access vba to open another Access Database and close this database from where I executed the code?
And if yes how can i make sure that the new database opened in macro enabled?

Thanks alot for helping...
Regards
Pedie

 

[boblarson]

Yes, you can do this. As for macros enabled you would need to ensure that the database is in a trusted location.

The code to do it would be something like this:

Function OpenAnotherDb(strFileAndPath As String)
Dim objAcc As Object
 
Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase(strFileAndPath)
 
objAcc.Visible = True
objAcc.UserControl = True
Set objAcc = Nothing
 
Application.Quit
End Function

Then you would put that into a standard module and then call it from wherever you needed to like:

OpenAnotherDb "C:\MyFiles\SomeDatabaseName.accdb"

Does that make sense?

 

[pedie]

I dont know how to call this function again....
and my database is password protected "TempPwd123"
Thanks again Bob


In standard module

[/FONT]
[FONT=Courier New]Function OpenAnotherDb(strFileAndPath As String)
Dim objAcc As Object
Dim OpenAnotherDb As String[/FONT]
[FONT=Courier New]OpenAnotherDb = "C:\Users\Pediez\Desktop\Excerise DBs\TEST.accdb"
Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase (strFileAndPath)
 
objAcc.Visible = True
objAcc.UserControl = True
Set objAcc = Nothing[/FONT]
[FONT=Courier New]Application.Quit
End Function
Sub testCall()
OpenAnotherDb
End Sub

 

[xenou]

You lost the file path in your changes. It is like this:

Function OpenAnotherDb([COLOR="RoyalBlue"]strFileAndPath [/COLOR]As String)
Dim objAcc As Object

Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase ([COLOR="RoyalBlue"]strFileAndPath[/COLOR])
 
objAcc.Visible = True
objAcc.UserControl = True
Set objAcc = Nothing
Application.Quit
End Function

Sub testCall()
Dim [COLOR="RoyalBlue"]strFileAndPath[/COLOR]
[COLOR="RoyalBlue"]strFileAndPath [/COLOR]= "C:\Users\Pediez\Desktop\Excerise DBs\TEST.accdb"
Call OpenAnotherDb([COLOR="RoyalBlue"]strFileAndPath[/COLOR])
End Sub

 

[boblarson]

[FONT=Courier New]Function OpenAnotherDb(strFileAndPath As String)[/FONT]
[FONT=Courier New]Dim objAcc As Object[/FONT]
[FONT=Courier New]Dim OpenAnotherDb As String[/FONT]
[FONT=Courier New][COLOR=red][B]OpenAnotherDb = "C:\Users\Pediez\Desktop\Excerise DBs\TEST.accdb"[/B][/COLOR][/FONT]
[FONT=Courier New]Set objAcc = CreateObject("Access.Application")[/FONT]
 
[FONT=Courier New]objAcc.OpenCurrentDatabase (strFileAndPath)[/FONT]
 
[FONT=Courier New]objAcc.Visible = True[/FONT]
[FONT=Courier New]objAcc.UserControl = True[/FONT]
[FONT=Courier New]Set objAcc = Nothing[/FONT]
[FONT=Courier New]Application.Quit[/FONT]
[FONT=Courier New]End Function[/FONT]
[FONT=Courier New]Sub testCall()[/FONT]
[FONT=Courier New]OpenAnotherDb[/FONT]
[FONT=Courier New]End Sub[/FONT]

For one you don't call this function from inside itself (see red part above and remove it). You would call it from wherever you are wanting to initiate this opening of the database.

As for opening a password protected database you would change the function to this:

Function OpenAnotherDb(strFileAndPath As String, Optional strDbPWD As String)
    Dim objAcc As Object
 
    Set objAcc = CreateObject("Access.Application")
 
    If Len(strDbPWD) > 0 Then
        objAcc.OpenCurrentDatabase strFileAndPath, , strDbPWD
    Else
        objAcc.OpenCurrentDatabase strFileAndPath
    End If
 
    objAcc.Visible = True
    objAcc.UserControl = True
 
    Set objAcc = Nothing
 
    Application.Quit
End Function

And then you would, from the event you want to open the other database from - for example the click event of a button, you would call it:

OpenAnotherDb "C:\Users\Pediez\Desktop\Excerise DBs\TEST.accdb", "TempPwd123"

 

[pedie]

Bob, thanks again for helping out....works perfect now...