• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro help - Use cell value in SQL query string

K

Kellan Adamson

New Member
Importing data from SQL. It currently pulls all the data from the database. That part works. Where I am stuck is I only want to import data that hasn't already been imported.

Column B of my data is a transaction number that increases by 1 with each completed transaction. So I have used =MAX(B:B) in cell AA1 to get the value of the last cell in column B.

I want to use the value of that cell in my macro. Something like this:

"SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber>(VALUE OF CELL THAT CONTAINS =MAX(B:B) - currently AA1)"

I can't for the life of me figure out how to do this.
 
You can concatenate query string with the value to build your complete query.

Ex:
Code: 
Dim strQuery as String

strQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value
 
Chihiro said:
You can concatenate query string with the value to build your complete query.

Ex:
Code: 
Dim strQuery as String

strQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value
Click to expand...

THANKS!!!!

So this is the complete import portion that I have been using with your addition. After putting your piece of code in I now get an error. "Incorrect syntax near ">" and debugging points to the last line below. What is it complaining about?


Code: 
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
  'Make sure to change:
      '1: PASSWORD
      '2: USERNAME
      '3: REMOTE_IP_ADDRESS
      '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=winfuel;Data Source=SERVER/DB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=WINFUEL"

'Opens connection to the database
    cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value

'Performs the actual query
    rst.Open StrQuery, cnn
-----------------------------------------------------------------------------------------
Edit by Mod: for putting code inside tag.
 
Last edited by a moderator:
Use code tag when posting codes.
upload_2017-12-20_15-11-10.png

A question, what type of SQL server are you connecting to?

Is it MS SQL? Or some other SQL variant?
 
When you debug.print strQuery before you open recordset. What do you get?

I tested on my end with SQL 2008 R2 & SQL 2016 and had no issue with syntax.
upload_2017-12-20_15-41-2.png
 
So I added "Debug.Print rst.RecordCount" in the same place you have it and I get the same error on the same line.

Code: 
Sub Download_and_Parse()

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
  'Make sure to change:
      '1: PASSWORD
      '2: USERNAME
      '3: REMOTE_IP_ADDRESS
      '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=wUSER;Data Source=DB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=WINFUEL"

'Opens connection to the database
    cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value



'Performs the actual query
    rst.Open StrQuery, cnn
   
    Debug.Print rst.RecordCount
   
'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
 
Last edited:
No, no. You should use Debug.Print on strQuery BEFORE you open recordset.
So that you can check syntax.
My code was just proof that the concept works.
 
My apologies. This is the first time I have had to tackle macros and VBA and SQL all at the same time and I'm a bit green.

To confirm since I'm guessing at this one, do I add debug.print like this:

Code: 
debug.Print StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value

or somewhere else?
 
No worries. You should add Debug.Print line after you set strQuery so...
Code: 
StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Range("AA1").Value

Debug.Print strQuery

Copy the result from immediate window and check for validity of Query string. There may be extra space or something else that's throwing off syntax.
 
This is what I get as a result. Thank you for the explanation. Now I know what the immediate window is as well. lol

SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber >
 
That means your cell AA1 contains no value. Therefore SQL query is invalid.

AA1 must contain numeric value in order for it to work.

EDIT: If it does contain value, you should also qualify Range with sheet that it belongs to.

Ex: Worksheets("Your sheet name").Range("AA1").Value
 
It didn't seem to like having that formula manually entered for some reason. I added the formula to the macro and it works!!!! Thank you for your help!!!

Here is the finished code.

Code: 
Sub Download_and_Parse()

Sheets("Import").Activate
Range("AA1").Select
ActiveCell.FormulaR1C1 = "=MAX(C[-25])"

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
  'Make sure to change:
      '1: PASSWORD
      '2: USERNAME
      '3: REMOTE_IP_ADDRESS
      '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=WINFUEL"

'Opens connection to the database
    cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Sheets("Import").Range("AA1").Value
    Debug.Print StrQuery
'Performs the actual query
    rst.Open StrQuery, cnn
   
'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
 
You must log in or register to reply here.
Back
Top