Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Visual Basic (Classic) FAQ


Create/Check various attributes in a database using DAO by HermanvLimbeek
Posted: 4 Jul 01

In DAO you can use the following function to check various attributes in a database:
Please note that myDB should be an opened database!

Public Function fnCheckTable(strTable As String, strCheck As String, intAttr)
'  strCheck = name of attribute to check
'  intAttr  = integer pointing to attribute
'              0 - field
'              1 - index
'              2 - relation
'              3 - tabledef
'              4 - allowzerolength = true
Dim td As TableDef
Dim Idx As Index
Dim fld As Field
Dim Rel As Relation
Dim Ok As Boolean
Dim n As Integer

Ok = False
n = 0
   Select Case intAttr
   Case 0   'Field
   Set td = MyDB.TableDefs(strTable)
   For Each fld In td.Fields
      If td.Fields(n).Name = strCheck Then Ok = True
      n = n + 1
   Case 1   'Index
   Set td = MyDB.TableDefs(strTable)
   For Each Idx In td.Indexes
      If td.Indexes(n).Name = strCheck Then Ok = True
      n = n + 1
   Case 2   'Relation
   For Each Rel In MyDB.Relations
      If MyDB.Relations(n).Name = strCheck Then Ok = True
      n = n + 1
   Case 3   'Tabledefs
   For Each td In MyDB.TableDefs
      If UCase(MyDB.TableDefs(n).Name) = UCase(strCheck) Then Ok = True
      n = n + 1
   Case 4   'AllowZeroLength property
   Set td = MyDB.TableDefs(strTable)
   For Each fld In td.Fields
      If td.Fields(n).Name = strCheck Then
         td.Fields(n).AllowZeroLength = True
         Ok = True
      End If
      n = n + 1

   td.Fields.Refresh      'if this results in an error then something did go wrong!

   End Select
fnCheckTable = Ok

End Function

Some samples of how to call the function (I omitted the declarations of variables):

   'Check if the table is already upgraded
   Set td = MyDB.TableDefs("Pricing")
   'create the necessary fields if they don't exist
   Ok = fnCheckTable("Pricing", "PackageID", 0)
   If Not Ok Then
      Set fld = td.CreateField("PackageID", dbLong)
      td.Fields.Append fld
   End If
   Ok = fnCheckTable("Pricing", "RecNum", 0)
   If Not Ok Then
      Set fld = td.CreateField("RecNum", dbInteger)
      td.Fields.Append fld
   End If
   'Create the index if it doesn't exist
   Ok = fnCheckTable("Pricing", "PricIdx", 1)
   If Not Ok Then
      'Create the index (two fields)
      Set NewIdx = td.CreateIndex("PricIdx")
      NewIdx.Primary = True
      NewIdx.IgnoreNulls = True
      NewIdx.Unique = True
      Set NewFld = NewIdx.CreateField("PackageID")
      NewFld.Required = True
      NewIdx.Fields.Append NewFld
      Set NewFld = NewIdx.CreateField("RecNum")
      NewFld.Required = True
      NewIdx.Fields.Append NewFld
      td.Indexes.Append NewIdx
   End If


Back to Visual Basic (Classic) FAQ Index
Back to Visual Basic (Classic) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close