Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Check for a specific field in a database

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    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

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    Basically ... the layout is something like this:
    Code:
    Function doSomething {returns True or False}
    ...
    success = False
    On Error Goto DidntWork
    ... attempt the operation ...
    success = True
    
    GoHome:
    return success
    
    DidntWork:
    ... check the error-code ...
    success = False
    resume GoHome
    ...
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    thanks! I'm going to try this later this week and see if it works!
    I've got too much work right now. =(

  7. #7
    Join Date
    Jan 2004
    Posts
    4
    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
    ' *************************************

Posting Permissions

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