|
accesswise
|
|
|
accesswise
|
If, after installing Access 2000, you create a new database and then
begin programming in the style you are familiar with from Access 97, you may
find yourself getting unexpected compilation or run-time errors. For example,
Set rstSupplier = OrderDb.OpenRecordset("tblSupplier")
where rstSupplier has been defined as a recordset, may give
Run Time Error '13' : Type Mismatch
The explanation may lie in what object libraries you have referenced.
To check this, bring up the Visual Basic Editor (if not already displayed) and then go into Tools, References. A tick will be shown against each object library which is referenced.
The programming interface which underlies Access 97 is Data Access Objects (DAO) version 3.51. Databases converted from Access 97 to 2000 will automatically reference the latest version of DAO, which is 3.6, and should work ok with little or no amendment to coding.
When you create a new database in Access 2000, however, it will not automatically reference any DAO library, but will instead reference ActiveX Data Object (ADO) 2.1 library, as shown in the above screen-shot. ADO is the successor to DAO and is the programming model for a more universal interface called OLE DB. The idea is that it can connect to virtually any type of data - spreadsheets, text files, data on the web, email data etc. as well as any type of database, rather than just the Jet Database Engine which underlies Microsoft Access itself.
DAO and ADO have certain objects in common, such as Database and Recordset, but the syntax for using them is different. So in the example given above the line involving the recordset was written in the style required for DAO, but the database is trying to interprete it using ADO - hence the error.
There are three possible solutions:
1) Just Reference DAO
If you just want to carry on coding in the style you have used in Access 97, and do not wish to use ADO for the time being, then go into Tools, References as described above, and tick the line which says Microsoft DAO 3.6 Object Library. Also untick the line which says Microsoft ActiveX Data Objects 2.1 Library.
2) Just Reference ADO
ADO is clearly the 'preferred' method which will go on to be developed in the future, and is therefore worth learning and using as much as possible. As mentioned above a new database created in Access 2000 will automatically have the ADO 2.1 library referenced.
3) Reference both DAO and ADO
If you choose to reference both DAO and ADO libraries, be aware that certain object names, such as Database and Recordset, occur in both libraries. When one of these is found in your code it will simply use the object library which has the highest priority, ie. which appears highest in your references dialog box. You can reprioritise the references using the up and down arrows.
You should, however, eliminate any possible ambiguity as to which one is being used where by employing the appropriate prefix for the library when you define your object variable. For example
Dim OrderDb as DAO.Database
Dim rstSupplier as DAO.Recordset
or
Dim OrderDb as ADODB.Database
Dim rstSupplier as ADODB.Recordset
This is called disambiguation. Arguably it is worth doing even when you only reference one of the two, on the grounds that it is a good discipline to adopt and it might speed processing up: there will be no need for it to search through the referenced object libraries in order to find an object which it finds in your code.
Top
|
Back
|
Home
|