I have been trying to figure out a way in Excel VBA to run a very large SQL query. I have code that has worked for smaller queries in the past but I am struggling to get it to work with a large query as I can't just enter the query text into the macro (too big and extremely difficult to troubleshoot where I entered something incorrectly).
My query works fine in Microsoft SQL Server. I am hoping that I can copy the query into a .txt file and have Excel VBA perform the SQL query found in the .txt file. Is this possible to do with Excel VBA and does anyone know how I'd greatly appreciate any help I can get!!
Below is the code I have used to run simple SQL queries - with the last one being the one I actually run and it calls the other functions:
My query works fine in Microsoft SQL Server. I am hoping that I can copy the query into a .txt file and have Excel VBA perform the SQL query found in the .txt file. Is this possible to do with Excel VBA and does anyone know how I'd greatly appreciate any help I can get!!
Below is the code I have used to run simple SQL queries - with the last one being the one I actually run and it calls the other functions:
Code:
Option Explicit
Private CN As ADODB.Connection
Function Connect(Server As String, _
Database As String) As Boolean
Set CN = New ADODB.Connection
On Error Resume Next
With CN
' Create connecting string
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Server=" & Server & ";" & _
"Database=" & Database & ";"
' Open connection
.Open
End With
' Check connection state
If CN.State = 0 Then
Connect = False
Else
Connect = True
End If
End Function
Code:
Function Query(SQL As String)
Dim RS As ADODB.Recordset
Dim Field As ADODB.Field
Dim Col As Long
Dim a
' Open up a recordset / run query
Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText
If RS.State Then
Col = 1
' Output the column headings in the first row
For Each Field In RS.Fields
Cells(2, Col) = Field.Name
Col = Col + 1
Next Field
' Output the results in the rest of the worksheet
a = Application.WorksheetFunction.CountA(Range("A:A")) + 2
Cells(a, 1).CopyFromRecordset RS
Set RS = Nothing
End If
End Function
Code:
Function Disconnect()
' Close connection
CN.Close
End Function
Code:
Sub NonEEQuery()
Dim SQL As String
Dim Connected As Boolean
Dim RangeSearch As Range
Dim RangeFound As Range
Dim LookupValue As String
Application.ScreenUpdating = False
Application.EnableEvents = False
' Our query
SQL = "Select * From MyTable Where MyColumn = MyCriteria"
' Connect to the database
Connected = Connect("MyServer", "MyDatabase")
If Connected Then
' If connected run query and disconnect
Call Query(SQL)
Call Disconnect
Else
' Couldn't connect
MsgBox "Could Not Connect!"
GoTo Finish
End If
Finish:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub