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

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

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.

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.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

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: