J
Jamie Collins
The purpose of this post is to consider whether the following statement
provides a reason to avoid the Jet 4.0 DECIMAL type in Access:
"VBA has no Decimal data type. Decimal as a subtype of Variant is vague
and inefficient, and there is no way to declare a constant of type
Decimal."
The most logical way I can think of to test this assertion is to come
up with a kind of 'null hypothesis' e.g.
"Uptake of the Jet 4.0 DECIMAL type in Access would increase if there
was a specific and efficient Decimal data type in VBA."
I think the above statement is incorrect and will use another data type
to try to demonstrate why.
Consider Jet's CHARACTER - I'll refer to as CHAR(N) - data type, being
fixed-width text e.g. CHAR(10) will always store/display 10 characters,
padding with spaces to the right where necessary. Usage example in Jet
SQL code:
CREATE TABLE Books (
isbn CHAR(10) NOT NULL PRIMARY KEY,
....
);
INSERT INTO Books (isbn) VALUES ('0672328488')
;
Further consider VBA's String * N data type, also being fixed-width
text. Usage example in VBA code:
Dim isbn As String * 10
isbn = "0672328488"
So VBA has an explicit (non-vague) data type equivalent to the CHAR(N)
data type. But is it efficient?
In VBA there is a fundamental difference between String and String * N.
Quoting Matt Curland:
"Types fall into two categories: In-line types and pointer types.
In-line types include [...] fixed-length strings. These types store
their data at the position they are defined... Pointer types (which
include [...] strings) are variable-length structures that store their
data in a different memory location. A 32-bit pointer to this memory
(equivalent to four bytes, or one Long variable) is then stored where
the variable is defined... When VB copies an in-line type, it simply
copies the data...VB always makes a deep copy when assigning a [pointer
type] string [...] variable. Deep copies are very expensive in terms of
performance and memory" (Advanced Visual Basic 6, P3-4).
In summary, because String * N is an inline type it is an efficient
type and a String * N variable will be more efficient than the
equivalent String variable.
So VBA has an explicit (non-vague) and efficient data type equivalent
to the CHAR(N) data type. How has this affected uptake of the CHAR(N)
data type in the Access community?
Based on regular reading of the Access groups (notably
microsoft.public.access.tablesdbdesign) there would appear to be little
evidence to suggest it is being used frequently. CHAR(N) is usually
mentioned in the context of another DBMS (e.g. ODBC linked table or
import), usually SQL Server (e.g. in a ADP).
As regards String * N in VBA, again I've seen them used only rarely in
the Access newsgroups and then as an 'added interest' point e.g. a code
example made up on the spot rather than ready-rolled 'real life' code.
Considering the above, I do not see a correlation between CHAR(N) usage
and String * N usage. Further, I don't consider the existence of the
String * N type has had a positive effect on the uptake of the CHAR(N)
type in Access.
VBA's Decimal type is a pointer type. In lieu of any details from
Microsoft about the VBA Decimal type, here is a description of a COM
DECIMAL:
"A DECIMAL fits in 16 bytes - exactly the same as a VARIANT. It uses
two bytes that are reserved (used for the VT_DECIMAL tag), a byte for
the scale, a byte for the sign, and the remaining 12 bytes (96 bits)
for the integer number. Adding another 32 bits to the integer means you
can handle numbers with 28 or 29 decimal digits"
Dr. GUI and COM Automation, Part 3: More on COM's Fabulous Data Types
(http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarguion/html/drgui042099.asp)
Forgive me (and correct me <g>) if I'm making a wrong assumption here
but that sounds like a good reason for making the VBA Decimal a
reference type, so it was always going to be 'inefficient' due to its
potential size. Exactly why it is a Variant subtype, hence tempting the
label 'vague' to be applied, rather than a type in its own right I
cannot say.
Even if were possible to implement an in-line Decimal type for VBA I
think the String * N experience demonstrates that it would not
significantly affect the uptake of the DECIMAL type in Access.
As regards a Decimal constant, I assume it best to declare as a String,
fixed length of course <g> e.g.
Const DEC_VALUE As String * 21 = "0.1234567890123456789"
Dim d
d = CDec(DEC_VALUE)
MsgBox d
In conclusion, I do not consider the Decimal type as implemented for
VBA as being a bar to the use of the Jet 4.0 data type in Access.
Feedback on any point made above is encouraged.
Jamie.
--
provides a reason to avoid the Jet 4.0 DECIMAL type in Access:
"VBA has no Decimal data type. Decimal as a subtype of Variant is vague
and inefficient, and there is no way to declare a constant of type
Decimal."
The most logical way I can think of to test this assertion is to come
up with a kind of 'null hypothesis' e.g.
"Uptake of the Jet 4.0 DECIMAL type in Access would increase if there
was a specific and efficient Decimal data type in VBA."
I think the above statement is incorrect and will use another data type
to try to demonstrate why.
Consider Jet's CHARACTER - I'll refer to as CHAR(N) - data type, being
fixed-width text e.g. CHAR(10) will always store/display 10 characters,
padding with spaces to the right where necessary. Usage example in Jet
SQL code:
CREATE TABLE Books (
isbn CHAR(10) NOT NULL PRIMARY KEY,
....
);
INSERT INTO Books (isbn) VALUES ('0672328488')
;
Further consider VBA's String * N data type, also being fixed-width
text. Usage example in VBA code:
Dim isbn As String * 10
isbn = "0672328488"
So VBA has an explicit (non-vague) data type equivalent to the CHAR(N)
data type. But is it efficient?
In VBA there is a fundamental difference between String and String * N.
Quoting Matt Curland:
"Types fall into two categories: In-line types and pointer types.
In-line types include [...] fixed-length strings. These types store
their data at the position they are defined... Pointer types (which
include [...] strings) are variable-length structures that store their
data in a different memory location. A 32-bit pointer to this memory
(equivalent to four bytes, or one Long variable) is then stored where
the variable is defined... When VB copies an in-line type, it simply
copies the data...VB always makes a deep copy when assigning a [pointer
type] string [...] variable. Deep copies are very expensive in terms of
performance and memory" (Advanced Visual Basic 6, P3-4).
In summary, because String * N is an inline type it is an efficient
type and a String * N variable will be more efficient than the
equivalent String variable.
So VBA has an explicit (non-vague) and efficient data type equivalent
to the CHAR(N) data type. How has this affected uptake of the CHAR(N)
data type in the Access community?
Based on regular reading of the Access groups (notably
microsoft.public.access.tablesdbdesign) there would appear to be little
evidence to suggest it is being used frequently. CHAR(N) is usually
mentioned in the context of another DBMS (e.g. ODBC linked table or
import), usually SQL Server (e.g. in a ADP).
As regards String * N in VBA, again I've seen them used only rarely in
the Access newsgroups and then as an 'added interest' point e.g. a code
example made up on the spot rather than ready-rolled 'real life' code.
Considering the above, I do not see a correlation between CHAR(N) usage
and String * N usage. Further, I don't consider the existence of the
String * N type has had a positive effect on the uptake of the CHAR(N)
type in Access.
VBA's Decimal type is a pointer type. In lieu of any details from
Microsoft about the VBA Decimal type, here is a description of a COM
DECIMAL:
"A DECIMAL fits in 16 bytes - exactly the same as a VARIANT. It uses
two bytes that are reserved (used for the VT_DECIMAL tag), a byte for
the scale, a byte for the sign, and the remaining 12 bytes (96 bits)
for the integer number. Adding another 32 bits to the integer means you
can handle numbers with 28 or 29 decimal digits"
Dr. GUI and COM Automation, Part 3: More on COM's Fabulous Data Types
(http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarguion/html/drgui042099.asp)
Forgive me (and correct me <g>) if I'm making a wrong assumption here
but that sounds like a good reason for making the VBA Decimal a
reference type, so it was always going to be 'inefficient' due to its
potential size. Exactly why it is a Variant subtype, hence tempting the
label 'vague' to be applied, rather than a type in its own right I
cannot say.
Even if were possible to implement an in-line Decimal type for VBA I
think the String * N experience demonstrates that it would not
significantly affect the uptake of the DECIMAL type in Access.
As regards a Decimal constant, I assume it best to declare as a String,
fixed length of course <g> e.g.
Const DEC_VALUE As String * 21 = "0.1234567890123456789"
Dim d
d = CDec(DEC_VALUE)
MsgBox d
In conclusion, I do not consider the Decimal type as implemented for
VBA as being a bar to the use of the Jet 4.0 data type in Access.
Feedback on any point made above is encouraged.
Jamie.
--