Recordset not cooperating

[jediderek]

I'm having an issue with my recordset. I'm creating an outlook form that opens up an access database. When I open a recordset with an sql statement, I create the sql statement in Access by making a query. This has worked with 100s of other sql statements that I have made. For some reason when I set the recordset with this particular sql statment below, it reads no records(both EOF and BOF return true). When I copy that sql back to access into a query, it reads 3 records. Does sql statements somehow work different in other programs? Note that I have other sql statements(that I copied from access query) that work fine in this outlook form. I even had someone come and verify that I am indeed copying the sql statement from access to the form correctly. Any thoughts?


Example of the sql statement if it helps:

SELECT tblCustomer.ID, tblQuote_Main.RevisionNumber, tblJobName.ID FROM (tblJobName INNER JOIN (tblIronwoodMaster INNER JOIN (tblCustomer INNER JOIN tblQuote ON tblCustomer.ID = tblQuote.Customer_ID) ON tblIronwoodMaster.ID = tblQuote.IW_ID) ON tblJobName.ID = tblIronwoodMaster.JobName_ID) INNER JOIN tblQuote_Main ON tblQuote.ID = tblQuote_Main.Quote_ID WHERE (((tblJobName.ID)=1));

[DEXWERX]

There's no alias' on the sub selects.

Try SELECT ... FROM () x; instead of SELECT ... FROM ();


And yes there's a difference between SQL that works fine in Access vs ADO
Different SQL modes, and a different subset of Functions are available or not available in Access vs out of Access.

[jediderek]

There's no alias' on the sub selects.

Try SELECT ... FROM () x; instead of SELECT ... FROM ();


And yes there's a difference between SQL that works fine in Access vs ADO
Different SQL modes, and a different subset of Functions are available or not available in Access vs out of Access.

What does the x represent? Are you saying that the inner joins aliases don't work? I'm pretty sure I'm using a DAO recordset and not ADO. I assume in my outlook form that declaring " Dim rs as Recordset" is using a DAO.