Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2032
  • Last Modified:

How to Create a dbDecimal Field Using DAO

Why does the following code:

Set fld = tdf.CreateField("QtyUsed", dbDecimal)

always fail on error 3259 invalid field data type?
0
rmk
Asked:
rmk
  • 4
  • 3
1 Solution
 
Steve BinkCommented:
Because you cannot create a Decimal data type.  Decimal is a sub-type only of type Variant.  You can convert to this sub-type from other types, but you cannot explicitly declare a decimal type variable.  I recommend using type Double instead.
0
 
Steve BinkCommented:
From the VB help file regarding Decimal type variables:

Note   At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

From same regarding the CDec() function:

Note   The CDec function does not return a discrete data type; instead, it always returns a Variant whose value has been converted to a Decimal subtype.
0
 
rmkAuthor Commented:
I think I have to disagree with you on this one because, as I see it, this has nothing to do with a VBA data type. Since Access has a Decimal data type, I should be able to create a database field (not a VBA variable) of that data type. From the VB help file for CreateField it clearly shows dbDecimal as an acceptable data type.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rmkAuthor Commented:
FYI, this is a VBA dump of the properties collection for tblPODtlTecd.Qty that I defined in the Access Table Desing window as Decimal with Precision 12 and Scale 2. The Type=20 corresponds to the value of dbDecimal. I'm not sure I understand why DecimalPlaces=255 and I'm not sure where to find the values for Precision and Scale.

Value=
Attributes=2
CollatingOrder=12
Type=20
Name=Qty
OrdinalPosition=3
Size=16
SourceField=Qty
SourceTable=tblPODtlRecd
ValidateOnSet=
DataUpdatable=False
ForeignName=
DefaultValue=0
ValidationRule=
ValidationText=
Required=True
AllowZeroLength=False
FieldSize=
OriginalValue=
VisibleValue=
ColumnWidth=-1
ColumnOrder=0
ColumnHidden=False
DecimalPlaces=255
DisplayControl=109
0
 
Steve BinkCommented:
That's just a matter of semantics.  Access does not have a Decimal data type.  It has a Decimal field SIZE for the Number data type.  In essence, a sub-type of "Number".  In VB, this sub-type is handled by a sub-typed variant object.  

You are correct in that the help file states dbDecimal is an appropriate type constant for CreateField.  At least, it directs you to look at the data type overview page, on which Decimal is listed.  With my own experimentation, I have yet to see it work.  Microsoft has no other comment on the matter that I've found, but everything in the help file points to the idea that Decimal is not a valid data type for declaration.  Granted, this applies to VARIABLES in VB, but it may hold over for field declarations also.  Maybe it's a bug.  :)  In the Access help file, search under the Answer Wizard for this phrase: "Field data types available in Access".  Go to the help topic with the same title.  Notice that Decimal is absent...it is considered a field size for the Number data type, not a data type in of itself.

The only other information I found is that Jet 4.0 uses Decimal data types, where Jet 3.5 does not.  The DAO3.6 library is supposed to use Jet 4.0, and that is what I have loaded, but dbDecimal still returns an error.  I think you will be limited to using another, more standard, data type for your column, such as Double or Currency.  If you do get it to work, please post your solution here.
0
 
rmkAuthor Commented:
OK - so I giv eup trying to do this in DAO. Now I'm trying to do it in ADO with a connection and SQL statements. For example:

    strSQL = "CREATE TABLE " & strTable & " " _
           & "(" _
           & "PartBOMID INTEGER NOT NULL CONSTRAINT PK_" & strTable & " PRIMARY KEY," _
           & "ParentStockID INTEGER NOT NULL," _
           & "StockID INTEGER NOT NULL," _
           & "QtyUsed DECIMAL(12,2) NOT NULL DEFAULT 0," _
           & "CreatedByUser TEXT(50) NULL," _
           & "CreatedByComputer TEXT(50) NULL," _
           & "CreatedDateTime DATETIME NULL DEFAULT Now()," _
           & "UpdatedByUser TEXT(50) NULL," _
           & "UpdatedByComputer TEXT(50) NULL," _
           & "UpdatedDateTime DATETIME NULL," _
           & "UpdatedCounter INTEGER NOT NULL DEFAULT 0" _
           & ")"
    rcnn.Execute strSQL

This all works well, but now I'm stuck with how to set the custom Format property of the CreateDateTime column to be "mm/dd/yyyy ttttt". Do I have to revert back to DAO and create a custom property for this column? I'd sure like to stick to one technology or another.
0
 
Steve BinkCommented:
Why not run the construction in ADO, back out, see how Access generated the field (type, sub-type, etc), then duplicate that structure with a DAO sub instead?  That way you have will have what Access considers the 'correct' method of declaring a Decimal data type, and you can still set the other properties you need.  

Access CAN handle Decimal types...the question is how does Access expect to see them...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now