I have been using the following vba code Using Excel VBA to run SQL query (note upvoted answer in this link is the method i used) this allows me to enter a value in an excel cell and the results from sql are dumped in another column.
However the problem I have now is I want to run this against multiple cells within an excel column (not just a single cell) and hopefully provide me with the status from another column (like a vlookup) from sql.
I have data that contains around 20000 rows that I need to check the status from a status column in sql of 1 million+ records.
Impossible to do one by one using the method linked, I don't know if instead of using '" & Range("A3") & "'" in my StrQuery I can use a table?
To claify this is what I am using:
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;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=DATABASE"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
StrQuery = "SELECT StatusColumn FROM Status WHERE Contact = " & Range("A2") & "'"
'Performs the actual query
rst.Open StrQuery, cnn
Sheets(1).Range("B2").CopyFromRecordset rst
End Sub
I was wondering in range if I could change the range in my sql query from A2 to A:A or A2:A20000 and dump in B:B or B2:B20000 but this doesn't work.
EDIT: I have semi-success with running this again, but with the range increased by 1, however ran out of space, could a loop help?