Getting the application’s path at network (MS Access 97)

Topic

New Reply

Hi All,
I have a MS Access application and the users are supposed to export and Import files using this application. This application would be located on network. The path where the files will be exported will always be the same as of the application for e.g
L:9098909802REDSREDSUSERDonor MotivationRecptControltest
Now I can put this path in the code and it runs just fine.
The problem is different users have used different drives to map to this directory.
I tried using genralised path like:
RK8VOL8029098909802REDSREDSUSERDonor MotivationRecptControltest
it does’nt work….
Please suggest some options that I could use…..

Reply | Quote

Replies

I realised all I want to solve this problem is the application path.
Here is the code that I used to get the path of the mdb:

Public Function GetAPath()
Dim FullPath$, AppPath$
Dim DB As Database
Dim iloc As Integer
Dim X As Integer

Set DB = CurrentDb

FullPath = DB.Name

For X = Len(FullPath) To 1 Step -1
iloc = InStr(X, FullPath, “”)
If iloc 0 Then
Exit For
End If
Next X

GetAPath = Mid$(FullPath, 1, iloc)

End Function

And then I am calling this function from the following :

Private Sub exportFile(qryName As String)

DoCmd.TransferSpreadsheet acExport, 8, qryName, GetAPath + qryName, True, “”
End Sub

Reply | Quote

Here is slightly simpler version of your function:
Public Function fngetpath() As String
Dim mypath As String
Dim myfilename As String
mypath = CurrentDb().Name
myfilename = Dir(mypath)
mypath = Left(mypath, Len(mypath) – Len(myfilename))
End Function

One proviso
If you have a db split into be/fe, with the fe on the local machine, this function and yours will both return the location of the fe not the be. Instead in that case you need the location of the back end.
This function will do that:

public function fnGetnetworkPath() as string
    Dim db As Database
    Set db = CurrentDb

    Dim strpath As String
    Dim lnglength As Long
    Dim tabledef As TableDef
    Dim strconnect As String
    Set tabledef = db.TableDefs("tblPatients")
 
    strconnect = mytabledef.Connect
'    msgbox(myconnect)
    lnglength = Len(strconnect)
    strpath = Right(strconnect, lnglength - 10)
    lnglength = Len(strpath)
    strpath = Left(strpath, lnglength - 16)
   fnGetnetworkPath = strpath
end function

You would need to change tblPatients to one of your tables.
The connection string includes the path but precedes it by a database type description, and finishes with the full file name. These are removed to leave the path. The 10 and 16 in my code are just hardcoded values for the length of these.

Reply | Quote

> Here is slightly simpler version of your function:

I’m going to steal this version, unless you tell me not to within the next three milliseconds.

Nice solution!

(three milliseconds later) Thanks!

Reply | Quote

I can’t claim credit for this.

I forget just where I got it – I think it was from some ealier post on this forum.

Reply | Quote

I don’t know if this is an improvement or not, but it should not be necessary to “hard code” the lengths used in function if you are dealing with a linked .MDB table, which has a “standard” connection string. Sample function to return path of linked .MDB table (assumes linked file is an .MDB file):

Public Function GetLinkedTablePath(ByVal strTbl As String) As String

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim strConn As String
Dim strMsg As String
Dim intStart As Integer

‘ This function intended for linked .MDB tables only!
‘ TableDef Attributes property:
‘ &H40000000 = Linked ISAM (.mdb, .xls, .txt, etc)
‘ &H20000000 = Linked ODBC
‘ Connection string example:
‘ ;DATABASE=H:09AACCESSFILESNorthwind.mdb

Set db = CurrentDb
Set tbl = db.TableDefs(strTbl)
strConn = tbl.Connect

‘ Note: This excludes “” at end of path; modify if necessary:
If tbl.Attributes = &H40000000 And Right(strConn, 4) = “.mdb” Then
intStart = 11 ‘ Based on standard conn string for linked .MDB table
GetLinkedTablePath = Mid(strConn, intStart, InStrRev(strConn, “”, -1, 0) – intStart)
Else
strMsg = “Specified Table (” & strTbl & “) is not a linked .MDB table.”
MsgBox strMsg, vbExclamation, “NOT LINKED TABLE”
GetLinkedTablePath = “”
End If

Exit_Function:
Set db = Nothing
Set tbl = Nothing
Exit Function
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Function
End Function

Example of use (local hard drive, works same if on network); returns path for standard installation of Northwind.mdb in Access XP/2002:

? GetLinkedTablePath(“Products1”)
C:Program FilesMicrosoft OfficeOffice10Samples

Function can be modified for use with other linked table types, which have varying connection string formats….

Reply | Quote

(Edited by MarkD on 08-Mar-03 09:32. Minor clarifications.)

As alternative to DAO TableDef properties, you can use ADOX properties to get path for non-ODBC linked tables. Sample function returns full path of linked table, including or excluding file name of source db, depending on option specified in 2nd argument:

Public Function GetLinkedTablePathADO(strTbl As String, intOpt As Integer) As String
On Error GoTo Err_Handler

‘ intOpt = 1: returns source database full path (including filename)
‘ intOpt = 2: returns source database full path (excluding filename)
‘ Linked .TXT file: Use intOpt 1, returns path only
‘ Linked .XLS file: same as .MDB
‘ Does not work with linked ODBC tables
‘ ODBC tables: parse string returned by “JET OLEDB:Link Provider String” property

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strProp As String
Dim strPath As String
Dim strMsg As String

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTbl)
strProp = “Jet OLEDB:Link Datasource”
strPath = tbl.Properties(strProp).Value

If Len(strPath) > 0 Then
If intOpt = 2 Then
strPath = Left(strPath, InStrRev(strPath, “”, -1, 0) – 1)
End If
GetLinkedTablePathADO = strPath
Else
strMsg = strTbl & ” is not a valid linked table.”
MsgBox strMsg, vbExclamation, “INVALID TABLE”
GetLinkedTablePathADO = “”
End If

Exit_Function:
Set cat = Nothing
Set tbl = Nothing
Exit Function
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Function

End Function

Example of use:

? GetLinkedTablePathADO(“Products1”,1)
C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb

? GetLinkedTablePathADO(“Products1”,2)
C:Program FilesMicrosoft OfficeOffice10Samples

This may be simpler than DAO appoach in previous example? Only drawback, I don’t see simple way to distinguish linked ISAM from linked ODBC tables the way you can with DAO TableDef Attributes property, other than to test for Link Datasource property returning an empty string. Also, when testing both methods, the DAO method was VERY slow, taking several seconds to return connection string (even on local drive), whereas the ADOX function returned string with no delay. You’d have to test both to see if this applies on your system. To use this example, set a reference to Microsoft ADO Ext. 2.x for DDL and Security (ADOX) type library (MSADOX.DLL).

HTH

Reply | Quote

The worst thing about working with ADO for this is that the OLEDB properties have such clunky names. There are no simple property names like “Connect” or “SourceTable”. Instead, you have to get the OLEDB property name exactly right for the correct provider and they can be pretty much whatever the provider decides to call them. That provides tremendous flexibility in developing OLEDB drivers but it can be awfully frustrating for the developer using them. Once you’ve build the code, it runs nicely, of course.

Reply | Quote

You’re right about the clunky property names. I had to loop thru table properties to get right syntax using sub like this:

Public Sub GetADOTableProperties(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 = 1 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

Example (linked .MDB table):

GetADOTableProperties(“Products1”)

Jet OLEDB:Table Validation Text:
Jet OLEDB:Table Validation Rule:
Jet OLEDB:Cache Link Name/Password: False
Jet OLEDB:Remote Table Name: Products
Jet OLEDB:Link Provider String:
Jet OLEDB:Link Datasource: C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
Jet OLEDB:Exclusive Link: False
Jet OLEDB:Create Link: True
Jet OLEDB:Table Hidden In Access: False

Like that last property listed; I think a simple “Hidden” may have sufficed as property name….

Reply | Quote

Yes, everyone working with ADO needs a routine like this in their toolkit because every provider’s properties are different. So is you change the provider to SQL Server, you’ve got a whole new set of properties to work with. That means, you have to enum the property names for each provider before you start working with them.

Reply | Quote