SQL Query within VBA

[|||||||E|||||||]

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.

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

 

[denzo36]

what version of excel do you have? if its 2016 onwards, you can connect to a SQL server via power query which might be easier?

 

[|||||||E|||||||]

what version of excel do you have? if its 2016 onwards, you can connect to a SQL server via power query which might be easier?

I looked into Power Query, but it doesn't seem as if you can write SQL Queries within Power Query to pull data from a table. You have to sort the data via filters, which would be very tedious with the amount of information I'm pulling, and the variety of queries that I need.

 

[RoryA]

What error are you getting with your code?

 

[|||||||E|||||||]

What error are you getting with your code?

Run-time error '-2147217900 (80040e14)':

Incorrect syntax near the keyword 'From'.

 

[RoryA]

That means your query string is invalid. It appears you are missing either a field name or the asterisk - e.g. SELECT TOP 10 * FROM ...

 

[|||||||E|||||||]

Facepalm...Got lazy at the easiest part. Thank you.

 

[denzo36]

I looked into Power Query, but it doesn't seem as if you can write SQL Queries within Power Query to pull data from a table. You have to sort the data via filters, which would be very tedious with the amount of information I'm pulling, and the variety of queries that I need.

FYI you can write the SQL statement directly into Power Query if you do the following (I'm in Excel 2016)

Data -> New Query -> From Database -> From SQL Server Database

From here enter the database server and database you are using within that server

If you select 'advanced options' you can write you SQL statement directly in there. You don't get any intellisense help though so best to verify your query is working in Management Studio first before dropping it in here