Working with Older Format Databases in Access

Even if you are currently using Access 2007, you may still be working with Access 2002/2003 format databases (or even Access 2000 format databases) for a while. Access 2007 supports working with these older database formats in read/write mode, and you may need to do this — for example, if you are working on a database for a client running an older version of Office. So long as you don't need to use any of the new features introduced in Access 2007 (such as multi-valued lookup fields, or rich text in Memo fields and attachments), you can continue to work with databases in 2000 or 2002/2003 format in Access 2007 without converting them to the new database format.

Disambiguating References to Object Model Components

When DAO was the only object model you could use to work with Access data, when you declared DAO objects there was no need to indicate which object model your objects belonged to —you could just declare a recordset variable as Recordset, or a field variable as Field (as in the following declarations), and your code would work fine:

Dim rst as Recordset Dim fld as Field

But since the introduction of the ADO object model, you may run into problems with such declarations, because certain object names are used in both of these object models. This is true of the new Access 2007 DAO object model as well as the old DAO object model. If you compare the DAO (or Access 2007 DAO) and ADO object models, you will see Recordset, Parameter, and Field objects in both.

When your code is compiled, if the declarations don't include the object model, the first reference in the list of references that contains that object name is used, and it may not be the right one. In Access 2000 and XP (perhaps prematurely), new databases had a default reference only to the ADO object model, which led to many problems for users and developers who were working primarily or exclusively with DAO (see Figure 5.3). However, if you create a new database in Access 2003, in the Access 2002/2003 database format, by default it will have references set to both the DAO and ADO object models, in that order, as shown in Figure 5.4.

FIGURE 5.3

The default references for a new Access 2002 (XP) database.

References - db1

Available References:

0 Visual Basic For Applications 0 Microsoft Access 10,0 Object Library 0OLE Automation 0 Microsoft ActiveX ^

□ IAS Helper COM Component 1.0 Type Library

□ IAS RADIUS Protocol 1,0 Type Library

□ Acrobat Access 2.0 Type Library

□ AcroIEHelper 1,0 Type Library

□ Active DS Type Library

□ Active Setup Control Library

□ ActiveMovie control type library

□ ActiveX DLL to perform Migration of MS Repository V

Priority

Help f t if

Ivi yLkiyu; Ëfetl&fti

This means that all Recordset, Field, and Parameter variables declared without an object model reference will be interpreted as belonging to the DAO object model, which may not be correct.

If your database was first created in Access 2000 or XP, and you didn't set a reference to the DAO object model, you will have the opposite problem — Recordset, Field, and Parameter variables will be interpreted as belonging to the ADO object model, which could cause problems when working with DAO object properties and methods.

The default references for a new Access 2003 database.

When using one of the Find* search methods (FindFirst, FindNext, and so forth), save the search string to a variable, and display it in the Immediate window using a Debug.Print statement; this will be very helpful in debugging any problems because it shows you exactly what expression is being used for the search.

If you are working on a database originally created several Access versions ago, it might have ambiguous declarations such as:

Dim dbs As Database Dim rst As Recordset

As a demonstration of possible problems, the following procedure sets up a recordset and uses FindFirst to locate the first match for "Microsoft" in the CompanylD field:

Private Sub TestFindFirst()

Dim dbs As Database Dim rst As Recordset Dim strSearch As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(Name:="tblCompanyIDs", _

Type:=dbOpenDynaset) strSearch = "[CompanylD] = " & Chr$(39) _

& "Microsoft" & Chr$(3 9) Debug.Print "Search string: " & strSearch rst.FindFirst strSearch

End Sub

If you have a reference set only to the ADO object model, this code won't even compile, and you will get a "User-defined type not defined" error. If you have references set to both object models, and the ADO reference is first, you will get a different error, this time on the line with the FindFirst method reference: "Method or data member not found." If you have just a DAO reference, or the DAO reference is positioned above the ADO reference, the code will compile and run.

If you get a "User-defined type not defined" or "Method or data member not found" error message when compiling code, this is almost always an indication of a missing or incorrect object model version reference.

A more subtle problem could result from an ambiguous declaration of the Field object. There is a Field object in both the ADO and DAO object models, but it has different properties and methods in each object model (see Figures 5.5 and 5.6), so a line of code referencing a Field property could lead to an error if that property is not supported in the object model that is being used.

Although the Access 2007 interface is very different than the interface you might be used to, which has remained pretty much the same from Access 2000 through Access 2003, the Visual Basic (Modules) window is unchanged, except that the mouse wheel now works (about time!).

Using the Object Browser

The Object Browser is a very useful tool for examining object models and their components. It can be opened from a Visual Basic window in Access, Word, Excel, or Outlook from a command on the View menu or by pressing the F2 key. The drop-down list at the top-left lists the available object libraries (corresponding to the references you have set in the database); the lower drop-down list is a search box where you can enter the name of an object model component or attribute to search for; clicking the binoculars button starts the search, and the results are displayed in the Search Results box, as in the following figure. The Classes list shows the members of the selected object library, and the Members of 'Field' list shows the attributes (properties, methods, and events) of the selected object model component.

If you click the yellow question mark button, you will usually get a Help topic for the selected object or attribute, but you can't depend on this — sometimes all you get is a blank Help window. In the case of ADO, in previous versions of Access, if you set a reference to the most recent version of this library (2.8 at that time), you would get blank Help pages; if you set a reference to ADO 2.5, however, you would get the appropriate Help topic. In Access 2007, if you set a reference to the highest version of ADO (6.0), you will get an "Unable to display Help" error message on clicking the Help button. If you set a reference to ADO 2.5, clicking the Help button opens the "Browse Access Developer Help" screen, rather than the specific Help topic for the selected object model component or attribute.

Figure 5.5 shows the attributes of the ADO Field object in the Object Browser.

The attributes of the Field object in the ADO object model.

— Search Results library Ciass Member

Classes

Members of'Fieltf

® <globals>

*

Ei®

ActualSize

Command

EET

Attributes

23 Connection

Ë?

DataFormat

& Error

ggi

DetinedSize

§£1 Errors

Name

ÈS1

NumericScale

Fields

OriginalValue

© Parameter

LJ1

Precision

© Parameters

m-

Properties

© Properties

ö

Status

iffi Property

Type

© Record

UnderlyingValue

Recordset

lit

Value

iS$J Stream

AppendChunk

<#> ADCPROP_ASYNCTHREADF

<3»

GetChunk

sP ADCPROP_AUTORECALC_E

¿F ADCPROP_UPDATECRITER

ADCPROP_UPDATERESYNC

t*p AfTectEnum

<£p BookmarkEnum

CommandTTypeEnum

-

Class Field

Member of ADODB

Member of ADODB

There are many more properties for the Field object in the DAO object model (Figure 5.6), corresponding to specific Access field properties; the ADO Field properties are more generic, because ADO supports data in many different applications.

The solution to the ambiguous reference problems discussed previously is simple: include the object model name in declarations of DAO and ADO variables, as in the following declarations for DAO variables (either DAO 3.6 or Access 2007 DAO). This is called disambiguating the declarations:

Dim rst as DAO.Recordset Dim fld as DAO.Field

Here is the ADO version (note that the object model name is not ADO, as you might think, but ADODB):

Dim rst as ADODB.Recordset Dim fld as ADODB.Field

FIGURE 5.6

The attributes of the Field object in the DAO object model.

■ Search Results

Classes

Members of'Field'

Q <globals>

*

AllowZeroLength

IS ComplexType

nf? Attributes

IS Connection

Co Hating Order

0 Connections

DataUpdatable

0 Container

=

El? DefaultValue

IS Containers

ESP FieldSize

IS Database

El? ForeignName

131 Databases

El? Name

© DBEngine

El? OrdinalPosition

131 Document

El? OriginalValue

IS Documents

eI? Properties

131 Error

i^P Required

IS Errors

Size

i^P SourceField

IS Field2

i^P SourceTable

Fields

(?§? Type

IS Index

eS1 ValidateOnSet

l3l Indexes

eS" ValidationRule

© Parameter

El? ValidationText

IS Parameters

if Value

IS Properties tf+ll Dr^nnr*,

-

El? VisibleValue

Class FteM

Member pf PAD

Member pf PAD

I prefer to always include the object model name in declarations, even if the object in question only appears in one object model, for consistency and in case that object name might be used in some other object model I might need to reference in the future.

The DAO Object Model (Old and New)

The DAO 3.6 object model (shown in Figure 5.7) has been listed as deprecated by Microsoft, which generally means that it will soon be obsolete (not supported). Yet DAO 3.6 is still supported in Access 2007, at least for databases created in Access 2000 or Access 2002/2003 format, so it is worth documenting, because you may need to continue working with older format databases for compatibility with other users who have not yet upgraded to Access 2007.

The DAO object model has many components; for purposes of this book, I will examine in detail only the main components used to reference Access data — Databases, QueryDefs, Recordsets, and TableDefs.

The new Access 2007 DAO object model omits (actually, hides) some of the less frequently used components of the DAO 3.6 object model, and adds a few new ones. This object model is now specifically focused on working with data in Access tables, always its strong point.

The DAO 3.6 object model.

| DBEngine | -| Errors |—| Error | -j Workspaces^Workspace ]

-| Contained]—| Container"

L| Documents]—(Document |

-| QueryDefs |—| QueryDef |

-| Fields

1—1 Field 1

Parameters Parameter

-| Recordsets|—| Recordset |

L| Fields

|—| Field |

-| Relations |—| Relation |

L| Fields

|—| Field |

L|TableDefs 1—|TableDef |

-1 Fields

|—| Field |

-| Indexes

|—| Index |

-| Groups

Group

L| Fields 1—1 Field |

L| Users 1—1 User |

-| Users

1—1 User 1

Groups I—I Group

Was this article helpful?

0 0

Post a comment