How to set the correct SQL syntax to use WHERE?

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
Hello everybody in forum,

I've found the example code of how to use SQL in VBA in here query-table-with-excel-as-data-source.html (See Code below)

All the code works fine, but my issue is how to set the correct query to
print print Field2, Field3 where Field1 = 2008.

If I use below line works but prints all lines:
Code:
SQL = "SELECT [Name],[LastName] FROM [Employees$A2:P6]"

If I use below line, I get Run Time Error saying something like: "Some values haven't been established for some required parameters"
Code:
SQL = "SELECT [Name],[LastName] FROM [Employees$A2:D6] WHERE [Year]=2008"

If I use below line, I get Run Time Error too with the same error.
Code:
SQL = "SELECT [F2],[F3] FROM [Employees$A2:D6] WHERE [F1]=2008"

How would be the correct syntax in order to be able to use WHERE?

Code:
Sub Excel_QueryTable()
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim SourceFile As String
Dim qt As QueryTable

SourceFile = Application.ThisWorkbook.FullName
                     
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & SourceFile & ";" & _
                  "Extended Properties=Excel 12.0;" & _
                  "Persist Security Info=False"
                  
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

[COLOR=#0000cd]SQL = "SELECT [Name],[LastName] FROM [Employees$A6:P250]"[/COLOR] [COLOR=#008000]'Here is the line where I need help[/COLOR]

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets("Dest").QueryTables.Add(Connection:=oRS, _
Destination:=Range("B2"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

The sample database in sheet Employees is:
YearNameLastNameDepartment
2003MaryJ.Sales
2007JohnK.Adminstration
2008CarlM.accounting
2010AnnP.Security
2008PeterT.Restaurant

<tbody>
</tbody>

Thanks for any help.
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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