CRecordset.Open throws exception querying MS Access database "Too few parameters. Expected 1"

using a simple call:
CString queryStr;
queryStr.Format("SELECT Col1 FROM UnionQuery");
recset.Open(CRecordset::forwardOnly,queryStr,CRecordset::readOnly);

the last statement throws an exc CDBException, and the message is too few parameters, Expected 1.  I cannot pinpoint the reason.  The UnionQuery object is a union query, and i tried to reduce it to find the problem, but it still happens with something like this:

SELECT "a" As Name, 0 as ID, "a" as Command, "a" as ResultB64, "a" as ResultNAB64, "a" As FileName,"a" As FileVersion,"a" as LocationPath,Yes As Required,"a" As UTC, 0 As ProductID,0 As ServicePackID, Yes As Auxiliary, "a" as Exe, "a" as Switches, "a" as Cmd, "a" as prerun, "a" as postrun, 0 as RebootDetectable, 0 as Replaced, "a" as ReplacedBy, 0 as NETSEC, "a" as MS, 0 As OSID
FROM SupportedOSes
WHERE 1=0

UNION

SELECT pScript.Name, cVerify.ID, Command, ResultB64, ResultNAB64, Null, Null, Null, Null, Null,Null,Null, Null ,Null ,Null ,Null ,Null ,Null ,Null ,pScript.Replaced ,Null ,pScript.NETSEC ,pScript.MS, pScript.OSID
FROM (tableP INNER JOIN pScript ON tableP.ID = pScript.ID) INNER JOIN cVerify ON cVerify.ID = tableP.ID
WHERE tableP.ID > 0
ORDER BY Name, ID ASC

The reason for the first query in the union is to establish the column types for the rest of the query.  THis is the first leg of the union, there are several more, but this seems to still reproduce the problem.
LVL 1
bowser17Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you open the query in Access (or some other query designer) does it work correctly?
0
bowser17Author Commented:
Yes.  and ive searched google.  All of the google results talk about an invalid column name, which would make sense since it would be viewed as a parameter.  This is not the case.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post the SQL of the query here?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... I see that you already have.

Is this your actual query that you're running:

SELECT Col1 FROM UnionQuery

or is this just an example? If it's NOT just an example, then I don't see a Col1 field in your query ...
0
bowser17Author Commented:
Its in there the original message.  There are 2 queries, the union query, and then the query from C++.  The C++ query is very simple, SELECT Col1 from UnionQuery
0
bowser17Author Commented:
Sorry, yes.  that was an example.. just select the first field, or SELECT *.. it all gives the same exception
0
bowser17Author Commented:
If anyone is still looking at this thread, (maybe i should start a new one)...

I was thinking about this and maybe its a driver issue?  What are the alternatives to CDatabase and ODBC connections?  I was thinking about using the Jet driver, but i cannot figure out how to do it.  I believe the jet driver is what Access uses internally?
0
bowser17Author Commented:
This question has been submitted to the Microsoft product group.
0
bowser17Author Commented:
a very simple, yet frustrating solution:

do not use double quotes in access queries.  perhaps this is a best practice that i missed.  According to http://support.microsoft.com/?id=237994  this query would have worked in old versions of MDAC.  i feel that if used to work and it is a valid syntax in access, it should continue to work today.  I'm trying to get a more robust answer from MS.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdelaCommented:
Very frustrating indeed! Thank you for posting the solution. I had built my union SQL query based on a normal query so that the double quotes appear automatically in SQL view. I had another union query that imported into Excel without a problem because the query was based on a linked query and a table (no double quotes appear in this case in SQL view).

THANK YOU!!!!

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.