Nested Select works in Access but NOT ADOR

[chri...@hotmail.com]

The following works in Access, but returns an "ODBC--call failed" when
run in VBScript using ADOR.

amPamsDSN = "DSN=MS Access Database;DBQ=C:\Program Files\Softshare
\Softshare Delta\Production\Documents\pams.mdb;DefaultDir=C:\Program
Files\Softshare\Softshare Delta\Production
\Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

' Read source data records
Set accountRS = CreateObject("ador.recordset")
accountRS.open "Select DISTINCT
cc_facility_account_xref.CCFA_VENDOR_CODE, cc_facility.BRAND FROM
cc_facility_account_xref, cc_facility WHERE
cc_facility_account_xref.FACILITY_CODE=cc_facility.FACILITY_CODE And
cc_facility_account_xref.EFFECTIVE_DATE = (Select max
(cc_facility_account_xref.EFFECTIVE_DATE) FROM
cc_facility_account_xref WHERE
cc_facility_account_xref.FACILITY_CODE=cc_facility.FACILITY_CODE And
cc_facility_account_xref.EFFECTIVE_DATE <= Now()) And
cc_facility.BRAND = 'CITGO';", amPamsDSN, adOpenStatic

[chri...@hotmail.com]

Oh, one more tidbit... it works with ADOR on my XP machine, but fails
on my Win2K machine... I've updated MDAC on the Win2K machine but it
made no difference.

[chri...@hotmail.com]

Well, I changed it to use JET instead and it works fine now.

Changed this:

amPamsDSN = "DSN=MS Access Database;DBQ=C:\Program Files\Softshare
\Softshare Delta\Production\Documents\pams.mdb;DefaultDir=C:\Program
Files\Softshare\Softshare Delta\Production
\Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

To This:
amPamsDSN = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program