Compile Error User-defined type not defined??

[hillaryruth]

I picked this code up in the forum. I have tried to change it to work with my
DB, but I know nothing about VB so I don’t know what the error I’m getting means and I don’t know how to correct it. I get the error Compile Error User-defined type not defined
This is what I have:


Private Sub Command10_Click()
'ADO Recordset
Dim rs As ADODB.Recordset, cmd As ADODB.Command

'Excel Objects
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'SQL Strings - 3 Recordsets
Dim sqlArr(1 To 3) As String

'Field Names - Stack into Array
Dim fldArr() As String

'Need some loop counters
Dim i As Long, j As Long

'OLE - Create xl Objects
Set xlApp = New Excel.Application

'Add a new Workbook, with one Worksheet, to our Excel Application
Set xlWb = xlApp.Workbooks.Open("Full Path Of Excel Spreadsheet")

'Stack the SQL Array wit String Elements - Stored Procedure Names
Let sqlArr(1) = "Query2"

For i = LBound(sqlArr) To UBound(sqlArr)
'New ADO Command Object
Set cmd = New ADODB.Command

'Open the Recordset, Passing the SP Name from our Array
With cmd
.ActiveConnection = CodeProject.Connection
.CommandText = sqlArr(i)
.CommandType = adCmdStoredProc
.Parameters.Refresh

Set rs = .Execute
End With

With rs
'Stack a String Array with the Field Names
ReDim fldArr(0 To .Fields.Count - 1)

For j = LBound(fldArr) To UBound(fldArr)
Let fldArr(j) = .Fields(j).Name
Next

'Time to Pass some Data to Excel!
With xlWb.Worksheets

'Add a Worksheet if we're at 2nd Recordset or Greater
If i > 1 Then .Add After:=.Item(i - 1)

'Refer to the Worksheet by Item Number in the Collection of _
Worksheets (1-based)
With .Item(i)
'Pass our dynamic Field String Array to A1, stretched to the _
Right for number of Elements
Let .Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr

'Copy our Current Recordset to A2
.Range("a2").CopyFromRecordset rs

'Rename Individual Worksheet
.Name = "Query2" & i
End With
End With
End With
Next

'Make Excel visible - (Otherwise Save and Close)
With xlApp
.GoTo xlWb.Worksheets("Sheet3").Range("A1")
.Visible = True
End With

'Terminate our Excel Object Variables
Set xlWb = Nothing: Set xlApp = Nothing

'Now close and terminate the ADO Recordset, we're all done!!
rs.Close: Set rs = Nothing
Set cmd = Nothing
End Sub

[geekgirlau]

You need to make sure that you have a reference to Excel in your code. Select Tools | References and make sure "Microsoft Excel x.x Object Library" is ticked (the number depends on the version of MS Office you are running).

For future reference, when you post code, select the code text and click on the VBA button. I've taken the liberty of modifying your post so you can see what this looks like.

[hillaryruth]

Thank you that fixed that error, now I have another:
"Expected query name after EXCUTE" I tried putting in the query name but still won't run.

Private Sub Command10_Click()
'ADO Recordset
Dim rs As ADODB.Recordset, cmd As ADODB.Command
'Excel Objects
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
'SQL Strings - 3 Recordsets
Dim sqlArr(1 To 3) As String
'Field Names - Stack into Array
Dim fldArr() As String
'Need some loop counters
Dim i As Long, j As Long
'OLE - Create xl Objects
Set xlApp = New Excel.Application
'Add a new Workbook, with one Worksheet, to our Excel Application
Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\mike\Desktop\Book1.xls")
'Stack the SQL Array wit String Elements - Stored Procedure Names
Let sqlArr(1) = "Query2"
For i = LBound(sqlArr) To UBound(sqlArr)
'New ADO Command Object
Set cmd = New ADODB.Command
'Open the Recordset, Passing the SP Name from our Array
With cmd
.ActiveConnection = CodeProject.Connection
.CommandText = sqlArr(i)
.CommandType = adCmdStoredProc
.Parameters.Refresh
Set rs = .Execute
End With
With rs
'Stack a String Array with the Field Names

[geekgirlau]

Might be a stupid question, but does your stored procedure result in a recordset?

[hillaryruth]

stored procedure result in a recordset--

Sorry I don't know what this is

[geekgirlau]

You are attempting to run a stored procedure

.CommandType = adCmdStoredProc

A recordset is a collection of records, such as you would see when you run a query. Is that what this stored procedure does?

[OTWarrior]

To the best of my knowledge the .Execute command requires something after it so it knows what it is to execute.

(Taken from the help file)

Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

So maybe this would work?

Set rs = .Execute (cmd,"Query2" )

[akn112]

just to get an idea of whats going on can you copy and paste the SQL code from Query2. To do this,

open Query2 in design mode
goto view->SQL
copy and paste the text there

[akn112]

CommandText should be an SQL statement so it should look something like

set rs = connection.execute("SELECT...WHERE...", lngLong)

where lngLong is a Long which stores the amount of records the operation affected



To the best of my knowledge the .Execute command requires something after it so it knows what it is to execute.

(Taken from the help file)

Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

So maybe this would work?

Set rs = .Execute (cmd,"Query2" )

[DarkSprout]

Or:=


Dim sql as String

sql = "SELECT tbl_DAY_Bookings.anDayBookingID, tbl_DAY_Bookings.nDayProviderID, tbl_DAY_Providers.txtProviderName, tbl_DAY_Bookings............"

DoCmd.RunSQL sql