Jet, Data Types, and Declarations

You must specify data types in many places within Access, including variables within procedures and tables of stored data on a storage device. The following sections cover some development issues pertaining to data types from Jet 4 and the use of variables in procedures.

Jet

Access 2000 natively supports two database engines. Historically, Access was tied to the Jet database engine. Access 2000 introduces built-in database support for SQL Server 7. Access 2000 SQL Server compatibility is covered in Chapter 12. This section deals with the effects of Jet 4 innovations on Access 2000 application design. These innovations can affect the size of database files and record locking, how you work with selected field data types (including Memo, Hyperlink, and auto-incrementing key fields), and links to external data sources.

Access 2000 stores all text and memo data in Unicode format. This replaces the multi-byte character set (MBCS) format used in previous versions of Access for Japanese, Chinese, and selected other languages. Unicode is a subset of the ISO 10646 standard, which provides digital encoding for all written languages. This new standard requires 2 bytes, instead of the previous 1 byte, to represent a character. This can nearly double the size of databases that rely heavily on character-based fields. To offset this increase, Jet automatically compresses and decompresses these data types whenever possible. It compresses memo fields that are 4000 characters or less in length. Within a field, this can result in compression for some but not all rows in a memo data field. Both Access and ADO automatically compress Unicode characters, but DAO does not support compression for string data types. Developers who program in Jet SQL have available a WITH COMPRESSION clause for specifying tables.

In addition to the new format for character data types, the page size has doubled to 4 KB. This can lead to reduced concurrency due to more page-lock conflicts. Jet addresses this by offering single-row locking. You can minimize the concurrency issue by locking individual records instead of whole pages. Access 2000 lets users update two records on the same page concurrently.

NOTE
The new page size increases the maximum database size from 1.07 GB to 2.14 GB.

Single-row locking is the overall default option, but users and developers can fall back to traditional page locking. Memo fields and indexes never support single-row locking. Data access via ADO or Jet SQL enables the optional fallback to page-level locking. Data access via Access forms and DAO is always via single-row locking.

While single-row locking is desirable for minimizing concurrency conflicts, it requires an overhead cost for setting and removing record locks. The impact of this overhead depends on record size relative to page size as well as the number of locks necessary for a task.

The transition to Unicode also enables Microsoft Windows NT-compatible sorting. This improves compatibility and performance because Access 2000 can deliver consistent sorting performance on both Microsoft Windows 95 and Windows NT systems. This is possible because Windows 95 supports sorting on just the default system language, while Windows NT supports correct sorting on multiple languages. In addition, Visual Basic 6 and SQL Server 7 support this same sorting standard, which provides cross-product standardization. Access 2000 sorting is 50 percent faster for most languages, but the increase in sorting speed is even greater for certain languages, such as Thai.

Jet 4 supports indexing for the first 255 characters of Memo data types. Prior versions of Access and Jet did not support any indexing for Memo fields. While the limited length of the index is not appropriate for all Memo field applications, it has special significance for the Hyperlink data type, which is a derivative of the Memo data type. The new indexing can enhance the sorting and searching of Hyperlink fields.

Jet 4 also enables developers to specify a start value and a step value for auto-incrementing fields. In addition, a new ALTER TABLE statement enables Jet SQL developers to reset both the start and step values. You use another new SELECT @@IDENTITY statement to recover the last value for the auto-incrementing column. You pass the SQL statement value along as the SQL text for the Open method of the Recordset object in an ADO statement. DAO does not support the new statement.

Jet 4 has enhanced installable ISAM technology in several areas, which can affect Access development opportunities with external data sources. The new Text/HTML ISAM lets you read Unicode-formatted web documents. The new Exchange ISAM offers enhancements in several areas. First, it reads indexes from Exchange Server. This dramatically speeds up searches for records in Exchange data sources. Second, the new ISAM supports the Windows Address Book for the Microsoft Outlook Express client. In smaller business environments in which the mail focus is external rather than internal, this offers significant benefits (if only in personal productivity). Third, Jet can retrieve custom-defined Outlook clients as well as those for the Outlook Exchange client. Users of dBase and Paradox databases can continue to enjoy read/write access to these databases through Jet 5. Those requiring support for a more recent version must obtain a copy of the Borland Database Engine from a third-party source.

Data Types

VBA regularly uses variables to save computed outcomes, set properties, designate arguments for methods, and pass values between procedures. To perform efficiently, VBA uses a set of data types for its variables. Several other Access tasks, such as defining tables and function arguments, use data types. This section focuses on data types for variables. Other data types are generally similar. Nevertheless, Access allows different data types for each task.

The following table lists the major types of variables and their storage requirements in VBA programs. Developers who need high levels of precision in their numerical computations can use the Decimal data type as a subtype of the Variant data type. Decimal data types are unsigned 12-byte integers scaled by a variable power of 10. You cannot declare a Decimal data type with a Dim statement, but you can store a Decimal data type by transforming a Variant with the CDec function. It is generally a good idea to use the smallest possible data type to leave the maximum amount of memory for other variables and application logic. However, when you need the precision for numerical computations, the Decimal data type is available.

Variable Data Types

Name Number of Bytes Range
Byte 1 0 through 255
Boolean 2 True or false
Integer 2 -32,768 through 32,767
Long 4 -2,147,483,648 through 2,147,483,647
Single 4 -3.402823E38 through -1.401298E-45 for negative values
1.40129E-45 through 3.402823E38 for positive values
Double 8 -1.79769313486232E308 through
-14.94065645841247E-324 for negative values
4.94065645841247E-324 through 1.79769313486232E308 for positive values
Currency 8 -922,337,203,685,477.5808 through 922,337,203,685,477.5807
Date 8 January 1, 100 through December 31, 9999
Object 4 A reference to an object (see Set statement in the online help)
String (fixed) Length Up to approximately 64,000 characters
String (variable) 10 + length Up to approximately 2 billion characters
Variant (with numbers) 16 Same as Double
Variant (with characters) 22 + length Same as String (variable)
User-defined Depends on elements Sum of the constituent elements for the custom data type

When you fail to declare a variable as a specific type, Access assigns it the Variant data type. This data type is flexible because Variants can assume both numeric and string values. The Variant data type can hold all the other data types except user-defined types (which are a superset of all the other data types). When VBA performs an operation on two Variants, it must first retrieve the contents of the second Variant and convert the result to the data subtype of the first Variant. This makes processing Variants slower than processing other data types. In addition, the data subtype returned by the operation might be incorrect for your application.

You can use the VarType function to determine the data subtype of Variant. The VarType function takes a Variant data type and returns a VBA constant that indicates the subtype of the value in the Variant data type. For example, if varMyVariable contains "Hi, there!", VarType(varMyVariable) returns vbString (8). Similarly, if varMyVariable contains #1/1/2000#, VarType(varMyVariable) returns vbDate (7). Check the online documentation or the Object Browser for a complete list of the Variant subtype constants returned by the VarType function.

VBA conversion functions let you convert a Variant data type to a specific data subtype and they let you specify the type of result that you expect. For example, CDbl(varMyVariable) returns the contents of varMyVariable as the Double data type.

Despite its potential for error and its need for extra processing, Variant is a popular data type. The data in table, query, report, and form fields is Variant by default. If you leave a field in a table unspecified, the field returns a Null value when queried (assuming it has not been assigned data), which is one of two special Variant data values. A Null indicates missing, unknown, or inapplicable data. You use the IsNull function in VBA to test for Null values, and you use the Is Null operator in query criterion statements to do the same for a field. Be careful how you process variables with Null values because Nulls propagate. Any combination of a variable that equals Null with any other variable always returns a Null.

NOTE
Since only Variant data types can assume Null values, you must use this type when your application can benefit from it. Nulls avoid the need for arbitrary values for specifying missing data from a field or variable type.

The Empty keyword is used as a Variant subtype and represents an uninitialized variable value. You use the IsEmpty function in VBA to determine whether a variable has this value. Empty, Null, 0, and zero-length strings ("") are different from each other. When VBA transforms a Variant variable that is equal to Empty, it transforms the variable to either 0 or "" depending on whether a number or string is most appropriate.

When you need to call Windows API functions through dynamic link libraries (DLLs), you sometimes need a user-defined variable. You must declare user-defined variables between Type and End Type statements. The individual lines between these two statements should define the elements of a custom variable type. You can think of a book, for example, as a collection of elements, including an ISBN, a title, author(s), a publisher, and pages. You can specify this custom data type as follows:

 Type Book     ISBN as Long     Title as String     Authors as String     Publisher as String     Pages as Integer End Type Sub MyBook Dim udvMyBook as Book     udvMyBook.ISBN = 1234567890     udvMyBook.Title = "Programming Microsoft Access 2000"     udvMyBook.Authors = "Rick Dobson"     udvMyBook.Publisher = "Microsoft Press"     udvMyBook.Pages = 550 End Sub 

You insert the Type…End Type statement pair in the general declarations area of a module. The procedure MyBook creates an instance of the Book user-defined variable.

Declarations

Variables and constants can be declared. (A constant is a special variable whose value cannot be changed after it's set using a Constant statement.) A declaration serves two roles: It designates the variable's scope (the area within the application that can reference the variable), and it specifies a data type for the variable. These are good reasons for declaring variables before using them. You can require a variable declaration using the Option Explicit statement in the General area of a module.

You use the Public statement in a module's Declaration area to declare variables that your applications can use from any procedure within the application, including other modules. You can use the Private statement to explicitly define a variable as having local scope within a module. (This is not strictly required because the Dim and Static statements declare variables that are private to a module by default.)

The Dim statement reserves memory for a variable only until the procedure in which the variable is declared ends. This means that variables declared using a Dim statement lose their value from one call of the procedure to the next. Variables declared using a Static statement are preserved for the life of the module or until an application resets or restarts the module. (One use for static variable declarations is for running sums or determining the number of times that a procedure is called.)

NOTE
You can clear the values from the static variables in a procedure by choosing Reset from the Run menu in VBE.

You can use the Static keyword in function and procedure declarations as well as in variable declarations. This keyword works identically for functions and procedures; it preserves all the local variables in the function or procedure for the life of the module.

You use the As keyword with either the Dim or Static statement to specify a data type for a variable. The statement Dim intMyNumber As Integer declares the intMyNumber variable to be an integer data type.

Arrays

Access permits you to declare arrays. Arrays are variables that contain lists of values of the same type. An array can be multidimensional, up to as many as 60 dimensions. For example, the statement Dim aryScores(2, 1) As Integer reserves a two-dimensional array of six elements: three rows and two columns of integers.

By default, arrays are zero-based. The statement Dim aryMyName(1) has two elements, which your code can reference as aryMyName(0) and aryMyName(1). You use the Option Base 1 statement in the Declarations area to designate an array as one-based. The following procedure declares an array of two elements, defines its values, and prints its elements and the sum of those elements to the Immediate window.

Sub ArrayTest() Dim aryMyExpenses(1) As Currency, Total As Currency     aryMyExpenses(0) = 10.5     aryMyExpenses(1) = 22.25     Total = aryMyExpenses(0) + aryMyExpenses(1)     Debug.Print aryMyExpenses(0) & " + " & aryMyExpenses(1) _         & " = " & Total End Sub 

The aryMyExpenses array contains two elements of the Currency data type. Because the Currency data type is 8 bytes, the aryMyExpenses array is a total of 16 bytes. Since you can use arrays with many dimensions and variable-length strings, arrays can rapidly consume large blocks of memory.

You use the Dim, Static, Public, and Private keywords as you do with scalar variables to define the scope of an array. You use the ReDim keyword to dynamically change the dimensions of a multidimensional array.



Programming Microsoft Access 2000
Programming Microsoft Access 2000 (Microsoft Programming Series)
ISBN: 0735605009
EAN: 2147483647
Year: 1998
Pages: 97
Authors: Rick Dobson
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net