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 !

 

[Fazza]

The connection is to serverName and databaseName. And then in the SQL an Excel worksheet range is mentioned. It will obviously fail. This is like saying I want something from my neighbour's house (the connection/address is to the neighbour's house) and what I want is the chair from my own lounge. It isn't at the place defined by the connection.

However, try adding specific connection reference to the Excel range inside the SQL. Keep the connection to the database and refer to the database table and when you want to refer to a different data connection have its specifics within the SQL

FROM `info for the Excel file`.[table reference]

The specifics will depend on your Excel version.

I addressed a similar question just yesterday. And in replying referenced this older thread http://www.mrexcel.com/forum/excel-questions/378209-ado-union-data-excel-data-text-file.html

See it had connection to an Excel file but then used data from a different source, a text file in that case, by having the file info in the SQL? There are other old threads that answer the question you've asked, but I failed to find them when I looked yesterday. Suggest you google (or experiment).

regards

 

[slickromeo]

the link you referenced leads to a post where the OP inquires about a "UNION ALL operation to merge both sets of data." This is different from my question because the data contained in a range of cells does not need to be merged with the data [databaseName].[dbTableName].....

Instead, what I need is a LEFT JOIN between a local excel range, and a [databaseName].[dbTableName].

Thank you for clarifying that the reason why this fails is because the local excel spreadsheet cell range IS NOT located in the server.database...... it makes sense that this is the reason why the Left Join is not working.

However, what I am looking for is........ a workaround, that would finagle VBA in a manner which would allow me to achieve the same results AS IF IT WERE located in the [server].[database].

What I mean is....

Using whatever method possible.... I need to get the same benefits of doing a LEFT JOIN on two tables in a server database, when one of the tables is not on that server database... and I would like to figure out a way to treat the local tableRange as if it were indeed in the server database, so that i could perform a left join.

you mentioned in your prior message that perhaps doing, "FROM `info for the Excel file`.[table reference]" might be a fix.

However, I do not fully understand your suggestion... could you provide the exact syntax please? or an example?

the table reference is stored in a variable called rangeTable, but its the 'info for the excel file' that you mention that has me a little bit confused... please elaborate.

if anyone else has any ideas, please let me know, this is an ongoing dilemma I am facing...

 

[Fazza]

Yes, the linked thread is a rather different question but the solution is the same as for your case. It has a connection to one file (Excel) and then in the SQL uses data not only from that connection but also a totally different one - a txt file.

The exact syntax I can't tell you: it will depend on the Excel version btw. It is possible but I don't have the exact answer for your specific (though unknown to me) setup. Suggest you google or experiment.

If you don't understand my suggestion, please re-read the earlier link. The info for the excel file is the connection information for the file where you want to get data.

 

[Fazza]

In case it helps, here is the similar thread from just the other day. http://www.mrexcel.com/forum/excel-questions/860972-joining-multiple-sql-odbcs-into-one-table.html#post4185342

In that thread data was being joined from multiple databases & the solution was as suggested to you : include specifics of the connection directly in the SQL. It did work.

Perhaps it was simpler to see the details because of using MS Query? The specific info for the database was visible in MS Query and so easy to edit it from the first database to suit others.

So, can you connect to the Excel file using MS Query (ALT-D-D-N & follow the wizard, choosing the option to edit in MS Query at the final step) and see the details via the 'SQL' icon?

Then copy the specific info into your VBA & hopefully that will solve the problem.

 

[Kyle123]

It's quite possible that what you are trying to do isn't

Someone would have to have given SQL server access to the location of the the Excel file which isn't particularly likely

 

[slickromeo]

Fazza,

I went to MS Query, ALT-D-D-N, and i selected the excel file, an excel named range, and at the last step i chose to edit in MS Query........ and I am dissapointed.

When I click the SQL Icon.............. ALL this icon shows me is........ the SQL code........ this SQL Code is not going to help me perform a LEFT JOIN to a serverName.DatabaseName.tableName

here's an example of the SQL code i see when I do ALT-D-D-N and following those steps to see the SQL Code

SELECT `Sheet1$`.PROP, `Sheet1$`.CERTNUM, `Sheet1$`.FULLCERT, `Sheet1$`.TOURPROP, `Sheet1$`.EXPIREDATE, `Sheet1$`.PURCHDATE, `Sheet1$`.PURCHAMT, `Sheet1$`.AMTPAID, `Sheet1$`.BALANCE, `Sheet1$`.CERTTYPE, `Sheet1$`.SPGNUM, `Sheet1$`.SALESCENTER, `Sheet1$`.orderNumber
FROM `Sheet1$` `Sheet1$`

then it just lists everything in sheet1 using the column header above.

the range of Data is "Sheet1!A1:L500"

in excel VBA, i've designated a variable for that called rangeTable = "Sheet1!A1:L501"


I still cannot perform a LEFT JOIN from rangeTable to the table in the server database....


Also, i tried one last thing before you say I didn't try your other suggestion...
the first link you posted in your first post, i clicked on it, and I saw that when that other person posted their SQL code for the Sheet1$, they included the file location, and excel version
specifically...... the other person used: FROM [Sheet1$] IN 'C:\ADOTest.xls' 'Excel 8.0;'

So, I changed the sql code assigned to string sqlStr and edited the part where the FROM clause is used

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

After Edit
FROM [rangeTable] IN 'C:\testFile.xls' 'Excel 8.0;'
LEFT JOIN [databaseName].[purchHistoryTable]
ON [rangeTable].[orderNumber] = [databaseName].[purchHistoryTable].[orderNumber]

and when I ran the VBA script which executes the SQL query stored in sqlStr... I get the following Popup Error

"Run-time error...." "Incorrect syntax near the keyword 'IN' "


if you or anyone else has any other ideas, please let me know, because I need to solve this dilemma of doing a LEFT JOIN.

 

[slickromeo]

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...."

 

[Kyle123]

The code is run in the context of the Database Server, have you put the text file there? - Are the ACE drivers installed on the Db server?

 

[slickromeo]

I can't put any files, or tables, in the server database. It's read-only, accessed through the network. the server database has multiple tables, except the one I want to use.... and ideally I would figure out a way to make a LEFT JOIN from a range in excel spreadsheet to a table inside a database on a network server.