Solved

Excel ADO error:  User-defined type not defined

Posted on 2010-11-12
5
491 Views
Last Modified: 2012-06-21
I'm trying to just pull a single SQL 2005 table, one way, into Excel 2003.

Compile error:
User-defined type not defined

I have references set to both Microsoft ADO Ext. 2.8 for DDL and Security and Microsoft ActiveX Data Objects Recordset 2.8 Library

What am I doint wrong?
The below VBA is in the Workbook Open event  in "ThisWorkbook".  It's a newly created Excel worksheet with nothing but this code in it and the references I mentioned added from "Tools|References".
Private Sub Workbook_Open()

'This was set up using Microsoft ActiveX Data Objects version 2.8 (In Microsoft Visual Basic, select Tools, References)

Worksheets("Sheet1").Range("B2:BZ65535").Clear



Dim cnt As ADODB.Connection

Dim rst As ADODB.Recordset

Dim stSQL As String

Dim wbBook As Workbook

Dim wsSheet As Worksheet

Dim rnStart As Range





Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI; " & _

"Persist Security Info=False; " & _

"Initial Catalog=CustomerComplaints; " & _

"Data Source=BETASERVE"



Set wbBook = ActiveWorkbook

Set wsSheet = wbBook.Worksheets(1)



With wsSheet

Set rnStart = .Range("B2")

End With



stSQL = "SELECT * FROM Complaints ORDER BY Location, DateReceived"



Set cnt = New ADODB.Connection



With cnt

.CursorLocation = adUseClient

.Open stADO

.CommandTimeout = 0

Set rst = .Execute(stSQL)

End With



'Here we add the Recordset to the sheet from A1

rnStart.CopyFromRecordset rst



'Cleaning up.

rst.Close

cnt.Close

Set rst = Nothing

Set cnt = Nothing



End Sub

Open in new window

0
Comment
Question by:megnin
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
Comment Utility
Re-posting as Expert Comment:

Instead of referencing this:
Microsoft ActiveX Data Objects Recordset 2.8 Library

Reference this:
Microsoft ActiveX Data Objects 2.8 Library
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
You are brilliant!  :-)  Thank you.  That did the trick.  I missed that because instead of that option being down in the alphabetical listing with the Microsoft..... stuff it was up near the very top of the list of references.  Just not close enough to the top for me to notice it. ;-)
0
 
LVL 1

Author Closing Comment

by:megnin
Comment Utility
Thank you that fixed it.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now