Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Create a dbDecimal Field Using DAO

Posted on 2004-04-27
9
Medium Priority
?
1,970 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 1000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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