SQL Query within VBA

|||||||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.

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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|||||||

New Member
Joined
Mar 22, 2019
Messages
11
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,765
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What error are you getting with your code?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,765
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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 ...
 
Solution

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,362
Messages
5,641,703
Members
417,229
Latest member
BODYCOTE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top