G
Giangi
Hello everybody,
I need access an external database via a sql statement with two parameters
but my routine return run-time error -3142-; the same sql, without
parameters, runs perfectly.
Someone help me? Thanks in advance.
My routine is :
Dim conAHE As Connection, WS As Workspace, DOC As Recordset, miaQuery As
QueryDef
Set WS = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Set conAHE = WS.OpenConnection("AHE", dbDriverNoPrompt, True,
"ODBC;DATABASE=AHE_STD;UID=sa;PWD=;DNS=AHE")
Set miaQuery = conAHE.CreateQueryDef("")
With miaQuery
.Prepare = dbQPrepare
.Sql = "SELECT SDSDOC_MAST.MVSERIAL, MVNUMDOC, MVDATDOC, MVCODCON,
ANDESCRI, Left([anindweb],6) AS WEBcli, ANNAZION, Left([ddnomdes],6) AS
WEBdes, " & _
"Mid([ddnomdes],9) AS NOMdes, MVAIMPN1, MVAIMPN2, MVSPEINC,
MVAIMPS1, MVAIMPS2, MVCODART, MVDESART, MVQTAMOV, MVPREZZO, MVVALMAG " & _
"FROM ((SDSDOC_MAST LEFT JOIN SDSCONTI ON
(SDSDOC_MAST.MVTIPCON = SDSCONTI.ANTIPCON) AND (SDSDOC_MAST.MVCODCON =
SDSCONTI.ANCODICE)) " & _
"INNER JOIN SDSDOC_DETT ON SDSDOC_MAST.MVSERIAL =
SDSDOC_DETT.MVSERIAL) LEFT JOIN SDSDES_DIVE ON (SDSDOC_MAST.XXTIPCON =
SDSDES_DIVE.DDTIPCON) " & _
"AND (SDSDOC_MAST.XXCODCON = SDSDES_DIVE.DDCODICE) AND
(SDSDOC_MAST.XXCODDES = SDSDES_DIVE.DDCODDES) " & _
"WHERE [MVTIPDOC] In ('VFACC','VFDIF','VFTUE') AND
Year([mvdatdoc])=? AND Month([mvdatdoc])=? " & _
"ORDER BY MVDATDOC, MVNUMDOC;"
.Parameters(0).Direction = dbParamInput
.Parameters(1).Direction = dbParamInput
.Parameters(0) = 2009
.Parameters(1) = 10
.Execute
End With
Set DOC = miaQuery.OpenRecordset(dbOpenSnapshot)
.....
I need access an external database via a sql statement with two parameters
but my routine return run-time error -3142-; the same sql, without
parameters, runs perfectly.
Someone help me? Thanks in advance.
My routine is :
Dim conAHE As Connection, WS As Workspace, DOC As Recordset, miaQuery As
QueryDef
Set WS = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Set conAHE = WS.OpenConnection("AHE", dbDriverNoPrompt, True,
"ODBC;DATABASE=AHE_STD;UID=sa;PWD=;DNS=AHE")
Set miaQuery = conAHE.CreateQueryDef("")
With miaQuery
.Prepare = dbQPrepare
.Sql = "SELECT SDSDOC_MAST.MVSERIAL, MVNUMDOC, MVDATDOC, MVCODCON,
ANDESCRI, Left([anindweb],6) AS WEBcli, ANNAZION, Left([ddnomdes],6) AS
WEBdes, " & _
"Mid([ddnomdes],9) AS NOMdes, MVAIMPN1, MVAIMPN2, MVSPEINC,
MVAIMPS1, MVAIMPS2, MVCODART, MVDESART, MVQTAMOV, MVPREZZO, MVVALMAG " & _
"FROM ((SDSDOC_MAST LEFT JOIN SDSCONTI ON
(SDSDOC_MAST.MVTIPCON = SDSCONTI.ANTIPCON) AND (SDSDOC_MAST.MVCODCON =
SDSCONTI.ANCODICE)) " & _
"INNER JOIN SDSDOC_DETT ON SDSDOC_MAST.MVSERIAL =
SDSDOC_DETT.MVSERIAL) LEFT JOIN SDSDES_DIVE ON (SDSDOC_MAST.XXTIPCON =
SDSDES_DIVE.DDTIPCON) " & _
"AND (SDSDOC_MAST.XXCODCON = SDSDES_DIVE.DDCODICE) AND
(SDSDOC_MAST.XXCODDES = SDSDES_DIVE.DDCODDES) " & _
"WHERE [MVTIPDOC] In ('VFACC','VFDIF','VFTUE') AND
Year([mvdatdoc])=? AND Month([mvdatdoc])=? " & _
"ORDER BY MVDATDOC, MVNUMDOC;"
.Parameters(0).Direction = dbParamInput
.Parameters(1).Direction = dbParamInput
.Parameters(0) = 2009
.Parameters(1) = 10
.Execute
End With
Set DOC = miaQuery.OpenRecordset(dbOpenSnapshot)
.....