Full Version: How to use .DateModified with a table?
UtterAccess Forums > Microsoft® Access > Access Forms
MrSiezen
Sounds pretty simple, but I'm messing this up pretty badly.
How can I get the Date Modified of a table?
cheekybuddha
Morning MrSiezen,
IRC the DateModified refers to the last Design change to the table and is probably read-only.
Is that what you want?
d
MrSiezen
Morning! Hmm good one...
Well DateCreated is also good info for me. I just need to check if the temporary table is created today, or if it is old.
So eh, how can you check that then heh....
kuifjexx
Does this work?
CODE
Sub AllTables()
nbsp;   Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
    With obj
        Debug.Print .Name & " - Created " & .DateCreated & " - Modified " & .DateModified
    End With
    Next obj
End Sub


List all tables with date modified & Date Created
Greetz from Belgium compute.gif

Luc aka Kuifjexx
Dial222
Do you need to check the TableDef.LastUpdated flag? or .DateCreated?
cheekybuddha
CODE
Function fCreatedModified(strTblName As String) As String
nbsp;   Dim strRet as String
    strRet = "Created: " & CurrentDb.TableDefs(strTblName).DateCreated
    strRet = strRet & "   Modified: " & CurrentDb.TableDefs(strTblName).LastModified
    fCreatedModified = strRet
End Function

hth,
d
cheekybuddha
Oops!
astModified = LastUpdated
!!
d
MrSiezen
Thx guys, got it to work!
bit on topic: How can I check if a table exists in the current db?
I'm clearly missing some experience in this working area!
kuifjexx
Thx for the function, was completely on the wrong track ... wink.gif
reetz from Belgium compute.gif
Luc aka Kuifjexx
kuifjexx
How can I check if a table exists in the current db?
Add this to a global module
CODE
Public Function fncObjectExists(strObjectType As String, strObjectName As String) As Boolean
    ' Comments   :  Pass the Object type: Table, Query, Form, Report, Macro, or Module & Pass the Object Name
On Error GoTo Err_fncObjectExists
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
    
     Set db = CurrentDb()
     fncObjectExists = False
    
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.name = strObjectName Then
                    fncObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.name = strObjectName Then
                    fncObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).name = strObjectName Then
                    fncObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).name = strObjectName Then
                    fncObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
Exit_fncObjectExists:
    Exit Function
Err_fncObjectExists:
    MsgBox Err.Description & "-" & Err.Number
    Resume Exit_fncObjectExists
End Function

o your code becomes
CODE
if fncObjectExists("query","yourqueryl") then
.... 'your code
else
.... 'your code
endif

HTH
Greetz from Belgium compute.gif
Luc aka Kuifjexx
cheekybuddha
Probably the easiest way is to use an error (though a bit ugly!)
CODE
Function fTblExists(strTblName as String) as Boolean
On Error Resume Next
nbsp;   With CurrentDb.TableDefs(strTblName)
    End With
    fTblExists = (err = 0)
End Function

If the table is not in the TableDefs collection then it will produce an error.
hth,
d
cheekybuddha
Luc has given you the 'Proper' way to do it! wink.gif
d
MrSiezen
Thx! Strange that Access doesn't have a build in function for this!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.