Run macro from button

[Desu Nota from Columbus]

I have a macro that works when I use it by selecting from the menu (alt+F8) or by using the shortcut (ctrl+shift+O).

BUT, when I created a command button and had it run the macro, I keep getting the following error. Can someone explain why this is?


ERROR:

User defined type not defined!

Sub ATest()
Dim i As Integer
Dim lRow As Long
Dim iRow As Long
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim startDate As Date
Dim endDate As Date

Server_Name = "GGVORNE\SQLEXPRESS" ' Enter your server name here
Database_Name = "VorneDB" ' Enter your database name here
User_ID = "vorne" ' enter your user ID here
Password = "VData!!!" ' Enter your password here
startDate = Range("A1").Value
endDate = Range("A2").Value
SQLStr = ""
SQLStr = SQLStr & " SELECT * FROM [VorneDB].[dbo].[interval_stream] "
SQLStr = SQLStr & " WHERE [start_time] >= '" & Format(startDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = SQLStr & " AND [end_time] <= '" & Format(endDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
MsgBox SQLStr
Debug.Print SQLStr
Application.ScreenUpdating = False

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z50000") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

With Sheets("Sheet1")
    Columns("A:BB").Select
    Selection.NumberFormat = "General"
    Range("A:A,J:J,AS:AS").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
End With

This is the command button code:

Private Sub CommandButton1_Click()
    ThisWorkbook.ATest
End Sub

 

[Desu Nota from Columbus]

That would imply that you have not set a reference in your project in the VBEditor to the Microsoft ActiveX Data Objects Library. I don't see how you could have been running the same macro with your attempts fro the Macros dialog or shortcut, since you should get the same error.

I added the reference and it works. Thank you!