HELP: Parametrized union queries don't work with Jet 4.0 
Author Message
 HELP: Parametrized union queries don't work with Jet 4.0

I recently installed Jet 4.0 ODBC Access driver (with MDAC 2.1 SP2). Now
driver returns an error "Too few parameters. Expected <N+1>", where N is
the actual number of parameters the query has. It works flawlesly with
Jet 3.51.

Are there any known differences or issues with Jet 4.0 that could cause
this failure?

Here is the sample query wich works with Jet 3.51. I tested it with DAO,
RDO, and ADO.

PARAMETERS sFormName Text;
SELECT ASCRDEFS.sd_fldnm
FROM AFORMS INNER JOIN ASCRDEFS ON AFORMS.fm_id = ASCRDEFS.sd_fm
WHERE ((AFORMS.fm_name=[sFormName]) AND (ASCRDEFS.sd_req=True))

UNION SELECT [fld1].[sd_fldnm] & "_" & [fld].[sd_fldnm] AS sd_fullname
FROM AFORMS INNER JOIN (ASCRDEFS AS fld LEFT JOIN ASCRDEFS AS fld1 ON
fld.sd_fldrel = fld1.sd_fldno) ON AFORMS.fm_id = fld.sd_fm
WHERE (((AFORMS.fm_name)=[sFormName]) AND ((fld.sd_req)=True) AND
((fld.sd_fldrel) Is Not Null));

Here is how I use it in DAO:

Dim wsp As Workspace
Dim cn As Connection
Dim q As QueryDef
Dim rs As Recordset

DBEngine.DefaultType = dbUseODBC
Set wsp = DBEngine.CreateWorkspace("BCS", "Admin", "")
Set cn = wsp.OpenConnection("BCS", , False,
"ODBC;DSN=BCS;UID=Admin;PWD=pwd")
Set q = cn.CreateQueryDef("", "{ Call stpMissing (?) }")
q.Parameters(0).Value = #2/5/00#
Set rs = q.OpenRecordset   ' <----FAILS HERE

I also tried it with RDO and got same result

With Jet 4.0 it produces the following error:
07001: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

It only seems to happen to union queires containing parameters.

Can anyone give me a hint as to which direction to go from here?

Dmitriy Shulman



Mon, 29 Jul 2002 03:00:00 GMT  
 HELP: Parametrized union queries don't work with Jet 4.0
I recently installed Jet 4.0 ODBC Access driver (with MDAC 2.1 SP2). Now
driver returns an error "Too few parameters. Expected <N+1>", where N is
the actual number of parameters the query has. It works flawlesly with
Jet 3.51.

Are there any known differences or issues with Jet 4.0 that could cause
this failure?

Here is the sample query wich works with Jet 3.51. I tested it with DAO,
RDO, and ADO.

PARAMETERS sFormName Text;
SELECT ASCRDEFS.sd_fldnm
FROM AFORMS INNER JOIN ASCRDEFS ON AFORMS.fm_id = ASCRDEFS.sd_fm
WHERE ((AFORMS.fm_name=[sFormName]) AND (ASCRDEFS.sd_req=True))

UNION SELECT [fld1].[sd_fldnm] & "_" & [fld].[sd_fldnm] AS sd_fullname
FROM AFORMS INNER JOIN (ASCRDEFS AS fld LEFT JOIN ASCRDEFS AS fld1 ON
fld.sd_fldrel = fld1.sd_fldno) ON AFORMS.fm_id = fld.sd_fm
WHERE (((AFORMS.fm_name)=[sFormName]) AND ((fld.sd_req)=True) AND
((fld.sd_fldrel) Is Not Null));

Here is how I use it in DAO:

Dim wsp As Workspace
Dim cn As Connection
Dim q As QueryDef
Dim rs As Recordset

DBEngine.DefaultType = dbUseODBC
Set wsp = DBEngine.CreateWorkspace("BCS", "Admin", "")
Set cn = wsp.OpenConnection("BCS", , False,
"ODBC;DSN=BCS;UID=Admin;PWD=pwd")
Set q = cn.CreateQueryDef("", "{ Call stpMissing (?) }")
q.Parameters(0).Value = #2/5/00#
Set rs = q.OpenRecordset   ' <----FAILS HERE

I also tried it with RDO and got same result

With Jet 4.0 it produces the following error:
07001: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

It only seems to happen to union queires containing parameters.

Can anyone give me a hint as to which direction to go from here?

Dmitriy Shulman



Mon, 29 Jul 2002 03:00:00 GMT  
 HELP: Parametrized union queries don't work with Jet 4.0


Fri, 19 Jun 1992 00:00:00 GMT  
 HELP: Parametrized union queries don't work with Jet 4.0
KB Article Q237994 mentions that Jet 4.0 ODBC driver no longer accepts
double quotes in query text, so I have to convert all queries that use
double quotes to use single quotes instead. (that's several databases two
dozen queries each in my case)
Quote:

> I recently installed Jet 4.0 ODBC Access driver (with MDAC 2.1 SP2). Now
> driver returns an error "Too few parameters. Expected <N+1>", where N is
> the actual number of parameters the query has. It works flawlesly with
> Jet 3.51.

> Are there any known differences or issues with Jet 4.0 that could cause
> this failure?

> Here is the sample query wich works with Jet 3.51. I tested it with DAO,
> RDO, and ADO.

> PARAMETERS sFormName Text;
> SELECT ASCRDEFS.sd_fldnm
> FROM AFORMS INNER JOIN ASCRDEFS ON AFORMS.fm_id = ASCRDEFS.sd_fm
> WHERE ((AFORMS.fm_name=[sFormName]) AND (ASCRDEFS.sd_req=True))

> UNION SELECT [fld1].[sd_fldnm] & "_" & [fld].[sd_fldnm] AS sd_fullname
> FROM AFORMS INNER JOIN (ASCRDEFS AS fld LEFT JOIN ASCRDEFS AS fld1 ON
> fld.sd_fldrel = fld1.sd_fldno) ON AFORMS.fm_id = fld.sd_fm
> WHERE (((AFORMS.fm_name)=[sFormName]) AND ((fld.sd_req)=True) AND
> ((fld.sd_fldrel) Is Not Null));

> Here is how I use it in DAO:

> Dim wsp As Workspace
> Dim cn As Connection
> Dim q As QueryDef
> Dim rs As Recordset

> DBEngine.DefaultType = dbUseODBC
> Set wsp = DBEngine.CreateWorkspace("BCS", "Admin", "")
> Set cn = wsp.OpenConnection("BCS", , False,
> "ODBC;DSN=BCS;UID=Admin;PWD=pwd")
> Set q = cn.CreateQueryDef("", "{ Call stpMissing (?) }")
> q.Parameters(0).Value = #2/5/00#
> Set rs = q.OpenRecordset   ' <----FAILS HERE

> I also tried it with RDO and got same result

> With Jet 4.0 it produces the following error:
> 07001: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
> Expected 2.

> It only seems to happen to union queires containing parameters.

> Can anyone give me a hint as to which direction to go from here?

> Dmitriy Shulman



Mon, 29 Jul 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Insert works in Access query window but not from VB using JET 4.0

2. MSDN Lybrary don't work on WNT 4.0

3. Queries in Access don't work in ADO

4. MovePrevious don't work but the MoveNext work's fine

5. I've programmed a game that don't work Help

6. Access parametrized union queries no longer work. HELP!

7. Union query gives 'Too few parameters'

8. ODBC Failed with 'UNION' in query

9. Assign Temporary Where to a query - don't modify old query

10. UNION query is not working...

11. Union query not working only in vb

12. UNION query is not working...

 

 
Powered by phpBB® Forum Software