0

I am attempting to add a series of fields to a Access 2016 Table, but keep encountering the error:

Runtime error '3259'
Invalid field data type

I originally specified the data type as dbNumeric but changed it to dbDecimal to see if that made a difference. The solution given here for CreateFields did not solve my problem, though I did not try the SQL. It did not. Here is the code:

Sub BOD_Variables()

    Dim myDBS
    Dim myTable As TableDef
    Dim myTableName As String

    myTableName = "BOD_Data"
    Set myDBS = CurrentDb
    Set myTable = myDBS.TableDefs(myTableName)

    Dim myField As Field
    Dim myVariableNames As Variant

    myVariableNames = Array("Blank_4_SampleVol", ... "BOD_ Concentration _OUT")

    Dim iCount As Integer

    For iCount = LBound(myVariableNames) To UBound(myVariableNames)
    Debug.Print myVariableNames(iCount)

        Set myField = myTable.CreateField(myVariableNames(iCount), dbDecimal) 'Originally specified dbNumeric for data type.
        myTable.Fields.Append myField
    Next  
End Sub

I attempted to replace the call to the Array(index) with: 

        Set myField = myTable.CreateField("Blank_4_SampleVol", dbNumeric)

Still get the same error.

I tried specifying the length as discussed here, but that did not correct problem. Documentation on CreateField says it ignores field length when field type is dbNumberic.

Any ideas of what I am missing? Thanks in advance.

Community
  • 1
  • 1
PhillipOReilly
  • 609
  • 12
  • 28
  • How far into the for loop do you get before the error? Need more info on the debugging you've done. – CatCatMcMeows Jul 27 '16 at 18:00
  • 1
    I think using 'dbDouble' will more than meet your needs and should execute without error - no need for specifying length – dbmitch Jul 27 '16 at 18:50
  • 1
    Note that your code is using DAO objects (e.g., `TableDef`) whereas the (working) code in my answer is using ADOX objects. – Gord Thompson Jul 29 '16 at 21:20
  • 1
    Sorry, took couple days off. It chokes on first iteration. @dbmitch, tried your simple approach and that worked! If you put it as an answer, I will select it as THE answer. – PhillipOReilly Jul 29 '16 at 21:40
  • No problem - looks like someone closed this question off so I can't add an answer. You can just upvote my comment if you want. – dbmitch Jul 29 '16 at 21:44

2 Answers2

1

While it remains true that DAO does not seem to expose the required properties to create a Decimal field, even if we try using a DAO.Field2 object, the following ADOX code does create a Decimal field (tested with Access 2010):

Option Compare Database
Option Explicit

Sub AddNewDecimalField()
    Dim cat As New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Dim tbl As ADOX.Table
    Set tbl = cat.Tables("MyTable")
    Dim col As New ADOX.Column
    col.Name = "MyNewDecimalField"
    col.Type = adNumeric  ' note: not adDecimal
    col.Precision = 18
    col.NumericScale = 8
    tbl.Columns.Append col
End Sub

Or, we could just use a DDL query ...

CurrentProject.Connection.Execute _
        "ALTER TABLE MyTable ADD COLUMN MyNewDecimalField DECIMAL(18,8)"

... as Andre suggests in his answer.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Gord, thanks for your time. I suspect I will use your more sophisticated approach in the future. I have a bit of a newbie question: how do I implement or reference ADOX? Access is interpreting ADOX.Table as a user-defined type right now. Thanks, Phillip – PhillipOReilly Jul 29 '16 at 21:44
  • In the VBA development environment you need to do `Tools > References...` and add a reference for "Microsoft ADO Ext. 6.0 for DDL and Security". Note that your specific question is something of special case: Most of the time DAO offers more complete coverage of Access features than ADOX does, it's just that support for `Decimal` fields is strangely lacking in DAO. – Gord Thompson Jul 29 '16 at 21:57
0

Follow the link in your linked question, and read the footnote #7 for DECIMAL:

[7] Not available in the Access query interface or DAO. Use ADO to Execute the DDL query statement.

So you'd use something like this:

strSQL = "ALTER TABLE myTable ADD COLUMN " & myVariableNames(iCount) & " DECIMAL (20,6);"
CurrentProject.Connection.Execute strSQL

Decimal fields have had (see comment below) their issues, see Avoid Using Decimal Field Size in Microsoft Access Tables or http://allenbrowne.com/bug-08.html

Long or Double or Currency may be better options.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 1
    re: "Decimal fields have their issues" - Both of those references are quite out of date (circa Access 2000 and 2003). Access 2010 does not have a problem sorting Decimal values, and exporting to text does not fail, although it does export the values with string delimiters. Allen's contributions to the Access community are legendary - and most deservedly so - but he has been retired for many years now and things that were true five (or more) versions ago are not necessarily true today. – Gord Thompson Jul 28 '16 at 02:20
  • Thank you for this information, @Gord! – Andre Jul 28 '16 at 06:11