Date/Time data types

[Pat]

I want to write a pogram to go through each table, loop through all fields, choose the date/time fields and test any date values where the year < 1000 or > 2100.
So i need to test if a field is a date field, how do i do this?
I also need to test a field for a PK so i can save that too.

Looping through each table is straight forward enough, its the other two problems im after.

[HansV]

Are the tables stored in the Access database or in SQL Server?

[Pat]

Access

[HansV]

You can use DAO:

Code: Select all

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Set dbs = CurrentDb
  For Each tdf In dbs.TableDefs
    If Not tdf.Name Like "MSys*" And Not tdf.Name Like "~*" Then
      For Each fld In tdf.Fields
        If fld.Type = dbDate Then
          Debug.Print tdf.Name, fld.Name
        End If
      Next fld
    End If
  Next tdf

[HansV]

And here is code to list the primary key fields. Note that a primary key may consist of more than one field.

Code: Select all

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  Set dbs = CurrentDb
  For Each tdf In dbs.TableDefs
    If Not tdf.Name Like "MSys*" And Not tdf.Name Like "~*" Then
      For Each idx In tdf.Indexes
        If idx.Primary Then
          For Each fld In idx.Fields
            Debug.Print tdf.Name, idx.Name, fld.Name
          Next fld
        End If
      Next idx
    End If
  Next tdf

[Pat]

Thank you Hans, your blood is worth bottling.