Varying server locations problem (Access 2K Win 2K)

Topic

New Reply

Dear All

Hello again. I’m working on a very simple little database for my boss, I’ve got the bulk of it sorted and have now come across a little problem. The finished item will be a front end / back end system, used by up to 10 people at one time, my problem is that some of the 10 people are using different drive letters for the server location I’ve been allocated for this database.

The initial solution, change their drive letters, has been rejected for various reasons.

I can easily enough link to the Access back end via the ‘my network places’ option, the problem comes from supporting files that I need to manipulate with VBA (all Excel files). They will be in the same folder as the back end system, but the only way I seem to be capable of getting the support file locations is an activeX list box (the example from programming for Dummies) backed up with a combo box for the user to select the drive letter. This requires more user understanding (and creates some potential errors) than I’m happy with.

How can I put the server name into code? My attempts to date have failed miserably.

Thanks

Ian

Reply | Quote

Replies

You should be able to use paths like “serversharefolder” in code. You can also get at the path to the database in code using CurrentDb.Name. For example,

Left(CurrentDb.Name, InStrRev(CurrentDb.Name, “”))

returns the path of the database with trailing backslash. This avoids having to hard-code the path.

Reply | Quote

Hans

Thank You, the currentDb thing is the perfect solution to my headache.

Thanks

Ian

Reply | Quote

Hans

Thank You, the currentDb thing is the perfect solution to my headache.

Thanks

Ian

Reply | Quote

I believe that Left(CurrentDb.Name, InStrRev(CurrentDb.Name, “”)) will return the path to the front not the path to the back end.

To get the path to the back end, I always look at the connect property of one of the tables, but I have often wondered if there is an easier way.

Reply | Quote

You are correct. I didn’t read the question carefully enough.

Reply | Quote

Here is the function I use to find the path to the back end:

Public Function fngetlinkedpath() As String
   Dim strmypath As String
   Dim db As DAO.Database
   Set db = CurrentDb
   Dim strTableName As String
   Dim mytabledef As DAO.TableDef
   Dim strmyconnect As String
   strTableName = "tbldoctors"
   Set mytabledef = db.TableDefs(strTableName)
   strmyconnect = mytabledef.Connect
   strmypath = Right(strmyconnect, Len(strmyconnect) - 10)
   strmypath = Left(strmypath, InStrRev(strmypath, ""))
  fngetlinkedpath = strmypath
  
End Function

The connect property returns ” ; DATABASE=” followed by the name and path of the back end.
so I chop of the first 10 characters to get the name and path, then cut it back to just the path.

edited by john to remove some unnecessary code

Reply | Quote

Here is the function I use to find the path to the back end:

Public Function fngetlinkedpath() As String
   Dim strmypath As String
   Dim db As DAO.Database
   Set db = CurrentDb
   Dim strTableName As String
   Dim mytabledef As DAO.TableDef
   Dim strmyconnect As String
   strTableName = "tbldoctors"
   Set mytabledef = db.TableDefs(strTableName)
   strmyconnect = mytabledef.Connect
   strmypath = Right(strmyconnect, Len(strmyconnect) - 10)
   strmypath = Left(strmypath, InStrRev(strmypath, ""))
  fngetlinkedpath = strmypath
  
End Function

The connect property returns ” ; DATABASE=” followed by the name and path of the back end.
so I chop of the first 10 characters to get the name and path, then cut it back to just the path.

edited by john to remove some unnecessary code

Reply | Quote

You are correct. I didn’t read the question carefully enough.

Reply | Quote

Try ADO. In A2002 this works without an ADO reference, but I don’t recall whether it does in 2000.

Public Function GetCurrentDataPath() As String
  'created by Charlotte Foust 2/14/2001
  'returns the current backend path
  'from the base connection string
  On Error Resume Next
  Dim strSource As String
  Dim strFile As String
  'get the connection string for the backend
  strSource = CurrentProject.BaseConnectionString
  If strSource  "" Then
    'get everthing to the right of "DATA SOURCE="
    strSource = Mid(strSource, InStr(strSource, "DATA SOURCE=") + 12)
    'trim off everything beyond the path
    strSource = Left(strSource, InStr(strSource, ";") - 1)
  End If
  GetCurrentDataPath = strSource
End Function 'GetCurrentDataPath() As String

Reply | Quote

When I tried this function getCurrentdatapath() in 2000, it returned the path to the front end.

? getCurrentdatapath()
C:My DocumentsTitan.mdb
? fngetlinkedpath()
E:JOHNACCESSGenPrac

Reply | Quote

When I tried this function getCurrentdatapath() in 2000, it returned the path to the front end.

? getCurrentdatapath()
C:My DocumentsTitan.mdb
? fngetlinkedpath()
E:JOHNACCESSGenPrac

Reply | Quote

I’ll have to check it in A2k at home. It returns the appropriate value in AXP on this machine, and the first table in the db window is not a linked table.

AHA!! You need an ADO connection established to the back end before this gives you the right value.

Reply | Quote

I’ll have to check it in A2k at home. It returns the appropriate value in AXP on this machine, and the first table in the db window is not a linked table.

AHA!! You need an ADO connection established to the back end before this gives you the right value.

Reply | Quote

If you’re too lazy to declare variables, etc, you can use one-line functions like these examples:

Public Function GetLinkedTablePath(ByRef strTbl As String) As String

GetLinkedTablePath = Mid$(Left$(CurrentDb.TableDefs(strTbl).Connect, _
InStrRev(CurrentDb.TableDefs(strTbl).Connect, “”, , 0) – 1), 11)

End Function

Public Function GetLinkedTableDBName(ByRef strTbl As String) As String

GetLinkedTableDBName = Mid$(CurrentDb.TableDefs(strTbl).Connect, _
InStrRev(CurrentDb.TableDefs(strTbl).Connect, “”, , 0) + 1)

End Function

Sample results (A2K) with linked table located in Northwind.mdb:

? GetLinkedTablePath(“Orders”)
C:Program FilesMicrosoft OfficeOfficeSamples

? GetLinkedTableDBName(“Orders”)
Northwind.mdb

Functions returned correct results with other linked tables in other back end db’s. These are intended for linked Access tables where the connection string looks like this:

;DATABASE=C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb

HTH

Reply | Quote

If not lazy, you can use more than single-line function to get path for linked Excel or Text files in addition to Access .mdb files:

Public Function GetLinkedTablePathEx(ByRef strTbl As String) As String
On Error GoTo Err_Handler

‘ Use for linked Access, XL, or Text files:
Dim strConnect As String
Dim strMsg As String

strConnect = CurrentDb.TableDefs(strTbl).Connect

Select Case Left$(strConnect, 5)
Case “;DATA”, “EXCEL”
‘ Access .MDB or Excel .XLS – connect string returns full path & file name:
GetLinkedTablePathEx = Mid$(Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1), _
InStr(1, strConnect, “DATABASE=”, vbTextCompare) + 9)
‘ Test if file in root directory:
If InStr(1, strConnect, “”, 0) = InStrRev(strConnect, “”, , 0) Then
GetLinkedTablePathEx = GetLinkedTablePathEx & “”
End If

Case “TEXT;”
‘ Text file – Connection string returns path only:
GetLinkedTablePathEx = Mid$(strConnect, InStr(1, strConnect, “DATABASE=”, vbTextCompare) + 9)
Case Else
GetLinkedTablePathEx = vbNullString
End Select

Exit_Sub:
Exit Function
Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “Table not found!”
MsgBox strMsg, vbExclamation, strMsg
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Unexpected Error”
End Select
Resume Exit_Sub
End Function

Test results (with example of connection string formats):

? CurrentDB.TableDefs(“Orders”).Connect
;DATABASE=C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
? CurrentDB.TableDefs(“XLFile”).Connect
Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:Documents and SettingsMARK DMy DocumentsWork StuffXLFile.xls
? CurrentDB.TableDefs(“TextFile”).Connect
Text;DSN=TextFile Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=1252;DATABASE=C:Access

? GetLinkedTablePathEx(“Orders”)
C:Program FilesMicrosoft OfficeOffice10Samples
? GetLinkedTablePathEx(“XLFile”)
C:Documents and SettingsMARK DMy DocumentsWork Stuff
? GetLinkedTablePathEx(“TextFile”)
C:Access

Note that the connection string for linked Text file provides path only. Also added test in case file in root directory (eg, path = “C:”). Modified function to get file name only:

Public Function GetLinkedTableDBNameEx(ByRef strTbl As String) As String
On Error GoTo Err_Handler

Dim strConnect As String
strConnect = CurrentDb.TableDefs(strTbl).Connect

Select Case Left$(strConnect, 5)
Case “;DATA”, “EXCEL”
GetLinkedTableDBNameEx = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Case “TEXT;”
‘ Text file – Connection string returns path only, use SourceTableName property:
GetLinkedTableDBNameEx = CurrentDb.TableDefs(strTbl).SourceTableName
Case Else
GetLinkedTableDBNameEx = vbNullString
End Select
‘ Error handling, etc
End Function

Test results:

? GetLinkedTableDBNameEx(“Orders”)
Northwind.mdb
? GetLinkedTableDBNameEx(“XLFile”)
XLFile.xls
? GetLinkedTableDBNameEx(“TextFile”)
TextFile.txt

The 2nd function worked correctly whether or not file located in root directory. If working with other types of linked tables, then functions can be modified accordingly based on format of connection string returned by table’s Connect property.

Reply | Quote

One last note, you can use ADOX instead of DAO to get linked table properties, but it is perceptibly slower than DAO. Example, applicable to linked Access, Excel, or Text files using Jet 4.0 provider:

Public Sub GetLinkedTablePathADO(ByRef strTbl As String, _
ByRef strPath As String, _
ByRef strFile As String)
On Error GoTo Err_Handler

Dim cat As New ADOX.Catalog
Dim strLinkProvider As String
Dim strConnect As String
Dim strMsg As String

‘ Example (Access):
‘ Jet OLEDB:Link Datasource: C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb

cat.ActiveConnection = CurrentProject.Connection
‘ Determine type of linked table:
strLinkProvider = Left$(cat.Tables(strTbl).Properties(“Jet OLEDB:Link Provider String”), 5)

If Len(strLinkProvider) = 0 Then
‘ Linked Access table
strConnect = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strPath = Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1)
strFile = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Else
Select Case strLinkProvider
Case “Excel”
strConnect = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strPath = Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1)
strFile = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Case “Text;”
strPath = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strFile = Replace(cat.Tables(strTbl).Properties(“Jet OLEDB:Remote Table Name”), “#”, “.”, , , 0)
Case Else
MsgBox “Unknown Link Provider.”, vbExclamation, “UNKNOWN”
End Select
End If

Debug.Print “Linked table path: ” & strPath
Debug.Print “Linked table file: ” & strFile

Exit_Sub:
Set cat = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “Table not found.”
MsgBox strMsg, vbExclamation, strMsg
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Unexpected Error”
Resume Exit_Sub
End Select

End Sub

Test results:

GetLinkedTablePathADO “Orders”,vbNullString ,vbNullString
Linked table path: C:Program FilesMicrosoft OfficeOffice10Samples
Linked table file: Northwind.mdb

GetLinkedTablePathADO “XLFile”,vbNullString ,vbNullString
Linked table path: C:Documents and SettingsMARK DMy DocumentsWork Stuff
Linked table file: XLFile.xls

GetLinkedTablePathADO “TextFile”,vbNullString ,vbNullString
Linked table path: C:Access
Linked table file: TextFile.txt

Note use of sub vice function to avoid duplication; pass null strings by reference to sub for strPath and strFile arguments. If using linked tables other than shown here (such as ODBC), or a different provider, modify sub as necessary. To determine what provider-specific properties are available for linked table, and their values, use sub like this to list table properties:

Public Sub GetTablePropertiesADO(ByRef strTbl As String)
On Error Resume Next

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim i As Integer

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTbl)

For i = 0 To tbl.Properties.Count – 1
Debug.Print tbl.Properties(i).NAME & “: ” & tbl.Properties(i).Value
Next i

Set cat = Nothing
Set tbl = Nothing

End Sub

As noted above, if using Jet linked tables only, DAO will return quicker results.

Reply | Quote

One last note, you can use ADOX instead of DAO to get linked table properties, but it is perceptibly slower than DAO. Example, applicable to linked Access, Excel, or Text files using Jet 4.0 provider:

Public Sub GetLinkedTablePathADO(ByRef strTbl As String, _
ByRef strPath As String, _
ByRef strFile As String)
On Error GoTo Err_Handler

Dim cat As New ADOX.Catalog
Dim strLinkProvider As String
Dim strConnect As String
Dim strMsg As String

‘ Example (Access):
‘ Jet OLEDB:Link Datasource: C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb

cat.ActiveConnection = CurrentProject.Connection
‘ Determine type of linked table:
strLinkProvider = Left$(cat.Tables(strTbl).Properties(“Jet OLEDB:Link Provider String”), 5)

If Len(strLinkProvider) = 0 Then
‘ Linked Access table
strConnect = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strPath = Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1)
strFile = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Else
Select Case strLinkProvider
Case “Excel”
strConnect = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strPath = Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1)
strFile = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Case “Text;”
strPath = cat.Tables(strTbl).Properties(“Jet OLEDB:Link Datasource”).Value
strFile = Replace(cat.Tables(strTbl).Properties(“Jet OLEDB:Remote Table Name”), “#”, “.”, , , 0)
Case Else
MsgBox “Unknown Link Provider.”, vbExclamation, “UNKNOWN”
End Select
End If

Debug.Print “Linked table path: ” & strPath
Debug.Print “Linked table file: ” & strFile

Exit_Sub:
Set cat = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “Table not found.”
MsgBox strMsg, vbExclamation, strMsg
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Unexpected Error”
Resume Exit_Sub
End Select

End Sub

Test results:

GetLinkedTablePathADO “Orders”,vbNullString ,vbNullString
Linked table path: C:Program FilesMicrosoft OfficeOffice10Samples
Linked table file: Northwind.mdb

GetLinkedTablePathADO “XLFile”,vbNullString ,vbNullString
Linked table path: C:Documents and SettingsMARK DMy DocumentsWork Stuff
Linked table file: XLFile.xls

GetLinkedTablePathADO “TextFile”,vbNullString ,vbNullString
Linked table path: C:Access
Linked table file: TextFile.txt

Note use of sub vice function to avoid duplication; pass null strings by reference to sub for strPath and strFile arguments. If using linked tables other than shown here (such as ODBC), or a different provider, modify sub as necessary. To determine what provider-specific properties are available for linked table, and their values, use sub like this to list table properties:

Public Sub GetTablePropertiesADO(ByRef strTbl As String)
On Error Resume Next

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim i As Integer

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTbl)

For i = 0 To tbl.Properties.Count – 1
Debug.Print tbl.Properties(i).NAME & “: ” & tbl.Properties(i).Value
Next i

Set cat = Nothing
Set tbl = Nothing

End Sub

As noted above, if using Jet linked tables only, DAO will return quicker results.

Reply | Quote

If not lazy, you can use more than single-line function to get path for linked Excel or Text files in addition to Access .mdb files:

Public Function GetLinkedTablePathEx(ByRef strTbl As String) As String
On Error GoTo Err_Handler

‘ Use for linked Access, XL, or Text files:
Dim strConnect As String
Dim strMsg As String

strConnect = CurrentDb.TableDefs(strTbl).Connect

Select Case Left$(strConnect, 5)
Case “;DATA”, “EXCEL”
‘ Access .MDB or Excel .XLS – connect string returns full path & file name:
GetLinkedTablePathEx = Mid$(Left$(strConnect, InStrRev(strConnect, “”, , 0) – 1), _
InStr(1, strConnect, “DATABASE=”, vbTextCompare) + 9)
‘ Test if file in root directory:
If InStr(1, strConnect, “”, 0) = InStrRev(strConnect, “”, , 0) Then
GetLinkedTablePathEx = GetLinkedTablePathEx & “”
End If

Case “TEXT;”
‘ Text file – Connection string returns path only:
GetLinkedTablePathEx = Mid$(strConnect, InStr(1, strConnect, “DATABASE=”, vbTextCompare) + 9)
Case Else
GetLinkedTablePathEx = vbNullString
End Select

Exit_Sub:
Exit Function
Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “Table not found!”
MsgBox strMsg, vbExclamation, strMsg
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Unexpected Error”
End Select
Resume Exit_Sub
End Function

Test results (with example of connection string formats):

? CurrentDB.TableDefs(“Orders”).Connect
;DATABASE=C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
? CurrentDB.TableDefs(“XLFile”).Connect
Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:Documents and SettingsMARK DMy DocumentsWork StuffXLFile.xls
? CurrentDB.TableDefs(“TextFile”).Connect
Text;DSN=TextFile Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=1252;DATABASE=C:Access

? GetLinkedTablePathEx(“Orders”)
C:Program FilesMicrosoft OfficeOffice10Samples
? GetLinkedTablePathEx(“XLFile”)
C:Documents and SettingsMARK DMy DocumentsWork Stuff
? GetLinkedTablePathEx(“TextFile”)
C:Access

Note that the connection string for linked Text file provides path only. Also added test in case file in root directory (eg, path = “C:”). Modified function to get file name only:

Public Function GetLinkedTableDBNameEx(ByRef strTbl As String) As String
On Error GoTo Err_Handler

Dim strConnect As String
strConnect = CurrentDb.TableDefs(strTbl).Connect

Select Case Left$(strConnect, 5)
Case “;DATA”, “EXCEL”
GetLinkedTableDBNameEx = Mid$(strConnect, InStrRev(strConnect, “”, , 0) + 1)
Case “TEXT;”
‘ Text file – Connection string returns path only, use SourceTableName property:
GetLinkedTableDBNameEx = CurrentDb.TableDefs(strTbl).SourceTableName
Case Else
GetLinkedTableDBNameEx = vbNullString
End Select
‘ Error handling, etc
End Function

Test results:

? GetLinkedTableDBNameEx(“Orders”)
Northwind.mdb
? GetLinkedTableDBNameEx(“XLFile”)
XLFile.xls
? GetLinkedTableDBNameEx(“TextFile”)
TextFile.txt

The 2nd function worked correctly whether or not file located in root directory. If working with other types of linked tables, then functions can be modified accordingly based on format of connection string returned by table’s Connect property.

Reply | Quote

If you’re too lazy to declare variables, etc, you can use one-line functions like these examples:

Public Function GetLinkedTablePath(ByRef strTbl As String) As String

GetLinkedTablePath = Mid$(Left$(CurrentDb.TableDefs(strTbl).Connect, _
InStrRev(CurrentDb.TableDefs(strTbl).Connect, “”, , 0) – 1), 11)

End Function

Public Function GetLinkedTableDBName(ByRef strTbl As String) As String

GetLinkedTableDBName = Mid$(CurrentDb.TableDefs(strTbl).Connect, _
InStrRev(CurrentDb.TableDefs(strTbl).Connect, “”, , 0) + 1)

End Function

Sample results (A2K) with linked table located in Northwind.mdb:

? GetLinkedTablePath(“Orders”)
C:Program FilesMicrosoft OfficeOfficeSamples

? GetLinkedTableDBName(“Orders”)
Northwind.mdb

Functions returned correct results with other linked tables in other back end db’s. These are intended for linked Access tables where the connection string looks like this:

;DATABASE=C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb

HTH

Reply | Quote

Try ADO. In A2002 this works without an ADO reference, but I don’t recall whether it does in 2000.

Public Function GetCurrentDataPath() As String
  'created by Charlotte Foust 2/14/2001
  'returns the current backend path
  'from the base connection string
  On Error Resume Next
  Dim strSource As String
  Dim strFile As String
  'get the connection string for the backend
  strSource = CurrentProject.BaseConnectionString
  If strSource  "" Then
    'get everthing to the right of "DATA SOURCE="
    strSource = Mid(strSource, InStr(strSource, "DATA SOURCE=") + 12)
    'trim off everything beyond the path
    strSource = Left(strSource, InStr(strSource, ";") - 1)
  End If
  GetCurrentDataPath = strSource
End Function 'GetCurrentDataPath() As String

Reply | Quote

I believe that Left(CurrentDb.Name, InStrRev(CurrentDb.Name, “”)) will return the path to the front not the path to the back end.

To get the path to the back end, I always look at the connect property of one of the tables, but I have often wondered if there is an easier way.

Reply | Quote

You should be able to use paths like “serversharefolder” in code. You can also get at the path to the database in code using CurrentDb.Name. For example,

Left(CurrentDb.Name, InStrRev(CurrentDb.Name, “”))

returns the path of the database with trailing backslash. This avoids having to hard-code the path.

Reply | Quote

Just for what it’s worth picking up an old thread but…
suppose you have supporting front-end table(s) with source table information including the (eventually presumed) relative/full source file path: could this be of some help?
For example, suppose that the users just have a different drive letter but the drives point at exact the same location. Then, if you’re sure the user HAS such a drive, you could get that drive letter in one way or another, put that in front of the relative path you get from the supporting table and off you go…
But maybe I’m overlooking something… looking to all this code in the previous posts (which I, to be honest, didn’t fully consume )

Reply | Quote