Updating the Back End structure in VBA

[MarionD]

Hi all,

Would really appreciate it anyone could help me here!

I send out an update of my DB with conversion routine. In this sub I (amongst other things) also add fields to table.

Set td = db.TableDefs("tblxx_Eigenschaften")
With td
.Fields.Append .CreateField("Forderungsart", dbDouble)
.Fields.Append .CreateField("Gis_Mappe", dbText, 255)
.Fields.Refresh
End With

All I need to do now, is check if this field has already been defined, and if not create it. How can I check, within this "with" statement eg. if the field Gis_Mappe exists already?

Thanks a lot
Marion

 

[RoyVidar]

A "brute force" version, could be to just try adding it, if it already exists, it will throw an exception that you can handle/disregard. Say (non tested, probably need some errortesting also for the Else part)

Set td = db.TableDefs("tblxx_Eigenschaften")
With td
    on error resume next
    .Fields.Append .CreateField("Gis_Mappe", dbText, 255)
    select case err.number
    case 3191
       MsgBox "Ist schon da"
    Case Is <> 0
       MsgBox "Was anderes " & err.description
    Case Else
      .Fields.Append .CreateField("Forderungsart", dbDouble)    
      .Fields.Refresh
    End Select
End With

 

[MarionD]

Thanks Roy - will try your advice- thought there might be a more elegant version of checking! Will Microsoft not change the error codes at some stage?

Thanks for the trouble!
Marion

 

[RoyVidar]

Yes, there are other ways of testing, one could for instance use the OpenSchema method of an ADO connection, say

dim rs            as adodb.recordset
set rs = TheConn.OpenSchema(adSchemaColumns, _
                 Array(Empty, Empty, "TheTable", "TheColumn"))
if rs.eof then
   ' doesn't exist, create
else
   ' exist, do nothing
end if

' or, you could loop the fields collection of the tablefed object

dim fld           as dao.field
...

for each fld in td.fields
    if fld.name = "TheColumn" then
        ' doesn't exist, create, then exit for
    end if
next fld

You could probably open either ADO/DAO recordset and loop their fields collection, use ADOX...

I'll agree avoiding "brute force" seems a bit more elegant, but then, if you count in both declaration, instantiation, looping/manipulation, selections, cleanup - then it will sometimes take more time to execute than using "brute force", but of course, a matter of preference

I don't think errorcodes will change, but some make a point of either per module level, or globally, declare constants (for instance for errorcodes and validation errors), so that one only have to change one place, should it change.

 

[MarionD]

Aha... the "brute force" appeals to me more and more!!

Thanks again
Marion