HOW TO - in excel VBA using SQL - Perform a Left Join from a local named table to a database ADODB table

[slickromeo]

I regularly run queries inside excel VBA.

What I do is I store the SQL code in a String variable 'sqlStr', and then I use that string and pass it to a function which runs SQL Code by opening a connection with ADODB, connecting to a 'serverName' and 'databaseName'...

there are hundreds of tables inside of 'databaseName' and I can easily do things like

FROM [databaseName].[customerTable]
LEFT JOIN [databaseName].[purchHistoryTable]
ON [databaseName].[customerTable].[orderNumber] = [databaseName].[purchHistoryTable].[orderNumber]
etc, etc, etc, and then the whole sql code is stored in a string and passed to the other function to run the query and data dump the results in some empty excel sheet.

and this all works fine until.............. I try to do the following:

Lets say i have an excel sheet called Sheet1, and inside that sheet there is, what I like to call, a temporary table which we'll just assign a range to... "Sheet1!A1:L500"

I have stored the range inside a string variable called rangeTable to reference the local table inside the workbook, and then I try to do a LEFT JOIN on rangeTable

the headers on rangeTable are clearly titled, so i try to link a LEFT JOIN using (for example, lets say one of the column header titles is orderNumber), then I might try and do a LEFT JOIN like this

FROM [rangeTable]
LEFT JOIN [databaseName].[purchHistoryTable]
ON [rangeTable].[orderNumber] = [databaseName].[purchHistoryTable].[orderNumber]

However, this FAILS !

Run-Time error '-2147217865 (80040e37): Invalid Object name 'rangeTable'


?????????????????????

If anyone can help, please help me to do a LEFT JOIN, from a local workbook sheet range, TO a database table field.

If anyone can please help, I would really really appreciate it !

 

[Kyle123]

In summary, you can't.

As I said before, the spreadsheet needs to be accessible from the server, it probably won't be.

 

[Fazza]

I tried something else.

I changed the FROM clause in the sql string to say

"FROM [Text;Database=C:\;HDR=YES;FMT=Delimited].[testFile.xlsm].[rangeTable] AS rangeTable "

and i get popup error "run-time error..." "Invalid object name...."

That is the sort of thing I was thinking - and I don't know if it will work or not - except the connection data used is for a text file. It would need to be the equivalent info but for your Excel file.

To see if it might work, how about changing to a text file for testing? Try a few different queries. So, start with one closest to that old thread which was a UNION. If OK, then try a JOIN. If not successful with a LEFT JOIN try using INNER JOIN or OUTER JOIN to see if any of those work. If it were me, I wouldn't give up til all tested. Best wishes

And once you know if a text file works or not, then try with the correct connection data for your Excel file.

 

[Fazza]

In summary, you can't.

As I said before, the spreadsheet needs to be accessible from the server, it probably won't be.

If that is so, OK. But can't MS Query take care of things by pulling some data from one source & other from another and doing the JOIN (under its own capability)?

So it wouldn't matter that the server can't access the data so long as MS Query can: it is MS Query that does the work? I don't know either way if it is possible, though I am curious. The fact the UNION ALL was said to work in the old thread makes me think there is a chance.

regards