I have an Excel spreadsheet that draws data from Access. When we changed
from 2003 to 2007, the VB code/macro no longer draws the data from Access.
The error I now get is a Compile error: User-defined type not defined. The
line where I have "***" is where the error occurred.

Appreciate anyone's assistance. I am not familiar with visual basic and did
not write the macro. The person who wrote it is no longer with us.

If this isn't the right forum for this, I apologize and please point me in
the right direction.


Private Sub LoadData()
On Error GoTo ErrHandler
strConn2 = Me.Worksheets("LookUps").Cells(3, 6) 'Access file location,
in cell F3
strSQL = Me.Worksheets("LookUps").Cells(6, 6) 'Source table in cell F6
intDownloadCmd = Me.Worksheets("LookUps").Cells(3, 8) '1 or 0: 1 will
cause download
If intDownloadCmd < 1 Then
Exit Sub
End If
Application.Calculation = xlCalculationManual
Dim rowctr, colctr, intColIndex As Integer
*** Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstCount As New ADODB.Recordset
cnn.Open strConn1 & strConn2
rstCount.Open "Select Count(*) as RowCount from " & strSQL, cnn
rowctr = CInt(rstCount("RowCount"))
rstCount.Close
If rowctr < 1 Then
Err.Raise Number:=vbObjectError + 1000, _
Source:="LoadData", _
Description:="No records."
End If
rst.Open strSQL, cnn
colctr = rst.Fields.Count
Dim mySheet As Worksheet
Set mySheet = Sheets(SheetName)
'clear a 10,000-row by 250-column block of cells
mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(10000, 250)).Clear
'put in the column headers from the field names
For intColIndex = 0 To colctr - 1
'fields are 0-based, cell cols are 1-based
mySheet.Cells(1, intColIndex + 1).Value = rst.Fields(intColIndex).Name
Next
rst.MoveFirst
With mySheet
.Range("A2").CopyFromRecordset rst
End With
SubExit:
If rstCount.State = adStateOpen Then rstCount.Close
If rst.State = adStateOpen Then rst.Close
If cnn.State = adStateOpen Then cnn.Close
Set rstCount = Nothing
Set rst = Nothing
Set cnn = Nothing
Application.Calculate
Application.Calculation = xlCalculationAutomatic
Exit Sub

ErrHandler:
MsgBox Err.Description
GoTo SubExit
End Sub