Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a column definition with VBA (Access 2K)

    I have a situation where I need to dynamically add a small missing table definition to an MDB file, and a second case where I need to dynamically add a missing column definition to an existing table. Using well documented techniques from the Visual Basic Programmer's Guide, I can quite successfully add the table definition, including its three columns:

    dim tbl as ADOX.Table
    Set tbl = New ADOX.Table
    tbl.Name = "DataVersion"
    With tbl.Columns
    .Append "MajorVersion", adUnsignedTinyInt
    .Append "Revision", adUnsignedTinyInt
    .Append "Patch", adUnsignedTinyInt
    End With
    catDBData.Tables.Append tbl
    catDBData.Tables.Refresh

    In the second case, where I merely wish to add a column to a pre-existing table, I have tried two different variations on the above code, without success:

    Variation #1:
    Set catDBData = New ADOX.Catalog
    catDBData.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDataPath
    Set tbl = catDBData.Tables(strTable) ' Set ADOX table ptr
    On Error GoTo colAppendErr
    tbl.Columns.Append strColName, intColType ' Set col Name & Datatype
    tbl.Columns.Refresh
    catDBData.Tables.Refresh

    Variation #2:
    Set catDBData = New ADOX.Catalog
    catDBData.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDataPath
    Set tbl = catDBData.Tables(strTable) ' Set ADOX table ptr
    On Error GoTo colAppendErr
    Set col = New ADOX.Column
    col.Name = strColName
    col.Type = intColType
    tbl.Columns.Append col
    tbl.Columns.Refresh
    catDBData.Tables.Refresh

    In both variations, there is never any error (the On Error branch is not taken), yet at the end of the operation, the new column definition is NOT in the table.

    Have I run into a restriction in VBA and/or ADOX? Are column definitions only allowed for new table definitions? I have been unable to find any documentation that prohibits such actions.
    If not prohibited, then what am I doing wrong?

    Thanks, Jim

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

    Re: Add a column definition with VBA (Access 2K)

    Both variations of the code work on my system (Access 2002 SP-2) if I substitute valid names and paths, so it seems to be correct in itself. I was able to add fields of various types to an existing table without problems.

    Can you find anything unusual if you single step through the code?

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a column definition with VBA (Access 2K)

    Hans, Thank you! While composing a message of frustration back to you, I started looking for any other anomolies. Found it!

    It turns out that I had the target table LINKed at the time I was appending the new column. No real reason to do so; just did. So, I killed the LINK, and now the append works perfectly.

    Thanks again.

    -- Jim

Posting Permissions

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