DAO Strangeness

Nicholas d'Alterio -- nick@dalterio.demon.co.uk
Friday, February 07, 1997

Environment: VC++ 4.0 ; Windows NT 4.0

I have been attempting to use DAO a program that I am working on at the
moment, but I have come across a problem.

I am trying to construct a CDaoRecordSet from a query based on a number
of tables. The query works when run under MS Access but the same query
on the same database via DAO fails to produce any match.=20


This is more or less what I am doing:-

CString SQLqry;
qry.Format(=20
"SELECT a.[field1]=20
=46ROM a LEFT JOIN b ON (a.field1 =3D b.field1 And b.field2 =3D %d)
WHERE b.field1 Is Null;", field2_val );

=20CDaoRecordSet result(db);
=20result.Open( dbOpenDynaSet, qry );

If I leave and the second clause of the join condition (b.field2 =3D %d=
)
Then the query produces a result as expected.

I have also tried using parameters and querydefs designed in Access
with the same effect.

Any ideas what is wrong - what I am doing wrong. I am fairly new to
database programming

Nick
--=20
Nicholas d'Alterio                                    nick@dalterio.dem=
on.co.uk

---

Erik Thomas -- ErikThomas@msn.com
Tuesday, February 11, 1997

Nicholas:

Your sample code doesn't tell me much, and I am assuming you really 
meant "CDaoRecordset" and "dbOpenDynaset"--notice "set" is not 
capitalized. However, what helps me (I do quite a bit of DAO) is to write 
out a text file with a given SQL string before attempting to execute it. If 
it throws an exception, SQL.TXT will contain the latest SQL string that 
failed. Then, you can copy the SQL string from the text file, paste it 
directly into Access as a new query and more easily debug it. I have 
found that virtually any query you can build in Access will run using 
DAO--provided you are using the same version jet engine, i.e., version 
3 = Access 95 (and are not using embedded UDF's in the query). I also 
typically build the query using Access' query builder, then wholesale 
copy the SQL string and paste it into a string resource and only modify 
params if necessary.

CString cSQL;
cSQL.Format(SQL_SOME_STRING_RESOURCE, nArg);
SaveSQL(cSQL);  // Save the most recent SQL query string to file.
CDaoRecordset result(m_pDB);
result.Open(dbOpenDynaset, cSQL);

void SaveSQL(CString& cBuf)
{
#ifdef _DEBUG
  CStdioFile f;
  f.Open(_T("SQL.TXT"), CFile::modeCreate|CFile::modeWrite|CFile::typeText);
  f.WriteString(cBuf);
  f.Close();
#endif
}

Erik Thomas
E.J.Thomas & Associates
ErikThomas@msn.com

>This is more or less what I am doing:-

>CString qry;
>qry.Format("SELECT a.[field1] FROM a LEFT JOIN b ON (a.field1 = b.field1 
>And b.field2 = %d)
>WHERE b.field1 Is Null;", field2_val );

>CDaoRecordSet result(db);
>result.Open( dbOpenDynaSet, qry );

>Nicholas d'Alterio                                    nick@dalterio.dem=
>on.co.uk

---

Become an MFC-L member | Вернуться в корень Архива |