I am trying to open a form in another db (secured with MDW file) from the current db. I managed to do this for an unsecured db with a bit of help from this group. (thx everyone). The code is similar, the only difference is using the shell command (to send the user, pwd and workgroup info) recommended on the micorsoft website.
I find that the getObject() method does not want to work in Access (I understand that the new window must lose focus for the application object to be registered)...still...I replaced this line with Set accObj=New access.application. This way the access window opens however I run into an error trying to open the form.
The error is 2486 - You cannot carry out this action at the present time.
Here is the code...
Public Function openSecuredDBwAutomation()
Dim accObj As Access.application, Msg As String
Dim application As String, dbs As String, workgroup As String
Dim User As String, password As String
Dim strPathToFile As String, strDefaultUser As String 'these will be input params eventually
Dim strDefaultPwd As String, strPathToDatabase As String '...input params later
'this will be removed once these will become input params
strPathToFile = ....'the mdw file
strDefaultUser = .....'the user-- should have Admin priviliges to get access to forms/report
strDefaultPwd = .....'pwd
strPathToDatabase = ....'location of the secured db
' assign to local params
application = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
dbs = strPathToDatabase ' path and name of a secured MDB
workgroup = strPathToFile ' the location of the mdw file
User = strDefaultUser ' username
password = strDefaultPwd ' password
'open applciation with workgoup, user info and pwd
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & User & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMinimizedFocus)
'Set accObj = getObject("", "Access.Application") 'this line does not work
Set accObj = New Access.application 'had to resort to this instead
accObj.DoCmd.OpenForm "Form1", acDesign 'open form
MsgBox accObj.Forms("Form1").RecordSource 'return record source
accObj.CloseCurrentDatabase 'close db
Set accObj = Nothing
The purpose of this function is to return the recordsource of the selected object (form or report) as a string. The inputs are listed above. For simplicity I made them all local for now. The messagebox line will be assigning the recordsource to the function in the end to return the SQL as string.
I am not sure why Access breaks down here, the only thing I can think is that maybe the original database opening the secured one is by default belongs to the default security group, so even though the secured database is open, but its objects are not available to view or manipulate. The other issue maybe that most secured DBs hide their database windows, and it may need to be unhidden before accessing any objects. I tried this but this time Access broke down on this line instead. The error msg here is:
2046 - The command or action 'WindowUnhide' isn't available now.