how to open another database using VBA (1 Viewer)

[jaydwest]

Change is tough. I have some old databases that use the following code:

SendKeys "%FO" & strDBPathName & "~", False

This is real old code so only old timers will recognize it. But I want to open a new Access database from the current Access database and close the current database.

What is the new code to do this.

Thanks for your help

 

[pbaldy]

Try Shell or FollowHyperlink.

 

[jaydwest]

Thanks for your suggestions. Have tried them. THey all seem to work but get hung up on the new MS Access security stuff.

THe SendKeys actually does work but I get the message "Permission Denied." The followHyperlink gets locked up.

SO I guess the new question is - Is there any way to bypass the new Warning messages that MS Access now uses?

 

[jaydwest]

OpenDB Function

KEYWORDS: Open Database, Open FIle

After further reseach I came up with the following function that uses the Shell Command and does work.

'------ JW070610(B1)
Public Function OpenDB(pasDBPathName As String, pasCloseCurrent As String) As String
On Error GoTo Err_Proc

'-- Test Required Data
If Ns(pasDBPathName, "None") = "None" Then
myM = "DATABASE PATH NAME NOT FOUND. "
Alert
OpenDB = "ERROR"
Exit Function
End If

If Not FileExists(pasDBPathName) Then
myM = "DATABASE WAS NOT FOUND IN THE FOLDER SHOWN (" & pasDBPathName & "). "
Alert
OpenDB = "ERROR"
Exit Function
End If

Call Shell("msaccess.exe " & pasDBPathName, vbMaximizedFocus)
DoEvents
Select Case pasCloseCurrent
Case "Close", "CloseCurrent"
DoCmd.Quit
End Select

Exit Function

Err_Proc:
msg Error
Exit Function

End Function

--------------------------------------------
I would appreciate it if anyone has a better way to do this (Like Using MS Access Objects instead of the Shell Command)

Also would like to know if there is a solution to opening another database in code and bypassing the security warning and logon forms. In the case of the logon Popup, the SendKeys method did bypass it and after all you have already logged into the current dbs.

Thanks for all your help

 

[chergh]

Try this:

dim accapp as access.application

set accapp = new access.application

accapp.opencurrentdatabase("c:\whatever\blah.mdb")
accapp.visible = true

 

[jaydwest]

I already tried using an Access Application, but it locks up the current Access Database when the Logon Dialog Box pops up. It is modal and prevents closing the current db or doing anything with it. Had to use Task Manager to shut it down.

 

[boblarson]

Also would like to know if there is a solution to opening another database in code and bypassing the security warning...

The only way to get past this is to either

1. Set the user's Macro Security Level to LOW (not overly optimal nor allowed by many IT Departments with good reason)

2. Digitally sign the file, but to really do so requires some third-party certificate (although I THINK you can create your own which will work on your own machine and it may work on someone else's if they manually install it).

 

[jaydwest]

Bob,

Thanks for your response. Do your suggestions apply to using an Access Application or to the Shell Function?

Or were you referring to the Security Warning and Logon Popup issues?

Thanks.

 

[KingRudeDog]

Bob,

Thanks for your response. Do your suggestions apply to using an Access Application or to the Shell Function?

Or were you referring to the Security Warning and Logon Popup issues?

Thanks.

I know this may sound silly, after all I am more of a novice programmer than all of you. But...I picked up this much simpler code on this forum from a Windows OpenFile dialogue and pirated this portion of the code. Im not sure if it will work on the older databases or not. But it works on the new.

On Event:

Me.tbHidden.SetFocus
'sets focus to a hidden textbox (tbHidden)

If IsNull (txtbox) Or txtbox = "" Then
MsgBox "Please enter a valid path."
Else OpenFile (txtbox)
End If


I actually do open another access database with this one (from inside an access db)

 

[boblarson]

Or were you referring to the Security Warning and Logon Popup issues?

I was referring to the security warning and logon popup issues.

 

[jim2710]

Try this:

dim accapp as access.application
 
set accapp = new access.application
 
accapp.opencurrentdatabase("c:\whatever\blah.mdb")
accapp.visible = true

This works perfectly in Access 2010. Thanks!

 

[mramey64]

Try this:

dim accapp as access.application
 
set accapp = new access.application
 
accapp.opencurrentdatabase("c:\whatever\blah.mdb")
accapp.visible = true

This not only worked on my Access 2010 database but it bypassed the security screen. kudos!

Mike

 

[StuartP]

when I tried this code in Access 2007. it tried to open itself and gave me a debug message

Private Sub Command21_Click()
Dim accapp As Access.Application
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("C:\GQReporting\GQReporting.accdb")
accapp.Visible = True
End Sub


Debug message:

Microsoft Visual Basic

Run Time Error '7867"


Any suggestions

You already have the database open.

 

[StuartP]

ok answering my own request now.

went the shell route.

created a button with on click event "command21_click"

_______________________________________
Private Sub Command21_Click()
Call OpenDBRep
End Sub
_____________________________________________
and Public Function to open database


_____________________________________________
Public Function OpenDBRep()
Call Shell("msaccess.exe " & ("C:\GQReporting\GQReporting.accdb"), vbMaximizedFocus)
DoEvents
End Function
_________________________________________________

this opened the reports database maximised , and left the original database open and enabled (ie I could enter data with the reports database left open) ..just what I wanted

 

[Thales750]

Many of you youngsters out there may not know about this little trick.
Being a dinosaur hunter myself I decided to use a dino DOS tool.

Private Sub Command60_Click()
Shell "C:\Data\HCDC\HCDCstart.bat "
End Sub

Where
"C:\Data\HCDC\HCDCstart.bat "

Is a special text file known as a batch file, back in the Dino hunting days of DOS we used it to do all kinds of cool administration functions.
Try searching for DOS Batch file if you want to learn more about this powerful little tool.

All there is in this text file is:

start msaccess C:\Data\HCDC\HCDCServer.accdb

So, in more words than it took to do it; I have described it for you.

 

[Muaz]

Hi;

I have tried all the above mentioned solution to open another secured data base through VBA. The one that is working in my Laptop only is

Application.FollowHyperlink "C:\Users\Qazi\Desktop\hrmst.lnk"

After processing this code the system ask for logon screen of the secured database which is correct.

but when I am using the same code on other system it is not working. I spend more than 3 days by using different options but all in vain. Both the systems using MS Access 2007.

Any suggestion will be highly appreciated.

Regards
Muaz

 

[Minty]

Have you tried

Dim appAcc As Access.Application
    
Set appAcc = New Access.Application

appAcc.Visible = True
appAcc.OpenCurrentDatabase ("Yourdatabase.mbd"e)
appAcc.UserControl = True
appAcc.RunCommand acCmdAppMaximize
appAcc = Nothing

Setting your application to run a link on your desktop is really unreliable.

 

[pbaldy]

This isn't a path to an Access db, it's a path to a shortcut (I think):

Application.FollowHyperlink "C:\Users\Qazi\Desktop\hrmst.lnk"

 

[Muaz]

Thanks for your prompt reply.

Yes, I use this but not working. The reason to run link is because the database is secured with .mdw If I run the mdb then the system give message "you don't have necessary permission to use ........." because the database is secured.

Also used below mention code but not succeeded

Private Sub cmdWrkOpen_Click() ' Open a workgroup-secured database. ' Open a database. Const ACCESSEXE = "msaccess.exe" Const FILENAME = "C:\data\Northwind_be.mdb" Const SECUREWRK = "C:\developer.mdw" Const SECUREUSER = "Developer" Const SECUREPWD = "Developer" Dim strFilePath As String, varAppID As Variant, strShell As String On Error GoTo err_cmdWrkOpen strFilePath = SysCmd(acSysCmdAccessDir) If Len(Dir(FILENAME)) > 0 And Len(Dir(SECUREWRK)) > 0 Then ' Open the database while using Chr(34) to add inverted commas. strShell = strFilePath & ACCESSEXE & " " & FILENAME & _ "/WRKGRP " & Chr(34) & SECUREWRK & Chr(34) & _ "/USER " & Chr(34) & SECUREUSER & Chr(34) & _ "/PWD " & Chr(34) & SECUREPWD & Chr(34) varAppID = Shell(strShell, vbNormalFocus) Else MsgBox "Problem Opening Your Application. Contact Your DBA", vbCritical, _ "Database Is Out of Action" End If ' Reinstate the following line once you have finished testing. ' DoCmd.Quit acQuitSaveAll exit_cmdWrkOpen: Exit Sub err_cmdWrkOpen: Select Case Err.Number Case Else MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical End Select Resume exit_cmdWrkOpen End Sub

I don't understand why, the below mention code is working in my system but not on other systems

Application.FollowHyperlink "C:\Users\Qazi\Desktop\hrmst.lnk

 

[Muaz]

Yes, Pbaldy.. It is link of the db.