copying field properties

dylanerich · April 4th, 2011, 12:11 PM

Hi,

I am trying out an example on page 377 and I notice that only a couple of field properties are included for creating new fields. The two properties that come up are .Required and .AllowZeroLength.

Well, my question is, if I am trying to synchronise fields of two tables from two different databases, is there an easy way to cover all the properties of a particular field.

For example: there is a tblCustomer table in DB1.accdb and tblCustomer table in DB2.accdb. I add an email field in tblCustomer of DB1, which is text, i specify validation property, format, size etc...

I now want to write a general module, which will read fields from both tables, find the differences in their design and add the extra fields in the tblCustomer of DB2 (with ALL the field properties)

What is the best method for doing this?

malcolmdixon · April 4th, 2011, 01:27 PM

You need to enumerate the Properties collection. Here's a code snippet that may help.

Code:

Public Sub ListFieldProps(table As String)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    
    Set db = CurrentDb()
    
    On Error Resume Next
    Set td = db.TableDefs(table)
    
    If Err.Number = 0 Then 'table OK
        For Each fld In td.Fields
        Debug.Print fld.Name
            For Each prp In fld.Properties
                Debug.Print Chr(9) & prp.Name & " = "; prp.Value
            Next
        Next
    End If
    
    Set td = Nothing
    
End Sub

HTH.

Malc.

dylanerich · April 4th, 2011, 09:14 PM

hi Malcolm,

thank you very much. that is exactly what i was looking for.

cheers

dylan