ACCESS 2003 - Opening data base with rights

[Noel]

Hello,
Could you help me for that :
Context : I want to open an ACCESS (2003/XP) .mdb data base, under the
control of a rights setting file .mdw.
Problen : rights apply to the data base within ACCESS it-self. So, I need to :
- Open ACCESS ;
- Apply rights ;
- Load data base.
Within VBA EXCEL or OUTLOOK code, I code :

Private AAASub()
Dim AccessApplication As Access.Application
Shell "MSAccess.exe", vbNormalFocus
Set AccessApplication = GetObject(, "Access.Application")
AccessApplication.SetDefaultWorkgroupFile "C:\APath\ARightsDefinitionFile.mdw"
AccessApplication.OpenCurrentDatabase "C:\AnOtherPath\ADataBaseFile.mdb"

'Here the user is doing what he as to do, then closes ACCESS

AccessApplication.SetDefaultWorkgroupFile "C:\Docu...Soft\Access\System.mdw"
AccessApplication.Quit
Let AccessApplication = Nothing
MsgBox "Bye"
End Sub

This is running allright when there is not any else instance of ACCESS
loaded before. If an instance of ACCESS was loaded before (or several),
GetObject get always the last loaded before, which is not the one I hope, and
the code goes until the end without any stop.

Is there a method to associate the ACCESS loaded by Shell and the ACCESS
object ?
Ali and Klatuu deals about something similar between 5/23/2008 and
5/29/2008, but the thread stopped before solution ...
Thanks a lot,
Noel

 

[Wolfgang Kais]

Hello Noel.

Hello,
Could you help me for that :
Context : I want to open an ACCESS (2003/XP) .mdb data base, under
the control of a rights setting file .mdw.
Problen : rights apply to the data base within ACCESS it-self. So,
I need to :
- Open ACCESS ;
- Apply rights ;
- Load data base.
Within VBA EXCEL or OUTLOOK code, I code :

The rights are stored in the database itself, but only the users of
the current mdw can try to log in.

[snipped]
This is running allright when there is not any else instance of
ACCESS loaded before. If an instance of ACCESS was loaded before
(or several), GetObject get always the last loaded before, which
is not the one I hope, and the code goes until the end without
any stop.

Is there a method to associate the ACCESS loaded by Shell and the
ACCESS object ?

Don't use shell. Try this
Set AccessApplication = New Access.Application
AccessApplication.Visible = True

 

[Noel]

Hello,
Thank you for your quick answer. I had never use Set New. It works now
nearly good, but I have a problem to stop running. See code below :

Public Function OpenProtectedDataBase( _
ByVal AZéroValue As Byte) As Boolean

'Change from yesterday : initialization from an other Access data
'base with an AutoExec macro inside (more <<elegant>> than from EXCEL
'or OutLook for an Access application)

Dim AppAccess_1 As Access.Application
Set AppAccess_1 = Application

Dim AppAccess_2 As Access.Application
Set AppAccess_2 = New Access.Application 'Your suggestion
AppAccess_2.Visible = True

Call ShowWindow(AppAccess_2.hWndAccessApp, 9)
'Add to activate the above new window, with the idea to hide the
'AppAccess_1 window later with the instruction :
' Call ShowWindow(AppAccess_1.hWndAccessApp, 0

AppAccess_2.SetDefaultWorkgroupFile "C:\APath\ARightsDefinitionFile.mdw"
AppAccess_2.OpenCurrentDatabase "C:\AnOtherPath\ADataBaseFile.mdb"

'Works OK ... but stops sometimes yes, sometimes not ...
'More precisely, stops allright first time after recompliling,
'do not stop then after

MsgBox "Bye"

AppAccess_2.Quit
Set AppAccess_2 = Nothing

AppAccess_1.SetDefaultWorkgroupFile "C:\Docu...Soft\Access\System.mdw"

Let OpenProtectedDataBase = True

AppAccess_1.Quit
Set AppAccess_1 = Nothing

End Function

Any idea ?
Thanks,

Noel

Hello Noel.

Hello,
Could you help me for that :
Context : I want to open an ACCESS (2003/XP) .mdb data base, under
the control of a rights setting file .mdw.
Problen : rights apply to the data base within ACCESS it-self. So,
I need to :
- Open ACCESS ;
- Apply rights ;
- Load data base.
Within VBA EXCEL or OUTLOOK code, I code :

The rights are stored in the database itself, but only the users of
the current mdw can try to log in.

[snipped]
This is running allright when there is not any else instance of
ACCESS loaded before. If an instance of ACCESS was loaded before
(or several), GetObject get always the last loaded before, which
is not the one I hope, and the code goes until the end without
any stop.

Is there a method to associate the ACCESS loaded by Shell and the
ACCESS object ?

Don't use shell. Try this
Set AccessApplication = New Access.Application
AccessApplication.Visible = True

 

[Wolfgang Kais]

Hello Noel.

Hello,
Thank you for your quick answer. I had never use Set New. It works now
nearly good, but I have a problem to stop running. See code below :

Public Function OpenProtectedDataBase( _
ByVal AZéroValue As Byte) As Boolean

'Change from yesterday : initialization from an other Access data
'base with an AutoExec macro inside (more <<elegant>> than from EXCEL
'or OutLook for an Access application)

Dim AppAccess_1 As Access.Application
Set AppAccess_1 = Application

Dim AppAccess_2 As Access.Application
Set AppAccess_2 = New Access.Application 'Your suggestion
AppAccess_2.Visible = True

Call ShowWindow(AppAccess_2.hWndAccessApp, 9)
'Add to activate the above new window, with the idea to hide the
'AppAccess_1 window later with the instruction :
' Call ShowWindow(AppAccess_1.hWndAccessApp, 0

AppAccess_2.SetDefaultWorkgroupFile "C:\APath\ARightsDefinitionFile.mdw"
AppAccess_2.OpenCurrentDatabase "C:\AnOtherPath\ADataBaseFile.mdb"

'Works OK ... but stops sometimes yes, sometimes not ...
'More precisely, stops allright first time after recompliling,
'do not stop then after

MsgBox "Bye"

AppAccess_2.Quit
Set AppAccess_2 = Nothing

AppAccess_1.SetDefaultWorkgroupFile "C:\Docu...Soft\Access\System.mdw"

Let OpenProtectedDataBase = True

AppAccess_1.Quit
Set AppAccess_1 = Nothing

End Function

Any idea ?

What means "stop running"? You said that this Function is called in the
AutoExec macro of your datanase, so this will be executed when you open
that database, unless security is set to high and the code is disabled.
This function will open and close the secured database in an other
instance of Access (AppAccess_2) and then also quit the current Access
application (AppAccess_1). If you want to execute more, just don't
quit AppAccess_1.
btw: you don't have to use the variable AppAccess_1, you can reference
the running instance by the Application object (Application.Set...).
hth.

 

[Noel]

Hello,
Thanks again for your answer.
What I mean by "stop running" is how users could work in the secured base if
AppAccess_2 does not stop for them to work ? And, in fact, it stops the first
time.
The problem is I do not want force users to close without rights instance(s)
of Access already opened in their work space. It is why I try to open a new
instance, with rights, for this particular application.
OK for AppAccess_1, used only for "symetrical" reasons !
Noel

 

[Noel]

Hello,
What do you mean by "Click to show or hide original message or reply text" ?
I suppose it is relative to posts and not to ACCESS ?

 

[Wolfgang Kais]

Hello Noel.

Hello,
Thanks again for your answer.
What I mean by "stop running" is how users could work in the secured
base if AppAccess_2 does not stop for them to work ? And, in fact,
it stops the first time.

AppAccess_2.Quit makes it stop.

The problem is I do not want force users to close without rights
instance(s) of Access already opened in their work space. It is why
I try to open a new instance, with rights, for this particular
application.
OK for AppAccess_1, used only for "symetrical" reasons !

I think that you want Access_2 keep running while Access_1 stops.
Try to not Quit Access_2.