Unanswered: Left join query in ADO gives FALSE instead of NULL from linked sqlserver
(previously posted in microsoft forums but got no responses)
I have been using DAO and ADO for years to get at data from linked tables
and by accident I am finding a fault with ADO(???).
My query is below and the tables are linked SQL2005 tables.
If I use DAO - Set rsMembers = CurrentDb.OpenRecordset(strSQL,
dbOpenDynaset, dbSeeChanges, dbReadOnly)
and get 0000 Null Null Null
If I use ADO - rsMembers.Open strSQL, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly, adCmdText
and get 0000 False Null Null ' which is WRONG !!!!!!!!!! False should be Null
SELECT tblMembers.AcNo, tblOver70Bals.Covered, tblOver70Bals.Date70, tblOver70Bals.ShareBal
FROM tblMembers LEFT JOIN tblOver70Bals ON tblMembers.AcNo = tblOver70Bals.AcNo
In further testing I find that if I open a new ADO connection directly to the SQLServer using the SQLOLEDB Provider I get the correct results. It seems to be a problem with the Microsoft.Jet.OLEDB.4.0 provider but I cannot figure out what.
Can anyone explain please?
Any solution? I prefer ADO and I do sometimes need to use the currentconnection to do queries combining Access and SQL tables.
I may now have to go back and review mounds and mounds of queries.