Changing the SystemDB through vba

[nexes]

At my work we have a number of access db's and they all link to the
same systemdb, we have at leasst 200 computers and they are perodicly
replace and or loose the systemdb location. I have done a little bit of
research and have come to the conclusion that you cant simply point the
engine to the new workgroup file, but i was wonder if it was possible
to change the registry value of the SystemDb location and then restart
the application if that would work.

Has anyone done this??

[Rick Brandt]

nexes wrote:

At my work we have a number of access db's and they all link to the
same systemdb, we have at leasst 200 computers and they are perodicly
replace and or loose the systemdb location. I have done a little bit
of research and have come to the conclusion that you cant simply
point the engine to the new workgroup file, but i was wonder if it
was possible to change the registry value of the SystemDb location
and then restart the application if that would work.

Has anyone done this??

There is no link between a secure MDB and any particular workgroup file.

A workgroup (MDW) contains accounts, groups, and information about which
accounts belongs to which groups. This information is made part of the
Access "session" when Access is opened.

The MDB contains information about what acounts and/or groups are allowed to
do things within the application.

The information in the Access session is compared to the information in the
MDB and you are either allowed in or not. At this point the MDB does not
care what actual MDW file provided the account information in the session.
All it cares about is whether that data matches up with account permissions
stored in the file.

While it is common for exactly one MDW file to contain account data that
would allow you to open a particular MDB this does not represent a link of
any kind. If you wanted to you could create hundreds of different MDW files
that would all work for a given MDB and you can have any number of MDBs that
could be accessed with any one MDW file.

Normally if there IS only one MDW that allows access to a particular MDB and
that MDW file is lost then you should NOT be able to simply "point at
another MDW" file to gain access. If you have been able to do this it
strongly suggests that the security on your files was never set up correctly
in the first place.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[nexes]

Rick Brandt wrote:

nexes wrote:

At my work we have a number of access db's and they all link to the
same systemdb, we have at leasst 200 computers and they are perodicly
replace and or loose the systemdb location. I have done a little bit
of research and have come to the conclusion that you cant simply
point the engine to the new workgroup file, but i was wonder if it
was possible to change the registry value of the SystemDb location
and then restart the application if that would work.

Has anyone done this??

There is no link between a secure MDB and any particular workgroup file.

A workgroup (MDW) contains accounts, groups, and information about which
accounts belongs to which groups. This information is made part of the
Access "session" when Access is opened.

The MDB contains information about what acounts and/or groups are allowed to
do things within the application.

The information in the Access session is compared to the information in the
MDB and you are either allowed in or not. At this point the MDB does not
care what actual MDW file provided the account information in the session.
All it cares about is whether that data matches up with account permissions
stored in the file.

While it is common for exactly one MDW file to contain account data that
would allow you to open a particular MDB this does not represent a link of
any kind. If you wanted to you could create hundreds of different MDW files
that would all work for a given MDB and you can have any number of MDBs that
could be accessed with any one MDW file.

Normally if there IS only one MDW that allows access to a particular MDB and
that MDW file is lost then you should NOT be able to simply "point at
another MDW" file to gain access. If you have been able to do this it
strongly suggests that the security on your files was never set up correctly
in the first place.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I thank you for ur reply but that wasnt what i was trying to ask i
realise that you can have multiple associations to a mdw file however
you can only be joinned to 1 mdw at a time, would I would like to do is
set the join option for which mdw file is ussed through the registry

[Rick Brandt]

nexes wrote:

I thank you for ur reply but that wasnt what i was trying to ask i
realise that you can have multiple associations to a mdw file however
you can only be joinned to 1 mdw at a time, would I would like to do
is set the join option for which mdw file is ussed through the
registry

Well if you are okay with the fact that the change would only take affect on
the NEXT start of Access than I suppose you can do it. I found several
places in the registry that seem to be referencing this value though and
have no idea which one(s) would need to be changed.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[nexes]

Just figured Id let all know that it does work.
Here's the code

Place the Following in its own module
Option Compare Database
Option Explicit

Global Const REG_SZ As Long = 1
Global Const REG_DWORD As Long = 4

Global Const HKEY_CLASSES_ROOT = &H80000000
Global Const HKEY_CURRENT_USER = &H80000001
Global Const HKEY_LOCAL_MACHINE = &H80000002
Global Const HKEY_USERS = &H80000003

Global Const ERROR_NONE = 0
Global Const ERROR_BADDB = 1
Global Const ERROR_BADKEY = 2
Global Const ERROR_CANTOPEN = 3
Global Const ERROR_CANTREAD = 4
Global Const ERROR_CANTWRITE = 5
Global Const ERROR_OUTOFMEMORY = 6
Global Const ERROR_INVALID_PARAMETER = 7
Global Const ERROR_ACCESS_DENIED = 8
Global Const ERROR_INVALID_PARAMETERS = 87
Global Const ERROR_NO_MORE_ITEMS = 259

Global Const KEY_ALL_ACCESS = &H3F

Global Const REG_OPTION_NON_VOLATILE = 0

Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As
Long
Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias
"RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long,
ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult
As Long, lpdwDisposition As Long) As Long
Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA"
(ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long,
ByVal samDesired As Long, phkResult As Long) As Long
Declare Function RegQueryValueExString Lib "advapi32.dll" Alias
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal lpReserved As Long, lpType As Long, ByVal lpData As String,
lpcbData As Long) As Long
Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal lpReserved As Long, lpType As Long, lpData As Long, lpcbData As
Long) As Long
Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal lpReserved As Long, lpType As Long, ByVal lpData As Long,
lpcbData As Long) As Long
Declare Function RegSetValueExString Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As String,
ByVal cbData As Long) As Long
Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal
cbData As Long) As Long
Private Declare Function RegDeleteKey& Lib "advapi32.dll" Alias
"RegDeleteKeyA" (ByVal hKey As Long, ByVal lpSubKey As String)
Private Declare Function RegDeleteValue& Lib "advapi32.dll" Alias
"RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String)
Public Function SetValueEx(ByVal hKey As Long, sValueName As String,
lType As Long, vValue As Variant) As Long
Dim lValue As Long
Dim sValue As String

Select Case lType
Case REG_SZ
sValue = vValue
SetValueEx = RegSetValueExString(hKey, sValueName, 0&,
lType, sValue, Len(sValue))
Case REG_DWORD
lValue = vValue
SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, lType,
lValue, 4)
End Select

End Function
Public Function QueryValue(lPredefinedKey As Long, sKeyName As String,
sValueName As String)
' Description:
' This Function will return the data field of a value
'
' Syntax:
' Variable = QueryValue(Location, KeyName, ValueName)
'
' Location must equal HKEY_CLASSES_ROOT, HKEY_CURRENT_USER,
HKEY_lOCAL_MACHINE
' , HKEY_USERS
'
' KeyName is the key that the value is under (example:
"Software\Microsoft\Windows\CurrentVersion\Explore r")
'
' ValueName is the name of the value you want to access (example:
"link")

Dim lRetVal As Long 'result of the API functions
Dim hKey As Long 'handle of opened key
Dim vValue As Variant 'setting of queried value
lRetVal = RegOpenKeyEx(lPredefinedKey, sKeyName, 0,
KEY_ALL_ACCESS, hKey)
lRetVal = QueryValueEx(hKey, sValueName, vValue)
'MsgBox vValue
QueryValue = vValue
RegCloseKey (hKey)
End Function

Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As String,
vValue As Variant) As Long
Dim cch As Long
Dim lrc As Long
Dim lType As Long
Dim lValue As Long
Dim sValue As String

On Error GoTo QueryValueExError

' Determine the size and type of data to be read

lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
If lrc <ERROR_NONE Then Error 5

Select Case lType
' For strings
Case REG_SZ:
sValue = String(cch, 0)
lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType,
sValue, cch)
If lrc = ERROR_NONE Then
vValue = Left$(sValue, cch)
Else
vValue = Empty
End If

' For DWORDS
Case REG_DWORD:
lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType,
lValue, cch)
If lrc = ERROR_NONE Then vValue = lValue
Case Else
'all other data types not supported
lrc = -1
End Select

QueryValueExExit:

QueryValueEx = lrc
Exit Function

QueryValueExError:

Resume QueryValueExExit

End Function
Public Function SetKeyValue(lPredefinedKey As Long, sKeyName As String,
sValueName As String, vValueSetting As Variant, lValueType As Long)
' Description:
' This Function will set the data field of a value
'
' Syntax:
' QueryValue Location, KeyName, ValueName, ValueSetting, ValueType
'
' Location must equal HKEY_CLASSES_ROOT, HKEY_CURRENT_USER,
HKEY_lOCAL_MACHINE
' , HKEY_USERS
'
' KeyName is the key that the value is under (example:
"Key1\SubKey1")
'
' ValueName is the name of the value you want create, or set the
value of (example: "ValueTest")
'
' ValueSetting is what you want the value to equal
'
' ValueType must equal either REG_SZ (a string) Or REG_DWORD (an
integer)

Dim lRetVal As Long 'result of the SetValueEx function
Dim hKey As Long 'handle of open key

'open the specified key

lRetVal = RegOpenKeyEx(lPredefinedKey, sKeyName, 0,
KEY_ALL_ACCESS, hKey)
lRetVal = SetValueEx(hKey, sValueName, lValueType,
vValueSetting)
RegCloseKey (hKey)

End Function

Add this to the program's startup
If Application.SysCmd(acSysCmdGetWorkgroupFile) <"PATH\TO\MDW" Then
'For 2000 note the user must have administrative rights
IF SysCmd(acSysCmdAccessVer) = 9
SetKeyValue HkEY_LOCAL_MACHINE,
"SoftwareSoftware\Microsoft\Office\9.0\Access\Jet\ 4.0\Engines",
"SystemDB", "PATH\TO\MDW", REG_SZ

'For 2003
ElseIf SysCmd(acSysCmdAccessVer) = 11
SetKeyValue HKEY_CURRENT_USER,
"Software\Microsoft\Office\11.0\Access\Jet\4.0\Eng ines", "SystemDB",
"PATH\TO\MDW", REG_SZ

End If
Msgbox "The workgroup has been change the app will restart"
Application.Quit
End IF

[John Mishefske]

nexes wrote:

At my work we have a number of access db's and they all link to the
same systemdb, we have at leasst 200 computers and they are perodicly
replace and or loose the systemdb location. I have done a little bit of
research and have come to the conclusion that you cant simply point the
engine to the new workgroup file, but i was wonder if it was possible
to change the registry value of the SystemDb location and then restart
the application if that would work.

Something that may merit a look is the undocumented PrivDBEngine (hidden class in the DAO
library - use F2 to browse objects, right-click and set the Show Hidden Members.

Of course, undocumented means this is not suitable for critical project as support may not
continue in future Access versions. So..consider the pros/cons.

http://support.microsoft.com/kb/888634/en-us

has a sample. Past versions of the Jet Database Engine Programmer's Guide had examples of
how to do this. Code examples are likely available through Google.

--
'---------------
'John Mishefske
'---------------