Access 2010 - Launch DB from 2 different paths

[sunnie_joe]

I have users with mix OS needs to access the database resides on the server and can’t be divided in 2 or have 2 separate databases. The VB within the db, has code reference to the MS Office see below, I can only use one.

I’m looking for a way to incorporate both path in the script or an alternative method to enable both users access the db from multi OS Win-XP and Win-7.

I can do it with a batch file using the following script, but I prefer doing it within the DB.

If exist C:\"Program Files (x86)" GOTO 64BIT
if not exist C:\"Program Files (x86)" GOTO 32BIT

Thank you

Database
path= \\server-670\DATABASE\tsa.accdb

Clients
Windows XP 32bit OS with MS Access 2010 /32bit,
path= C:\Program Files\Microsoft Office\office14\MSACCESS.EXE

Windows 7 64bit OS with MS Access 2010 /32bit,
path= C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE

VB script
Private Sub Command71_Click()
Dim stAppName As String
stAppName = "" & DLookup("[Link]", "Admin_Links", "[No] = 4") & ""
If DLookup("[Type]", "Admin_Links", "[No] = 4") = "Database" Then
Call Shell(stAppName, 1)
Else
Application.FollowHyperlink stAppName, , True
End If
‘stAppName = "C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw"
‘stAppName = "C:\Program Files\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw"
End Sub

 

[Yonnel]

Can't you simply use a function like this one to check if the folder exists?

Public Function fnFolderExists(strFullPath As String) As Boolean
    On Error GoTo EndOfSub
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then fnFolderExists= True
EndOfSub:
    On Error GoTo 0
End Function

Then you do something like this:

If fnFolderExists("C:\Program Files (x86)\") Then
    stAppName = "C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw"
Else
    stAppName = "C:\Program Files\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw" 
End If
Application.FollowHyperlink stAppName, , True

-----
Lionel Garnier
http://www.gylsolutions.fr - IT consulting, VBA, Access trainings, Excel trainings, PowerPoint trainings

 

[sunnie_joe]

Simplify my post
________________________________________
I have users with mix OS needs to access the database resides on the server and can’t be divided in 2 or have 2 separate databases. The VB within the db, has code reference to the MS Office see below, I can only use one.

I’m looking for a way to incorporate both path in the script or an alternative method to enable both users access the db from multi OS Win-XP and Win-7.

Thank you

Database
path= \\server-670\DATABASE\tsa.accdb

Clients
Windows XP 32bit OS with MS Access 2010 /32bit,
path= C:\Program Files\Microsoft Office\office14\MSACCESS.EXE

Windows 7 64bit OS with MS Access 2010 /32bit,
path= C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE

***********************************************************************
VB script
Private Sub Command71_Click()
Dim stAppName As String
stAppName = "" & DLookup("[Link]", "Admin_Links", "[No] = 4") & ""
If DLookup("[Type]", "Admin_Links", "[No] = 4") = "Database" Then
Call Shell(stAppName, 1)
Else
Application.FollowHyperlink stAppName, , True
End If

‘stAppName = "C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE" \\server-670\DATABASE\tsa.accdb
‘stAppName = "C:\Program Files\Microsoft Office\office14\MSACCESS.EXE" \\server-670\DATABASE\tsa.accdb

End Sub

***********************************************************************
I can do it with a batch file using the following script, but I prefer doing it within the DB.

If exist C:\"Program Files (x86)" GOTO 64BIT
if not exist C:\"Program Files (x86)" GOTO 32BIT

 

[sunnie_joe]

Can't you simply use a function like this one to check if the folder exists?

Public Function fnFolderExists(strFullPath As String) As Boolean
    On Error GoTo EndOfSub
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then fnFolderExists= True
EndOfSub:
    On Error GoTo 0
End Function

Then you do something like this:

If fnFolderExists("C:\Program Files (x86)\") Then
    stAppName = "C:\Program Files (x86)\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw"
Else
    stAppName = "C:\Program Files\Microsoft Office\office14\MSACCESS.EXE\ \\server-670\DATABASE\tsa.accdb /wrkgrp \\server-670\DATABASE\tsa.mdw" 
End If
Application.FollowHyperlink stAppName, , True

-----
Lionel Garnier

.......................................................................
Thanks Lionel, I'll give it a try and get back with you.

 

[sunnie_joe]

Hi Lionel,

The scripted you suggested didn’t work out sorry. I end up adding additional command buttons for Windows 7 users on the main dashboard.
I was wondering if you can help me integrating this script with the existing db. The script runs fine independently, can be launched from anywhere in windows. I would like this script to lunch a at the same time my dashboard comes up.
Thanks
***************
Option Explicit
Dim oShell
'set oShell = WScript.CreateObject("Wscript.Shell")
'oShell.RegWrite "HKEY_LOCAL_MACHINE\SOFTWARE\NETCON\FirstPAGE Client\Config\Server","m2pagip5.maep.chrysler.com"
If OSbit64() Then
MsgBox "You’re running Windows 7 Operating System!"
Else
MsgBox "You’re running Windows XP Operating System!"
End If
Function OSbit64()
' This works on Windows 2000, XP, Vista, 7, as well as Server 2000, 2003, and 2008
Const HKLM = &H80000002
Dim strComputer, WshShell, sOSBit
strComputer = "."
Set WshShell = WScript.CreateObject("WScript.Shell")
OSbit64 = True
sOSbit = WshShell.RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\PROCESSOR_ARCHITECTURE")
If sOSBit = "x86" Then
OSbit64 = False
End If
Set WshShell = Nothing
End Function
***************