XL2000: Error Using ADODB Object Library

• Microsoft Excel 2000

SYMPTOMS

When you run a macro that uses the ADODB object, you may receive an error message similar to the following:

Compile Error:

Invalid use of New keyword

CAUSE

This problem will occur, when you do the following:

• You create a reference to both
  
  - Microsoft ActiveX Data Objects 2.1 Library

  -and-

  
  - Microsoft DAO 3.6 Object Library



• Microsoft DAO 3.6 appears before Microsoft ActiveX in the References dialog box.



-and-


• You explicitly refer to the ADODB object in a macro.




For example,



Dim rs as ADODB.Recordset 

WORKAROUND

To correct this problem, use either of the following methods.

Method 1: Remove the reference to DAO

1. In Microsoft Visual Basic, on the Tools menu, click References.



2. In the References dialog box, click to clear Microsoft DAO 3.6 Object Library.



3. Click OK.

Change the Priority of the ADO Reference

1. In Microsoft Visual Basic, on the Tools menu, click References.



2. In the References dialog box, click Microsoft ActiveX Data Objects 2.1 Library.



3. Use the Priority buttons to move the reference up the list until is appears before the reference to the Microsoft DAO 3.6 Object Library.



4. Click OK.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Office 2000.

MORE INFORMATION

In Microsoft Visual Basic, you can reference both the Microsoft DAO 3.6 Object Library (DAO) and Microsoft ActiveX Data Objects 2.1 Library (ADO) in a single project. The library for DAO is dao360.dll and for ADO, msado15.dll. However, if you reference both libraries in the same project, Visual Basic may not refer to the correct library when you make the connection or create the recordset object, because both libraries contain similar object names. To avoid these kinds of problems, you can use the program ID name contained in the object library when you declare variables in your project. However, creating a reference to both libraries requires that you reference ADO before the DAO library. The following examples create a variable for a recordset in both DAO and ADO using the program ID object:

Dim rsCustomers as DAO.Recordset
Dim rsCustomers as ADODB.Recordset 

Additional query words: XL2000

Keywords : kbprg kbdta kbdtacode OffVBA kbADO KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug