1

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?

Community
  • 1
  • 1
Adam128
  • 11
  • 2

1 Answers1

1

Consider a distributed query where you use the Excel workbook as its own table and inner join it with the local SQL Server table. This assumes ad-hoc distributed query privileges are enabled.

Below assumes Excel data maintains header columns (one of which is named status_column) and starts in first cell, A1, of SheetName:

SELECT * 
FROM mySQLServerTable s
INNER JOIN
   (OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Data Source=C:\Path\To\File.xlsx;Extended Properties=Excel 12.0', SheetName$)) e
ON s.status_column = e.status_column
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thank you for your answer, I currently do not have ad-hoc distributed query privileges enabled, is there another route without this? – Adam128 Oct 13 '17 at 11:04
  • Since you have 1 million+ records, import SQL Server table into an MS Access database then run query between Access and Excel, or even import Excel worksheet and run query all inside Access. Or import Excel worksheet in SQL Server and run query from there. – Parfait Oct 13 '17 at 14:25
  • Also, you do not need MSAccess.exe (GUI Office software) to use an Access database. Any Windows PC has the JET/ACE Engine (.dll files) installed. You can create a database from Excel VBA. See [here](https://stackoverflow.com/a/34259616/1422451). – Parfait Oct 13 '17 at 14:25
  • Access doesn't like the HUGE amount of data within the SQL table it seems. – Adam128 Oct 17 '17 at 14:25
  • But even more so in Excel! Access has a 2GB file limit (255-column limit but no row limit on any table). Try limiting columns in `SELECT`. For small character size and integer types, data should fit in an Access database even with 1 mill+ rows. And especially if joining (i.e., filtering) on a distinct Excel list for lesser rows. – Parfait Oct 17 '17 at 16:36