Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24

    Unanswered: Test if column exists before adding it

    We are adding a column to a table using dbs.Execute with an alter table add statement. Is there any way to check whether the column exists first before adding it?

    thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Test if column exists before adding it

    Originally posted by ddevico
    We are adding a column to a table using dbs.Execute with an alter table add statement. Is there any way to check whether the column exists first before adding it?

    thanks
    The only way that I know of would be to query on the column and trap the subsequent error code ...

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Test if column exists before adding it

    Originally posted by ddevico
    We are adding a column to a table using dbs.Execute with an alter table add statement. Is there any way to check whether the column exists first before adding it?

    thanks
    If you want to use code you can loop through the fields collection of the tabledef object to see if it exists.

    Gregg

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Test if column exists before adding it

    Originally posted by basicmek
    If you want to use code you can loop through the fields collection of the tabledef object to see if it exists.

    Gregg
    DOH! I forgot about that ... "Earth to Mike, Is anyone home???"

  5. #5
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24
    thank you to both! Do you have any samples I might use?

    thanks!

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by ddevico
    thank you to both! Do you have any samples I might use?

    thanks!
    Using DAO. Lets say the tables name is table1 and the field name is field1. You could make it a function that returned true or false (Boolean) and pass the table and field name to it also. Lots of variations possible.

    Private Sub CheckFieldExists()

    Dim db as Database, tdf as Tabledef, fld as field

    Set db = CurrentDb
    Set tdf = db.Tabledefs("table1")

    For each fld in tdf.Fields

    If fld.Name = "field1"
    Do what you want to.
    Exit Sub
    End If
    Next


    If it gets this far, the field is not there.

    End Sub


    This is off of the top of my head so if I did something stupid don't be too harsh. It should serve as a rough example if you have some familiarity with working with DAO. There are equivelant techniques for ADO.

    Hope this helps.

    Gregg

  7. #7
    Join Date
    Sep 2003
    Location
    USA
    Posts
    24
    Thank you so much for the sample and direction(s) to work towards. So much better than throwing my book against the wall.......

    thanks again!

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Here's some ADO for those thinking of ADO migration or are just browsing:

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    Dim col As ADOX.Column

    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = cat.Tables(YourTableName)
    For Each col In tbl.Columns
    If col.Name = YourSearchedColumn Then
    ' Do what you want
    Exit For
    End If
    Next col

    Set tbl = Nothing
    Set cat = Nothing
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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