getObject() and shell automation question

[juneBug via AccessMonster.com]

Hello.

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.
See: http://support.microsoft.com/default.aspx?scid=kb;en-us;192919

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
accObj.Quit
Set accObj = Nothing
Exit Function
End Function

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.

many thanks.
juneBug

 

[Douglas J Steele]

Try GetObject(, "Access.Application")

 

[juneBug via AccessMonster.com]

Doug.

Thanks for replying...I am still locking horns with VBA over this...
Here is the most recent attempt...

Public Function openSecuredDBwAutomation()
Dim accObj As Access.application, myDB As Database, myWS As Workspace,
dbe As PrivDBEngine
Dim strPathToMDWFile As String, strDefaultUser As String 'will be
input param
Dim strDefaultPwd As String, strPathToDatabase As String 'will be
input param

'WILL BE INPUT PARAMS

'strPathToMDWFile = ""
'strDefaultUser = ""
'strDefaultPwd = ""
'strDefaultPwd = "ratesrus"
'strPathToDatabase = "C:\WINNT\Profiles\CTCUSER\Desktop\tester.mdb"

'OPEN WORKSPACE
'protected by MDW
'If strPathToMDWFile <> "" And strDefaultUser <> "" And strDefaultPwd <>
"" Then
' Set myWS = CreateWorkspace("", strDefaultUser, strDefaultPwd,
dbUseJet)
' ' Open the secured database.
' Set myDB = myWS.OpenDatabase(strPathToDatabase)
'Else
'password protection or unprotected db
Set myWS = DBEngine.Workspaces(0)
'End If

'OPEN DATABASE
'MDW protected
If strPathToMDWFile <> "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;DATABASE=" &
strPathToMDWFile & ";UID=" & strDefaultUser & ";PWD=" & strDefaultPwd)
'password protected
ElseIf strDefaultPwd <> "" And strPathToMDWFile = "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;PWD=" & strDefaultPwd)
Else 'unprotected
Set myDB = myWS.OpenDatabase(strPathToDatabase)
End If

'GET ACCESS OBJECT
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set accObj = getObject(strPathToDatabase, "Access.Application")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'GET RECORDSOURCE
accObj.DoCmd.OpenForm "Form1", acDesign 'open form
MsgBox accObj.Forms("Form1").RecordSource

'accObj.CloseCurrentDatabase
'accObj.Quit

Set accObj = Nothing
Exit Function
End Function

 

[juneBug via AccessMonster.com]

Doug.

Thanks for replying...I am still locking horns with VBA over this...
Here is the most recent attempt...

Public Function openSecuredDBwAutomation()
Dim accObj As Access.application, myDB As Database, myWS As Workspace,
dbe As PrivDBEngine
Dim strPathToMDWFile As String, strDefaultUser As String 'will be
input param
Dim strDefaultPwd As String, strPathToDatabase As String 'will be
input param

'WILL BE INPUT PARAMS

'strPathToMDWFile = ""
'strDefaultUser = ""
'strDefaultPwd = ""
'strDefaultPwd = "ratesrus"
'strPathToDatabase = "C:\WINNT\Profiles\CTCUSER\Desktop\tester.mdb"

'OPEN WORKSPACE
'protected by MDW
'If strPathToMDWFile <> "" And strDefaultUser <> "" And strDefaultPwd <>
"" Then
' Set myWS = CreateWorkspace("", strDefaultUser, strDefaultPwd,
dbUseJet)
' ' Open the secured database.
' Set myDB = myWS.OpenDatabase(strPathToDatabase)
'Else
'password protection or unprotected db
Set myWS = DBEngine.Workspaces(0)
'End If

'OPEN DATABASE
'MDW protected
If strPathToMDWFile <> "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;DATABASE=" &
strPathToMDWFile & ";UID=" & strDefaultUser & ";PWD=" & strDefaultPwd)
'password protected
ElseIf strDefaultPwd <> "" And strPathToMDWFile = "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;PWD=" & strDefaultPwd)
Else 'unprotected
Set myDB = myWS.OpenDatabase(strPathToDatabase)
End If

'GET ACCESS OBJECT
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set accObj = getObject(strPathToDatabase, "Access.Application")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'GET RECORDSOURCE
accObj.DoCmd.OpenForm "Form1", acDesign 'open form
MsgBox accObj.Forms("Form1").RecordSource

'accObj.CloseCurrentDatabase
'accObj.Quit

Set accObj = Nothing
Exit Function
End Function

 

[juneBug via AccessMonster.com]

oops. I messed up ....sorry....I somehow ended up posting something 2x that I
wasn't done editing...
here is what I meant to say...

This particular piece will work for an unprotected DB, but when trying to run
a password protected db it still asks for pwd. I am not sure if creating
another workspace given the pwd and user id and the mdw location would help
once creating the access object.
And in case of the MDW protected db, it won't create the workspace. I got
passed this a couple of times and then the getObject() broke down.

'*********************************************************
'start code

'*********************************************************
Public Function openSecuredDBwAutomation()
Dim accObj As Access.application, myDB As Database, myWS As Workspace,
dbe As PrivDBEngine
Dim strPathToMDWFile As String, strDefaultUser As String 'will be
input param
Dim strDefaultPwd As String, strPathToDatabase As String 'will be
input param

'WILL BE INPUT PARAMS
'strPathToMDWFile = "" 'path to mdw file
'strDefaultUser = "" ]your id
'strDefaultPwd = "" 'your pwd
'strPathToDatabase = "C:\WINNT\Profiles\CTCUSER\Desktop\tester.mdb"

'OPEN WORKSPACE
'protected by MDW
'If strPathToMDWFile <> "" And strDefaultUser <> "" And strDefaultPwd <>
"" Then
' Set myWS = CreateWorkspace("", strDefaultUser, strDefaultPwd,
dbUseJet)
' ' Open the secured database.
' Set myDB = myWS.OpenDatabase(strPathToDatabase)
'Else
'password protection or unprotected db
Set myWS = DBEngine.Workspaces(0)
'End If

'OPEN DATABASE
'MDW protected
If strPathToMDWFile <> "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;DATABASE=" &
strPathToMDWFile & ";UID=" & strDefaultUser & ";PWD=" & strDefaultPwd)
'password protected
ElseIf strDefaultPwd <> "" And strPathToMDWFile = "" Then
Set myDB = myWS.OpenDatabase _
(strPathToDatabase, False, False, "MS Access;PWD=" & strDefaultPwd)
Else 'unprotected
Set myDB = myWS.OpenDatabase(strPathToDatabase)
End If

'GET ACCESS OBJECT
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set accObj = getObject(strPathToDatabase, "Access.Application")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'GET RECORDSOURCE
accObj.DoCmd.OpenForm "Form1", acDesign 'open form
MsgBox accObj.Forms("Form1").RecordSource

'accObj.CloseCurrentDatabase
'accObj.Quit

Set accObj = Nothing
Exit Function
End Function
'*********************************************************
'end of code

'*********************************************************

 

[Douglas J. Steele]

Did you try making the change I suggested?

You may find it useful to read http://support.microsoft.com/?id=210111
(don't worry that it only mentions Access 2000: it applies to all versions)

 

[juneBug via AccessMonster.com]

Doug.

Thanks for the note, once again. I have been reading a lot on PrivDBAngine
online. I see you help out in a lot of different groups. I did try your
suggestion and saw the Microsoft page as well. I read thru it quickly, but I
will make sure I read it on my way home in more detail...

for now here is what I have been experimenting with...I am using an access
application object and trying to define the

Function XXXXXX()

Dim myDB As Database, dbe As PrivDBEngine, wrk As Workspace, mytdf As
TableDef
'Dim accObj As Object
Dim accObj As Access.application

Set dbe = New PrivDBEngine
dbe.SystemDB = 'path to MDW
dbe.DefaultUser = "dbadmin" 'user
dbe.DefaultPassword = "xxx" 'pwd

''''''''''''''''''''this will return the 1st table object's
name'''''''''''''''''''''''''''
Set wrk = dbe.Workspaces(0)
' Open the secured database.
Set myDB = wrk.OpenDatabase("path to db")
Set mytdf = myDB.TableDefs(0)
MsgBox mytdf.Name
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


''''''''''''''''''''this attempt to grab the main form's recordsource,
unsuccessfully'''''''''''''''''''''''
Set accObj = getObject(, "Access.application")
accObj.DBEngine.DefaultUser = "dbadmin"
accObj.DBEngine.DefaultPassword = "Answer$@Life"
accObj.DBEngine.SystemDB = "m:\mobility\newversion\system.mdw"
accObj.DoCmd.OpenForm "fmnuMain"
MsgBox accObj.Forms("fmnuMain").RecordSource
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set accObj = Nothing
End Function

 

[juneBug via AccessMonster.com]

Doug.

Thanks for the note, once again. I have been reading a lot on PrivDBAngine
online. I see you help out in a lot of different groups. I did try your
suggestion and saw the Microsoft page as well. I read thru it quickly, but I
will make sure I read it on my way home in more detail...

for now here is what I have been experimenting with...I am using an access
application object and trying to define the

Function XXXXXX()

Dim myDB As Database, dbe As PrivDBEngine, wrk As Workspace, mytdf As
TableDef
'Dim accObj As Object
Dim accObj As Access.application

Set dbe = New PrivDBEngine
dbe.SystemDB = 'path to MDW
dbe.DefaultUser = "dbadmin" 'user
dbe.DefaultPassword = "xxx" 'pwd

''''''''''''''''''''this will return the 1st table object's
name'''''''''''''''''''''''''''
Set wrk = dbe.Workspaces(0)
' Open the secured database.
Set myDB = wrk.OpenDatabase("path to db")
Set mytdf = myDB.TableDefs(0)
MsgBox mytdf.Name
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


''''''''''''''''''''this attempt to grab the main form's recordsource,
unsuccessfully'''''''''''''''''''''''
Set accObj = getObject(, "Access.application")
accObj.DBEngine.DefaultUser = "dbadmin"
accObj.DBEngine.DefaultPassword = "Answer$@Life"
accObj.DBEngine.SystemDB = "m:\mobility\newversion\system.mdw"
accObj.DoCmd.OpenForm "fmnuMain"
MsgBox accObj.Forms("fmnuMain").RecordSource
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set accObj = Nothing
End Function

 

[juneBug via AccessMonster.com]

gak. I made the same mistake ....the tab button gets you into trouble here...

anyhow...

defining a new access application and setting its systemdb, user, pwd later
does not help. using the accessObj.dbengine.docmd openform "" formula won't
be of help even after submitting userid etc. I will try to insert later
tonite a createWorkspace() between, maybe that will help....
Set accObj = getObject(, "Access.application")
accObj.DBEngine.DefaultUser = "dbadmin"
accObj.DBEngine.DefaultPassword = "xxx
accObj.DBEngine.SystemDB = "path to mdw"

'enter createWorkspace() here?????

accObj.DoCmd.OpenForm "fmnuMain"
MsgBox accObj.Forms("fmnuMain").RecordSource

thx
juneBug

**Beware of the killer tab button**