Microsoft KB Archive/291199

Knowledge Base

"Invalid Use of New Keyword" error using ADODB object library

Article ID: 291199

Article Last Modified on 1/31/2007

---

APPLIES TO

• Microsoft Office Excel 2007
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition

---

This article was previously published under Q291199

For a Microsoft Excel 2000 version of this article, see 225059.

SYMPTOMS

When you run a macro that uses the ADODB object in Microsoft Excel, 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
• Microsoft DAO 3.6 appears before Microsoft ActiveX in the References dialog box.

• 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 in the "Applies to" section.

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
                

Additional query words: XL2002 XL2003 XL2007

Keywords: kbbug kbdtacode kberrmsg kbpending kbprogramming KB291199

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.