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

slickromeo

New Member
Joined
Jun 12, 2015
Messages
5
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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

New Member
Joined
Jun 12, 2015
Messages
5
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...
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
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

New Member
Joined
Jun 12, 2015
Messages
5
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

New Member
Joined
Jun 12, 2015
Messages
5
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
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

New Member
Joined
Jun 12, 2015
Messages
5
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.
 

Forum statistics

Threads
1,082,309
Messages
5,364,420
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top