MS Access, ODBC and drive mapping

  • Thread starter Thread starter Joseph Uher
  • Start date Start date
J

Joseph Uher

I have created a Access database to which users connect
via ODBC. With Oracle and other drivers, I can map a drive
(checking reconnect at logon) to a System DSN as
an "administrator" and the connection is maintained for
any user on the workstation. With Access's Microsoft
Access Driver, I've found that other (non-administrator)
users do not have the mapped drive maintained.

I cannot set up all workstations ODBC for each user? Is
there a way around this problem?
 
Try using a UNC (\\server\share\folder\file.mdb), rather than
f:\folder\file.mdb
 
if you by mapped drive mean network drive u can use the UNCPath instead
ie
W:\data\access.mdb would be \\SERVER\USERDATA\DATA

u could use this to get at it:
'--------------------------------------------
Private Declare Function WNetGetConnection Lib "mpr.dll" Alias
"WNetGetConnectionA" (ByVal lpszLocalName As String, ByVal lpszRemoteName As
String, cbRemoteName As Long) As Long


Public Function GetUNCPath(ByVal strDriveLetter As String, Optional ByVal
FullPath As Boolean = False) As String
Const ERROR_BAD_DEVICE = 1200&
Const ERROR_CONNECTION_UNAVAIL = 1201&
Const ERROR_EXTENDED_ERROR = 1208&
Const ERROR_MORE_DATA = 234
Const ERROR_NOT_SUPPORTED = 50&
Const ERROR_NO_NET_OR_BAD_PATH = 1203&
Const ERROR_NO_NETWORK = 1222&
Const ERROR_NOT_CONNECTED = 2250&
Const NO_ERROR = 0
Dim ret As Long
Dim LocDrive As String
Dim UNCPath As String
Dim UNCLen As Long
On Local Error Resume Next
LocDrive = VBA.Left(strDriveLetter, 2) & VBA.Chr(0)
UNCPath = VBA.String(255, VBA.Chr(0))
UNCLen = Len(UNCPath)
ret = WNetGetConnection(LocDrive, UNCPath, UNCLen)
If ret Then
GetUNCPath = "" ' strDriveLetter
ApiRaise ret
Else
ret = VBA.InStr(UNCPath, ":")
If ret Then UNCPath = VBA.Left(UNCPath, ret - 1) & "\" &
VBA.Mid(UNCPath, ret + 1) 'novell
UNCPath = VBA.Left(UNCPath, VBA.InStr(UNCPath, VBA.Chr(0)) - 1)
If (Len(strDriveLetter) > 2) And FullPath Then UNCPath = UNCPath &
VBA.Mid(strDriveLetter, 3)
GetUNCPath = UNCPath
End If
End Function
'---------------------------------------

HTH

Pieter
 
Gave this a try ... however, setting the ODBC requires
mapping the drive. I accomplish this with an
administrative sign on; signing off and signing back on as
a (non-administrative) user, the mapped drive does not
even show in "My Computer". My confusion is that working
with an Oracle ODBC driver (for another database), the
settings hold for a System DSN for *all* users.

Is this just "the way MS Access driver works"? Or is there
a way around this: ie, to get the mapped drive to hold for
*all* users via a System DSN?

JFU
 
Back
Top