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:
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.
Code:
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.