FileDateTime(CurrentProject.FullName) on backend datafile (1 Viewer)

[Danick]

In the front end of my database, I added an unbound text box with a click event to show the last modified date of the database

Me.txtLastModified = Format(FileDateTime(CurrentProject.FullName), "ddd, mmm dd yyyy, hh:mm AM/PM")

This works fine, but only for the front end. The backend is located on an NAS. I could modify the "FullName" to the exact location and name of the backend, but I'd rather just use relative references to the linked backend to get the information.

Is there any way to do that without hard coding the exact name and location of the database backend?

Thanks

 

[theDBguy]

Hi. Check out the Connect property of your linked tables. Or, the Database column in the MSysObject table.

 

[arnelgp]

from Allen Browne site:

Public Function GetDataPath(strTable As String) As String
      On Error GoTo Err_Handler
      'Purpose:   Return the full path of the file from the Connect property of this tabledef.
      'Return:    Full path and file name for attached MDB.
      '           Just the path for some other types (e.g. attached text.)
      '           Zero-length string for local table (not attached), or of argument is zero-length.
      '           "#Error" on error, e.g. table not found.
      'Requires:  Split() function for Access 97 or earlier.
      Dim varArray As Variant
      Dim i As Integer

      If Trim$(strTable) <> vbNullString Then
            varArray = Split(CurrentDb.TableDefs(strTable).Connect, ";")
            For i = LBound(varArray) To UBound(varArray)
                  If varArray(i) Like "DATABASE=*" Then
                        GetDataPath = Trim$(Mid$(varArray(i), 10))
                        Exit For
                  End If
            Next
      End If
Exit_Handler:
      Exit Function

Err_Handler:
      LogError Err.Number, Err.Description, conMod & ".GetDataPath", strTable, False
      GetDataPath = "#Error"
      Resume Exit_Handler
End Function

to use:

strBackEnd = GetDataPath("anyLinkTableNameHere")

 

[Danick]

Hi arnelgp

This module is giving me a Compile error: Sub or Function not defined.
The notes in the module state that it's for Access 97 or earlier. I'm using Access 2016. Could that be the problem?
I'm using a button to populate a text box with this code.

Dim strBackEnd As String
strBackEnd = GetDataPath("tblName")

Me.txtLastModified = strBackEnd

Thanks

 

[theDBguy]

Hi arnelgp

This module is giving me a Compile error: Sub or Function not defined.
The notes in the module state that it's for Access 97 or earlier. I'm using Access 2016. Could that be the problem?
I'm using a button to populate a text box with this code.

Dim strBackEnd As String
strBackEnd = GetDataPath("tblName")

Me.txtLastModified = strBackEnd

Thanks

Hi. Just curious, what do you get with this?

MsgBox DLookup("Database","MSysObjects","[Name]='tblName'")

 

[Danick]

Hi. Just curious, what do you get with this?

MsgBox DLookup("Database","MSysObjects","[Name]='tblName'")

I get the full location and name of the backend database name.
\\<network>\<folder>\<BackEndName.mdb>

So I figured out how to get it working with "MSysObjects"

Just created a simple string for the backend and called it up on a button click.

Dim strBackEnd As String
strBackEnd = DLookup("Database", "MSysObjects", "[Name]='tblName'")
Me.txtLastModified = Format(FileDateTime(strBackEnd), "ddd, mmm dd yyyy   hh:mm AM/PM")

Thanks for the help!!

 

[theDBguy]

I get the full location and name of the backend database name.
\\<network>\<folder>\<BackEndName.mdb>

So I figured out how to get it working with "MSysObjects"

Just created a simple string for the backend and called it up on a button click.

Dim strBackEnd As String
strBackEnd = DLookup("Database", "MSysObjects", "[Name]='tblName'")
Me.txtLastModified = Format(FileDateTime(strBackEnd), "ddd, mmm dd yyyy   hh:mm AM/PM")

Thanks for the help!!

Hi. Glad to hear you got it sorted out. Good luck with your project.