Solved

How to Create a dbDecimal Field Using DAO

Posted on 2004-04-27
9
1,896 Views
Last Modified: 2007-12-19
Why does the following code:

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

always fail on error 3259 invalid field data type?
0
Comment
Question by:rmk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 10932542
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10932559
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
 

Author Comment

by:rmk
ID: 10932686
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rmk
ID: 10932858
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10933120
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
 

Author Comment

by:rmk
ID: 10933938
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10934014
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question