I am trying to check a field in a table to see if the field exist or not. If the table does NOT exist then I plan to add it to the table.

I have code that works to append the field to the table but I don't know how to first check the table for the field. =(

I am using Visual Basic 6 and my database is MS Access 2000.

I know my code should look something like this:

************CODE*********************
Dim tdf As TableDef
Dim fld As Field

if <<check to see if DOP (field) is in table (Table_Item_Details) then
' Field NOT in table so appened the table
With gDBS
Set tdf = .TableDefs("Table_Item_Details")
tdf.Fields.Append tdf.CreateField("DOP", dbDate)
End With
End If
************CODE END*******************

Note that gBDS is defined elsewhere in my code. It is a global variable defined as a Database. DOP = Date of Purchase.

Any help anyone can provide would be appreciated. I have looked all over the VB help files and can not seem to find a way to check this.

There are usually two ways to do this sort of thing:

• There should be some way provided in which you can enumerate a list of fields (e.g. a Visual Basic "collection"), or to attempt to access a field "if it exists" without throwing an exception if it does not.
• Another way, sometimes just as convenient, is to simply go ahead attempt the operation using the field-name provided, and prepare to catch the exception that will be thrown if the field-name is not valid. (In Visual Basic, the on error goto statement.)

Which one to use? Well, how "optimistic" are you that a particular entry is probably-correct? If you're optimistic, then the on error goto approach avoids the additional overhead of retrieving a field-list (maybe... and maybe not). Also consider the fact that "you can't always anticipate which exceptional conditions might occur, but you can always detect when an exception just did occur." As usual there is no bright-line rule for making this decision; just things to think about in your design.

I know FOR a fact that the 1st time the code is read/done there WILL NOT the a field in the table, thus I will have to create it. However, the next time the code is run it WILL be there (99% sure unless off course the user opens the DB manually and removes it). I like the on erro goto routine, but how exaclty do I write that code?

Thanks

Basically ... the layout is something like this:The on error goto statement sets a trap that will go off when any subsequent error occurs in the subroutine. If an error does happen, the error-handling routine (at DidntWork) is executed, but Basic is in a kind of a "limbo state" until the resume statement is executed. So we really only analyze the condition and then resume. (And yes, we design the code so that you can't "fall into" the error-handling section.)

The whole construct is wrapped in a single subroutine or function basically as a matter of style... since VB's entire error-handling logic is based on goto, you don't want those goto's going willy-nilly.

Create an ado recordset object - loop through the fields collection to verify that the field does not exist or you can trap on error 3265(always risky - I prefer the proactive method). Once you have verified that it does not exist then you can use the "alter table ... add column" sql statement.

thanks! I'm going to try this later this week and see if it works!
I've got too much work right now. =(

THANK YOU ALL!!!!
It's working!!!! It's working!!!!
You guys are the best!

Here is my code:

Public Sub AddFields2Table_Ver129()
' *************************************
' THIS SECTION ADDED IN VERSION 1.2.9
' This sub will appened Table_Item_Details and V122_Table_Item_Details_Tmp
' so that they have the "DOP" field
Dim tdf As TableDef
Dim fld As Field

On Error GoTo AllReadyInDB

' Table_Item_Details
With gDBS
Set tdf = .TableDefs("Table_Item_Details")
tdf.Fields.Append tdf.CreateField("DOP", dbDate)

' V122_Table_Item_Deatils_Tmp
Set tdf = .TableDefs("V122_Table_Item_Details_Tmp")
tdf.Fields.Append tdf.CreateField("DOP", dbDate)
End With

AllReadyInDB:
' This label is used to get out of sub if error occurs.
' Error will occur is this sub attempts to add the SAME field the the table
' and that field is ALREADY there
End Sub
' *************************************