|Windows 2008 Server, Access 2000 and 2010 (fails on both), Visual Studio 2010
Long story short, I'm trying to use the following query:
SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) )
When I execute this in my code it works about 15% of the time. If I add a 3.5 second sleep after the execution of this query before the "read" it seems to work about 95% of the time. The process looks something like this:
1. SELECT INTO chkparam (it should create the table and add 1 record to the table)
2. select from the table looking for the record
However the SELECT INTO doesn't always populate the table with the Y. Yes I verified that the table "param" does have a Y. My best guess is the query isn't completing before the code is executed. Is there some way to force completion of the query/transaction? I was alawys under the impression that control wasn't passsed back to the calling procedure until the function was completed. However it seems like I'm getting control back before the function is completing.
This is part of our test network, on our live older system we have been using DAO with the same query (obviously different implementation) for 10+ years with no problems. We found that using DAO on our new network slowed down the queries (plus you can't use it with anything new), thus the change to ODBC.
This is the code in quesiton (in the live version some of this is replaced by varaibles, but we have hardcoded this for testing for now until it works):
strSQL = _T("SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) ")
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
getinfoonly.Open(CRecordset::snapshot, xstrSQL );
short indx = 0;
catch( CDBException* e )
AfxMessageBox(_T("Given SQL Expression \n") + strSQL + e->m_strError );
ok = FALSE;