Show Linked file creation and modification date 2

[jdttek]

I have several linked tables in my DB. I would like text boxes on my switchboard to show users the creationdate and last modification date of each linked file. I am familiar with extracting and showihg this info in Excel using BuiltinDocumentProperties, and am trying to use the DateCreated and LastUpdated Properties here, but don't know how to populate the text boxes with the results in Access and have the data refresh each time the switchboard is opened. Any help greatly apprceiated.

Thanks
JDTTEK

 

[VBslammer]

Private Sub Form_Load()
On Error Goto ErrHandler

  Dim tdf As TableDef
  Dim db As Database
  
  Set db = CurrentDb()
  Set tdf = db.TableDefs("MyLinkedTable")
  
  ' update textboxes.
  Me.txtName = tdf.Name
  Me.txtCreated = tdf.DateCreated
  Me.txtUpdated = tdf.LastUpdated

ExitHere:
  On Error Resume Next
  Set tdf = Nothing
  Set db = Nothing
  Exit Sub
ErrHandler:
  MsgBox "Error: " & Err & "-" & Err.Description
  Resume ExitHere  
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[jdttek]

VBSlammer
Thanks for your reply.

I have tried the following which by all reasoning, should work:
Private Sub Form_Load()
Dim tdf As TableDef
Dim db As Database
Set db = Currentdb()
Set tdf =db.TableDefs("CreditLimits&quot 'Linked table name
Me.txtDate = tdf.DateCreated 'Box for creation date
End Sub

When I run thei I get:
"Compile Error: Can't find project or Library"

Have checked references and all look OK. What do you think is goinfg wrong?

Meanwhile, following does work (gets date of underlying linked file):

Private Sub Form_Load()
Dim fs,f,d, n
Set Fs=CreateObject("Scripting.FileSystemObject&quot
Set f= fs.GetFile("C:\CreditLimit.xls&quot
d = f.DateCreated
n = f.Name
Me.txtDate = d
Me.txtName = n

End Sub

What I am trying to accomplish is to allow user to compare the name and creation date of the linked file with the name and creation date of the underlying source file to verify that the correct files, tables and links are being used (ie, that the link is to the correct table).

1. Any idea why the TableDef option is not working?
2. Does yours show the creation date of the link, or the creation date of the underlying source file?

Thanks

JDTTEK

 

[AccessAce]

In your code, if it's Access2000, you need to specify if your are using ADO or DAO objects. Try definine youg objects like this.

Dim tdf As DAO.TableDef

That should do it.

 

[jdttek]

Did DAO.TableDef and DAO.Database and worked like a charm. Thanks. You get a star!!

Give objective allowing user to verify that link being used is to the correct file, I tried to add tdf.SourceTableName and only got the named range of Excel workbook. Any idea how to show full file name?

Thanks again.

JDTTEK

 

[AccessAce]

I'm not sure of the specifics, but in the MySysObjects table, the Database field will have the complete path and filename for the linked table. You could try refenceing that field to get the data you want. Sorry I don't have the code for you to post.

 

[VBslammer]

The path is stored in the tabledef's connect string:

Private Sub Form_Load()
  Dim tdf As TableDef
  Dim db As Database
  
  Set db = CurrentDb()
  Set tdf = db.TableDefs("X Y")
  
  ' update textboxes.
  Me.txtName = tdf.Name
  Me.txtCreated = tdf.DateCreated
  Me.txtUpdated = tdf.LastUpdated
  Me.txtPath = GetPathFromConnect(tdf.Connect)
  
End Sub

Public Function GetPathFromConnect(ByVal strConnect As String) As String
On Error GoTo ErrHandler
  Dim v As Variant
  Dim i As Integer
  
  v = Split(strConnect, ";")
  
  For i = 0 To UBound(v)
    If Left(v(i), 9) = "Database=" Then
      GetPathFromConnect = Mid(v(i), 10)
      Exit For
    End If
  Next i
  
ExitHere:
  Exit Function
ErrHandler:
  MsgBox "Error: " & Err & "-" & Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas