Getting JOINs to work in MSDASQL

Background:

I have an Excel workbook which I am treating as a database.

I am using ADO reference library ("Microsoft ActiveX Data Objects Library")

I connect to the workbook with MSDASQL

My Connection string = "Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=****[insert fullname of excel workbook]****; ReadOnly=True; HDR=Yes;"


Problem:

Why can't I write SQL queries involving JOIN with MSDASQL? I try different variations but I get an error every time.

Below are examples of queries that have failed
(Please ignore the fact that SELECT is not returning anything from the other tables in the queries - I was trying to narrow down where the error was occurring)

• SELECT [tblSong$].* FROM ([tblSong$] LEFT OUTER JOIN [tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle]) WHERE [tblSong$.ID] = 100
• SELECT [tblSong$].* FROM ([tblSong$] OUTER JOIN [tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle]) WHERE [tblSong$.ID] = 32
• SELECT [tblSong$].* FROM ([tblSong$] LEFT JOIN [tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle]) WHERE [tblSong$.ID] = 32
• SELECT [tblSong$].* FROM [tblSong$] JOIN ([tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle]) WHERE [tblSong$.ID] = 32
• SELECT [tblSong$].* FROM [tblSong$] JOIN [tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle] WHERE [tblSong$.ID] = 32
• SELECT [tblSong$].* FROM [tblSong$] INNER JOIN ([tblTitle$] ON [tblTitle$.ID] = [tblSong$.fkTitle]) WHERE [tblSong$.ID] = 32

Finally after much frustration, I rewrote my queries to avoid any use of the word 'JOIN'

Guess what? The queries work now * (examples at bottom of post)

But I hate it. Because:

1. It's forcing me to learn an unnatural SQL query structure (because I cant join, I have to move all the ON parts to the WHERE section) and
2. as a result of the above - the query strings are 'bloated'
3. I'm guessing that these sloppy queries will slow down the response times as the database grows

Can anyone please tell me how to properly write JOIN queries so that they run in MSDASQL?



* Below are some examples of how I wrote my queries while avoiding any use of the word 'JOIN'. Foul aren't they?

• SELECT [tblSong$].* FROM [tblSong$], [tblTitle$] WHERE [tblSong$.ID] = 32 AND [tblTitle$.ID]=[tblSong$.fkTitle]
• SELECT [tblSong$].*, [tblTitle$.Name] FROM [tblSong$], [tblTitle$] WHERE [tblSong$.ID] = 32 AND [tblTitle$.ID]=[tblSong$.fkTitle]
• SELECT [tblSong$].*, [tblTitle$.Name], [tblArtist$.Name] FROM [tblSong$], [tblTitle$], [tbjSongArtist$], [tblArtist$] WHERE [tblSong$.ID] = 100 AND [tblTitle$.ID]=[tblSong$.fkTitle] AND [tbjSongArtist$.fkSong] = 100 AND [tblArtist$.ID] = [tbjSongArtist$.fkArtist]

Why don't you use the ACE.OLEDB.12.0 provider?

That did cross my mind as I was asking the question...

Originally Posted by habang

Hi, mc84Excel - do you think you could attach an example of the datafile?

It would be easier to have a go at the problem.

Best regards
Habang

Thank you for your offer Habang.

Attached is an edited copy of the database workbook. It has an extremely small number of records as I am holding off adding the rest until I have the SQL queries sorted out!

(edited = The only change I have made from the original is to obscure the song titles)

Originally Posted by Kyle123

What happens if you remove all parenthesis? AFAIK only Access is pedantic when it comes to them

I tried that. In the OP - the bullet points of what didn't work - the parenthesis free version is 2nd from the bottom.

Originally Posted by Kyle123

One could also ask why not use a database....

Kyle....

I PMd you the reasons for that a few days ago.

mc84excel

Can you post the code you are using to connect to the workbook?

Originally Posted by romperstomper

Why don't you use the ACE.OLEDB.12.0 provider?

Because I am not familiar with it. How do I use it? Do I need MS Access installed first? Is there any Excel/Windows versions that it wont work with?

Originally Posted by Kyle123

Because that would be easy

Originally Posted by romperstomper

That did cross my mind as I was asking the question...

OK you've had your fun now please lay off the teasing.


My approach to VBA is this:

I never intentionally choose the most difficult option just because it is difficult (I'm not a masochist).

But if I want to achieve a specific goal - and I have dismissed the alternative options that I am aware of - then I don't give up and avoid taking the remaining - more difficult - option.


Moving back to the specifics - I haven't avoided using ACE because it's "easy". Rather, I haven't considered ACE because I am not aware of this option. I only ever use MSDASQL to connect to Excel because it was the first connection method I found that worked for me.

Originally Posted by Kyle123

No, it's the standard for newer versions of Access and the XML based spreadsheet formats. MSDASQL is old and AFAIK not available on 64bit, the ACE provider should be a straight replacement and installed by default.

* At least one of the PCs that this project is used on doesn't have Access installed. If MS Access is a prerequisite for using ACE then I can't adopt it (at least not for this project - this isn't the only project I have that uses Excel databases )
* I have successfully run projects using MSDASQL on 64 bit OS... Whereas a quick google search appears to indicate that some people have problems using ACE in 64 bit (the patch doesn't work for everyone?)

Originally Posted by Kyle123

you are really making life much more difficult than it needs to be though by refusing to use a database - relational data is what they are for. Whilst you may be able to emulate a small portion of this in Excel it does not mean that it's a good idea, if it were then Access, for example would not exist. Excel is a calculator and shouldn't be hammered into every application.

True. But if I am building a tiny - and I mean minuscule (say less than 5000 records) - database then I use Excel workbooks. I don't see any point in going for a full RDMS for such a tiny project. Especially when the end users wont want to install extra software on their PCs (whereas they all have Excel)

I agree what you say about Excel but my Excel skills far surpass my Access skills. Therefore I stick with what I know. How do I put this - It's like seeing someone code in BASIC and you say hey why don't you code this in C instead? But at the end of the day, what matters is whether it works - the end user doesn't care what code was used to write the program (the "they ain't gonna see it" logic). A well written program in BASIC will trump a poorly written program in C.

Originally Posted by Norie

mc84excel

Can you post the code you are using to connect to the workbook?

Good to hear from you Norie

Er well, if I posted my code you would ROTFL!


OK then - I am trying to return an array using the function "fnavarAdoSELECTExcelMSDASQL". The input arguments are the FullName of the Excel workbook and the SQL query string.

I keep this function in my 'template module' for Databases (template module = I create modules for storing reusable code. I drag and drop them into projects as I need them).

Unfortunately my SQL skills are low and my Database template module is a mess (most of the functions in it are experimental and not being used)

OK I am uploading a WB which contains the Database template module + some other code that the module requires.

Originally Posted by Kyle123

As I said, you don't need Access installed to use ACE.

I missed that bit. My fault. OK then I am willing to look at using ACE as an alternative option. Is there any page you recommend for learning ACE (e.g. connection strings etc)?

I am still missing the bit about why ACE would work where MSDASQL will not. Since ACE is more recent, I assume that MSDASQL was heavily used prior to ACE existence? If yes then how did they code around the JOIN issue I've run into??

Here's a good resource for connection strings http://www.connectionstrings.com/excel/.

Thanks Norie. (BTW any thoughts on my scrambled mess of code I uploaded per your request?)

Only had a quick look, could you post a simple example sub showing how you were running the queries from the first post?

Originally Posted by Norie

Only had a quick look, could you post a simple example sub showing how you were running the queries from the first post?

Thanks Norie - I appreciate your help.

I can do slightly better than just post a single sub.

Attached is an extract/mockup of the XLSM I am using to connect to the XLSX database (edited copy attached)

To test it, call the sub 'TestForNorie' (found in module 'aaa_NorieTest') and the string argument needs to be the FullName of wherever you saved the XLSX file.

You can test different songs by changing the optional lngSongPK argument (I have set it to default to song 32 while testing)

You can see my notes in the function 'fnstrQryOriginal_Get1Record' as to what queries work/wont work.

I think the problem is with this part of the SQL.

[tblTitle$.ID] = [tblSong$.fkTitle]

When I change the SQL of the first JOIN query to this the code appears to work.

Please Login or Register  to view this content.

Not tested it with criteria yet, but I think there's a similar problem there as you use [tblSong$.ID].

Just tested with this at again everything appears to work.

Please Login or Register  to view this content.

Originally Posted by mc84excel

I assume that MSDASQL was heavily used prior to ACE existence? If yes then how did they code around the JOIN issue I've run into??

I would say that JET.OLEDB.4 was the most common provider used before ACE replaced it with Office 2007.

Originally Posted by romperstomper

I would say that JET.OLEDB.4 was the most common provider used before ACE replaced it with Office 2007.

Oh dear. I've heard the name of that before but apart from that, I'm not familiar with that one either.

(You see, when I first decided to use Excel workbooks as small impromptu databases - which was some time ago now - I just copy/pasted different code from the web (without understanding what they/I were doing ) until I found the first one that worked for me. Which was MSDASQL. I have no idea what other connection types I tried back then - I do recall it was an exercise in frustration though. Maybe I should reconsider and look at using JET or ACE?)

Originally Posted by Norie

but I think there's a similar problem there as you use [tblSong$.ID]

What? How? Why?

I don't understand. Other table headers work within the brackets, so why should ID be different?


In any case, it now works. Thank you Norie. (Reps) Thank you very much for solving this problem.

What other table headers work?

Do you mean something like this?

[tblArtist$.ID]

To me that doesn't look right, it looks like you are trying to refer to a field with the name 'tblArtists$.ID'.

Originally Posted by Norie

What other table headers work?

Do you mean something like this?

[tblArtist$.ID]

To me that doesn't look right, it looks like you are trying to refer to a field with the name 'tblArtists$.ID'.

I mean things like [tblSong$.fkTitle] work. i.e. It doesn't have to be [tblSong$].fkTitle.

Also, to make it worse, see back in the OP, check the bottom few bullet points where I demonstrate what works. Some of them work with ID inside the brackets - [tblSong$.ID]

So unless I've missed something, you can set the table headers inside or outside the brackets and they'll work UNLESS you are using JOIN in which case the .ID must be outside the brackets?! (I find the apparent illogic here puzzling)

Did you try what I suggested in post #24?

Did it work?

Originally Posted by Norie

Did you try what I suggested in post #24?

Did it work?

Yes I did. I thanked you and repped you for it. See post #27

The fact is that the correct syntax is to use [tablename].[fieldname] not [tablename.fieldname]. It is illogical that the latter works at all, I agree.