Solved

How to Create a dbDecimal Field Using DAO

Posted on 2004-04-27
9
1,783 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
  • 4
  • 3
9 Comments
 
LVL 50

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 50

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 50

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 50

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now