Using SQL to Join 3 Tables

[jmunk]

I have a frontend database that is Access 2002 (originally written in Access 97) and the backend data is a SQL database. I need to join three tables in a SELECT statement to create a DAO recordset. My SELECT statement is:

    Criteria = "SELECT * "
    Criteria = Criteria & "FROM tblPendItems INNER JOIN "
    Criteria = Criteria & "(tblAccount INNER JOIN Rolodex "
    Criteria = Criteria & "ON tblAccount.BranchContactNumber = Rolodex.ContactNumber) "
    Criteria = Criteria & "ON tblPendItems.AccountID = tblAccount.AccountID;"
    Set rs = db.OpenRecordset(Criteria, dbOpenDynaset, dbSeeChanges)

When this code runs, Access gives the message that it has encountered a problem and needs to close. If I join only tblAccount and Rolodex or tblPendItems and tblAccount, it works fine.

I have also tried saving a query joining the three tables. I can run the query manually and it works. However, if I try to use it as the source for the recordset, Access bombs.

Any help would be greatly appreciated. Thank you.

 

[thecreator]

Hi jmunk,

Check your bracketing .... if you can run it through a QBE then switch to Sql view in your query design and recut - repaste the query to a string value in your module. Also, I would change your string name to MyCriteria to make sure that you are not using something that SQL uses as a reserved word .. you never know !!

MyCriteria = "select ........... <space>" _
& " more criteria .........<space>" _
& "more criteria ......;"

But, do not drop any of the brackets that Access has put into the code in the first place. It seems to like more brackets than pure SQL requires ... perhaps this is a function of how the JET interprets SQL ??? I have been stung like this before too.

You may also wish to drop the switch dbSeeChanges to see if that affects the results.

Hope this helps

 

[scriverb]

Try this code:

Criteria = "SELECT * FROM (tblPendItems INNER JOIN tblAccount " & _
"ON tblPendItems.AccountID = tblAccount.AccountID)INNER JOIN " & _
"Rolodex ON tblAccount.BranchContactNumber = Rolodex.ContactNumber;"
    Set rs = db.OpenRecordset(Criteria, dbOpenDynaset, dbSeeChanges)

Post back if you have any problems.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jmunk]

Thank you for the suggestions, thecreator and scriverb.

I tried both suggestions and Access still crashes when trying to open the recordset. I cannot remove the dbSeeChanges option because one of my tables has an identity column.

When Access crashes, the information in the debug screen is:

The instruction at "0x1b0f1ab2" referenced memory at
"0x00000000". The memory could not be read.

I tried installing Office XP SP3 since I was running on SP1 but that did not help. I believe I have the latest patch for Jet 4.0. I tried running the code on another computer just to rule out my PC but it did not work there either.

Any other suggestions? Thanks again.

 

[scriverb]

First of all try running your first Select within the UNION just as a select all by itself to see if it works. Then try each of the other two all by themselves. Let's see if there is something in the code or something in the tables data that is causing the problem.

From the error description here it does sound like something more like a memory or an installation problem. But, let's rule out the queries first.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Sorry, about the UNION query reference I was working on another thread just before this one which had UNIONS.

But, it still sounds like an installation or memory problem.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]