M
mdullni1 via AccessMonster.com
I wasn't sure where to post this. If this forum is inappropriate, please
point in the proper direction.
We currently have both MS Access XP and MS Access 2003 installed on our PCs
(XP Professional) because our mdb and adp are still XP. The shortcut to run
these databases explicitly call the XP version.
We have successfully converted our databases (they are only front-ends to SQL
Server) to 2003 in a test environment. Obviously, our goal is to eliminate
XP, but we don't want to tell services to remove it from the PCs until we
know for sure the conversion worked. Also, we distribute our DBs to other
offices where we cannot dictate removal of a version or which version is the
default version. The one thing we do know is that the users will not like
the macro security warnings in 2003, and we cannot tell other offices to set
it to low.
I have created a VBScript to open our DB by first running Access 2003 in a
shell so that it is the installed version. It then closes the application,
kills the shell, creates an instance of Access, sets its automationsecurity
to 1, and opens the DB. The problem comes in when XP was the installed
version. The script successfully installs 2003, but generates the 800A01B6
error indicating automationsecurity is not supported. If I run Detect and
Repair on 2003 and keep it installed, then my script will work because it is
not switching from XP.
Debugging: I have put in a loop to ensure 2003 gets installed before the
script continues. I have used echo to verify the version is 11. The
VBScript editor includes "automationsecurity" in its property dropdown for
the object when editing. The only thing I can surmise is that somewhere XP is
overwriting a setting that references automationsecurity for 2003.
Does anybody know of a setting or otherwise see where my problem may lie
(other than having both versions on the PC)?
My script:
' Open the MDE in Access 2003
Option Explicit
Dim PathToMDE
Dim acPath
Dim acApp
Dim WshShell
Dim objFSO
Dim LoopCnt
dim VerNum
PathToMDE = "c:\AccessDB\DB_2003.mde"
acPath = "C:\Program Files\Microsoft Office\Office11"
'Ensure MSAccess Office 2003 is default app
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(acPath) Then
Set objFSO = Nothing
Set WshShell = WScript.CreateObject("WScript.Shell")
With WshShell
.Run """C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"""
Do While verNum < 11
Set acApp=CreateObject("Access.Application.11")
vernum = acApp.Version
Set acApp=Nothing
If (LoopCnt+1) > 900000 Then
MsgBox "loopcnt limit not high enough"
Exit do
End if
loop
.SendKeys "%fx"
End With
WScript.Sleep 500
WScript.DisconnectObject WshShell
Set acApp=nothing
WScript.Sleep 500
Set acApp=CreateObject("Access.Application.11")
' WScript.Echo acApp.Version
' WScript.Echo acApp.Name
' WScript.Echo acApp.AutomationSecurity
WScript.Sleep 500
If AcApp.Version >= 11 Then
acApp.AutomationSecurity=1 ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.usercontrol=true
Set acApp=Nothing
Else
MsgBox "The loaded version of Access is " & acapp.version & vbCrLf & _
"named: " & acApp.name, vbOKOnly + vbCritical, _
"Access 2003 not loaded"
End if
Else
MsgBox "Cannot find Access 2003 in the expected folder." & vbCrLf & _
" Contact SomService", vbOKOnly + vbCritical, _
"Access 2003 not found"
End if
'End of Script
point in the proper direction.
We currently have both MS Access XP and MS Access 2003 installed on our PCs
(XP Professional) because our mdb and adp are still XP. The shortcut to run
these databases explicitly call the XP version.
We have successfully converted our databases (they are only front-ends to SQL
Server) to 2003 in a test environment. Obviously, our goal is to eliminate
XP, but we don't want to tell services to remove it from the PCs until we
know for sure the conversion worked. Also, we distribute our DBs to other
offices where we cannot dictate removal of a version or which version is the
default version. The one thing we do know is that the users will not like
the macro security warnings in 2003, and we cannot tell other offices to set
it to low.
I have created a VBScript to open our DB by first running Access 2003 in a
shell so that it is the installed version. It then closes the application,
kills the shell, creates an instance of Access, sets its automationsecurity
to 1, and opens the DB. The problem comes in when XP was the installed
version. The script successfully installs 2003, but generates the 800A01B6
error indicating automationsecurity is not supported. If I run Detect and
Repair on 2003 and keep it installed, then my script will work because it is
not switching from XP.
Debugging: I have put in a loop to ensure 2003 gets installed before the
script continues. I have used echo to verify the version is 11. The
VBScript editor includes "automationsecurity" in its property dropdown for
the object when editing. The only thing I can surmise is that somewhere XP is
overwriting a setting that references automationsecurity for 2003.
Does anybody know of a setting or otherwise see where my problem may lie
(other than having both versions on the PC)?
My script:
' Open the MDE in Access 2003
Option Explicit
Dim PathToMDE
Dim acPath
Dim acApp
Dim WshShell
Dim objFSO
Dim LoopCnt
dim VerNum
PathToMDE = "c:\AccessDB\DB_2003.mde"
acPath = "C:\Program Files\Microsoft Office\Office11"
'Ensure MSAccess Office 2003 is default app
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(acPath) Then
Set objFSO = Nothing
Set WshShell = WScript.CreateObject("WScript.Shell")
With WshShell
.Run """C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"""
Do While verNum < 11
Set acApp=CreateObject("Access.Application.11")
vernum = acApp.Version
Set acApp=Nothing
If (LoopCnt+1) > 900000 Then
MsgBox "loopcnt limit not high enough"
Exit do
End if
loop
.SendKeys "%fx"
End With
WScript.Sleep 500
WScript.DisconnectObject WshShell
Set acApp=nothing
WScript.Sleep 500
Set acApp=CreateObject("Access.Application.11")
' WScript.Echo acApp.Version
' WScript.Echo acApp.Name
' WScript.Echo acApp.AutomationSecurity
WScript.Sleep 500
If AcApp.Version >= 11 Then
acApp.AutomationSecurity=1 ' low
acApp.OpenCurrentDatabase PathToMDE
acApp.DoCmd.RunCommand (10) 'accmdappmaximize
acApp.usercontrol=true
Set acApp=Nothing
Else
MsgBox "The loaded version of Access is " & acapp.version & vbCrLf & _
"named: " & acApp.name, vbOKOnly + vbCritical, _
"Access 2003 not loaded"
End if
Else
MsgBox "Cannot find Access 2003 in the expected folder." & vbCrLf & _
" Contact SomService", vbOKOnly + vbCritical, _
"Access 2003 not found"
End if
'End of Script