XL2000: "Invalid Use of New Keyword" Error Using ADODB Object Library (225059)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q225059
For a Microsoft Excel 2002 version of this article, see 291199.

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 occurs when all of the following conditions are true:
  • You create a reference to both of the following libraries:

    • Microsoft ActiveX Data Objects 2.1 Library
    • Microsoft DAO 3.6 Object Library

    -and-
  • 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
NOTE: This problem does not occur when you use the DAO object, and Microsoft DAO 3.6 appears after Microsoft ActiveX in the References dialog box.

WORKAROUND

To work around this problem, use either of the following methods.

Method 1: Remove the Reference to DAO

If you do not need the reference to Microsoft DAO 3.6 Object Library, follow these steps to remove the reference:
  1. In the Microsoft Visual Basic Editor, click References on the Tools menu.
  2. In the References dialog box, click to clear the Microsoft DAO 3.6 Object Library check box.
  3. Click OK.

Change the Priority of the ADO Reference

Change the priority in which the ADO object library is referenced by placing the reference to Microsoft ActiveX 2.1 before the Microsoft DAO 3.6 Object Library.

To change the priority, follow these steps:
  1. In the Microsoft Visual Basic Editor, click References on the Tools menu.
  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 that this is a problem in the Microsoft products that are listed at the beginning of this article.

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, by using the program ID object: 
Dim rsCustomers as DAO.Recordset
Dim rsCustomers as ADODB.Recordset
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbdtacode kberrmsg kbpending kbProgramming KB225059
©2004 Microsoft Corporation. All rights reserved.