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

Posted on 2003-02-28
Medium Priority
Last Modified: 2008-01-16
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
Question by:TT2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Expert Comment

ID: 8042763
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!

Accepted Solution

i014354 earned 400 total points
ID: 8042818
Here's the Checkbox property using my example above:

SetAccessProperty tdfNew.Fields("PaymentsGenerated"), "DisplayControl", dbInteger, acCheckBox

Author Comment

ID: 8043826
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.

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 8045011

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.


Author Comment

ID: 8078376
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.


Expert Comment

ID: 8079398
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.

Author Comment

ID: 8080179
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.


Expert Comment

ID: 8081182
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.

Author Comment

ID: 8081298

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question