|||||||E|||||||
New Member
- Joined
- Mar 22, 2019
- Messages
- 11
Hi All,
I'm attempting to run a SQL Query from within VBA. After many iterations and different approaches, I think this is the best, but I'm still getting an error at: .Open Source:=SQL_Statement
Any help would be greatly appreciated.
I'm attempting to run a SQL Query from within VBA. After many iterations and different approaches, I think this is the best, but I'm still getting an error at: .Open Source:=SQL_Statement
Any help would be greatly appreciated.
VBA Code:
Option Explicit
Dim conn As ADODB.Connection
Dim rst As ADODB.RecordSet
Sub Run_Report()
Dim Server_Name As String
Dim DatabaseName As String
Dim SQL As String
Server_Name = "***MY SERVER NAME***"
DatabaseName = "***MY DATABASE NAME***"
SQL = "Select Top 10 From ***MY TABLE NAME***"
Call Connect_To_SQLServer(Server_Name, DatabaseName, SQL)
End Sub
Sub Connect_To_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)
Dim strConn As String
Dim wsReport As Worksheet
Dim col As Integer
strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Server=" & Server_Name & ";"
strConn = strConn & "Database=" & Database_Name & ";"
strConn = strConn & "Trusted_Connection=yes;"
Set conn = New ADODB.Connection
With conn
.Open ConnectionString:=strConn
.CursorLocation = adUseClient
End With
Set rst = New ADODB.RecordSet
With rst
.ActiveConnection = conn
.Open Source:=SQL_Statement
End With
Set wsReport = Sheets("Sheet1")
With wsReport
wsReport.Select
Selection.Clear
For col = 0 To rst.Fields.Count - 1
.Cells(1, col + 1).Value = rst.Fields(col).Name
Next col
End With
Set wsReport = Nothing
Call Close_Objects
End Sub
Private Sub Close_Objects()
If rst.State <> 0 Then rst.Close
If conn.State <> 0 Then conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
