Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Formatting Fields Created in VBA (Using DAO) - Access 2K

I'm working on updating an existing Access 2000 back-end database (mdb) using DAO 3.6. It is part of an application that is installed on users PCs/Networks nationwide.  I have virtually completed version 2 of the application and would like to iron-out a couple of inconsistencies.

I can create Tables, Fields, Indexes & Relations from the information I've gathered from EE, Access Developer's Handbook, etc.  However, when I create a field, say using the code below, I can't find a way to (1) Format the Field programmatically (eg "000000" for formatting ID fields) or (2) if the field is Boolean, format/make it appear as a Check Box in the table after it has been created (it just appears as a Text Field with 0 for False and -1 when altered by a bound Check Box on a Form).  The essential extracts of code for these two problems are labelled (1) and (2) below:

Function CreateTables()
    Dim WS As DAO.Workspace
    Dim DBS As DAO.Database
    Dim rel As DAO.Relation
    Dim rst As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim flda As DAO.Field
    Dim idx As DAO.Index
    Dim fldidx As DAO.Field
    Dim fldaidx As DAO.Field
    Dim fldbidx As DAO.Field
    Dim fldcidx As DAO.Field
    Dim prp As DAO.Property
    Dim strDBPWD As String
    Dim strDelField As String
'***    Provide Password if Necessary for specific BE database – strDBSPath is a Public Variable
    strDBPWD = "plantpot"    Set WS = DBEngine.Workspaces(0)
        Set DBS = WS.OpenDatabase(strDBSPath & "TTLU_be.mdb", True, False, "MS Access;PWD=" & strDBPWD)
    'LU Funding Body Table >>>
            Set tdf = DBS.CreateTableDef("LU Funding Body")
        '(1)Create ID Primary Key Field
                Set fld = tdf.CreateField("ID", dbLong)
                    fld.Attributes = dbAutoIncrField
                    fld.OrdinalPosition = 0
                tdf.Fields.Append fld
                Set idx = tdf.CreateIndex("PrimaryKey")
                    Set fldidx = idx.CreateField("ID", dbLong)
                    idx.Fields.Append fldidx
                    idx.Primary = True
                tdf.Indexes.Append idx
.(Irrelevant Code Taken Out)
        '(2)Create PaymentsGenerated Field
                Set fld = tdf.CreateField("PaymentsGenerated", dbBoolean)
                    fld.OrdinalPosition = 26
                tdf.Fields.Append fld
                Set idx = tdf.CreateIndex("idxPaymentsGenerated")
                    Set fldidx = idx.CreateField("PaymentsGenerated", dbBoolean)
                    idx.Fields.Append fldidx
                    idx.Unique = False
                tdf.Indexes.Append idx
            DBS.TableDefs.Append tdf
    Set prp = Nothing
    Set fldcidx = Nothing
    Set fldbidx = Nothing
    Set fldaidx = Nothing
    Set fldidx = Nothing
    Set idx = Nothing
    Set flda = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set rst = Nothing
    Set rel = Nothing
    Set DBS = Nothing
    Set WS = Nothing
End Function

Whilst I can make the updates work with the way I’ve updated the back-end tables, it really niggles me that I can’t seem to achieve quite the same results as I can when manually creating fields.

If anyone has a solution/workaround to achieve this, I would really like to know.


Glen Ashton
  • 5
  • 4
1 Solution
Here's a function to set the Field properties:

Function SetAccessProperty(obj As Object, _
    strName As String, intType As Integer, _
    Optional varSetting As Variant) As Boolean

    Dim prp As Property
    Const conPropNotFound As Integer = 3270
    On Error GoTo ErrorSetAccessProperty
    obj.Properties(strName) = varSetting
    SetAccessProperty = True
    Exit Function
    If Err = conPropNotFound Then
      Set prp = obj.CreateProperty(strName, intType, varSetting)
      obj.Properties.Append prp
      SetAccessProperty = True
      Resume ExitSetAccessProperty
      MsgBox Err & ": " & vbCrLf & Err.Description
      SetAccessProperty = False
      Resume ExitSetAccessProperty
    End If
  End Function

To format your ID field, use the following call:

  Dim dbs As DAO.Database
    Dim tdfNew As TableDef
    Dim strTableName As String
   Set dbs = DBEngine(0).OpenDatabase("C:\Documents and Settings\be\My Documents\db2.mdb")
    Set tdfNew = dbs.TableDefs("LU Funding Body")
    SetAccessProperty tdfNew.Fields("ID"), "Format", dbLong, "00000000"

I'm looking into the other problem with Boolean fields next!
Here's the Checkbox property using my example above:

SetAccessProperty tdfNew.Fields("PaymentsGenerated"), "DisplayControl", dbInteger, acCheckBox
TT2Author Commented:
Thanks for this  i014354 , I'm going to check it out in the next hour or so.  It looks good... Thanks... I'll come back and award points as soon as I've tested it out.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

TT2Author Commented:

I've set up the SetAccessProperty function and implemented the calls.  

The first call to format the Funding Body ID field doesn't fall over but it doesn't actually set the format property as "000000" - it's still blank.

However, the function call to set the Check Box works superbly.

There must be some minor problem with the Format call - I've tried for the past 3 hours-or-so to tweak/test various things but I can't discover what the problem is.  Could it be that formatting needs to be done before the index is set, or something like that?

The code seems good but these sort of things are usually sorted by something minor.

Have you got any ideas?

I'll up the points if this turns out to be complex.

Thanks for all your help.

TT2Author Commented:
Hi i014354

I've awarded the points for your check box answer - It worked well.  However, I can't seem to get the field formatting to work, using the same function.... Any ideas? I've tried for hours to tweak/reposition the function call/modify the code... to no avail.

Hmmmm... seemed to work fine for me.

I'm also using Access 2000 so I'm not quite sure what the issue might be.  I'll have a think about it, as well as double-checking my DAO version and code above.
TT2Author Commented:
Thanks i014354

It doesn't cause any errors as far as I can see but when I open the back-end, there's nothing in the Format Property of the field - I could send a copy of the mdb if you would like to test it.

I'm on the road this week.  If you'd like to send a copy of the mdb along, I'll have a look at it over the weekend. Send to elliottwh@yahoo.com.
TT2Author Commented:

I'll package everything up tomorrow with the old BE file and the Table Updating "mdb". I'm working from home for the day.



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now