Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically creating fields (2002)

    I've written a (clumsy) procedure to read certain Outlook e-mails and store data into a new Access table. It constructs field names based on colon separators within the e-mails. For example 'Company : Bob Inc'. I'm having to maintain a long string and use InStr to see if the field name has already been appended. Is there not another way to see if a field name already exists in the table without having to loop through the fields collection? Would a better alternative be to just try and append all field names and use error handling to just let it fail? Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically creating fields (2002)

    You could use an error handler to append a field on the fly if it doesn't exist, then continue the code with Resume Next. If you look up CreateProperty in the VBA help, you'll find an example that can easily be adapted to create a Field instead of a Property.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically creating fields (2002)

    Based on this, I assume that there is not a way to directly tell if a table already exists in the database? So I can either loop to compare the table names(s) or try and create it and allow it to fail? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically creating fields (2002)

    As far as I know, you must use one of those methods. You could create a function FieldExists that uses one of them, so that you don't have to write it out each time. The following example requires a reference to the Microsoft DAO 3.6 Object Library:

    Public Function FieldExists(TableName As String, FieldName As String) As Boolean
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(TableName)
    For Each fld In tdf.Fields
    If fld.Name = FieldName Then
    FieldExists = True
    Exit For
    End If
    Next fld
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Function

    Use like this:

    If FieldExists("tblSomething", "MyField") Then
    ...
    Else
    ...
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •