Hi guys,
I'm trying to run a passthrough query in MS Access, where the parameters are
from the current form. I have setup QueryDefs and can connect to the server,
but I'm getting
"Run-time error '3421':
Data type conversion error"

when I try clicking on the button to run the SP on the server. I suspect
that it is because the parameters are from combobox's which might be seen as
text rather than int4's like I have specified in the SP. Does anyone have
any ideas on how to get my PT query to work?

Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As
DAO.Recordset
Set MyDb = CurrentDb()
Set MyQry = MyDb.CreateQueryDef("")

MyQry.Connect =
"ODBC;DRIVER={PostgreSQL};DATABASE=xxxxxxxx;SERVER =xxxxxxxxxxx;PORT=5432;Uid=xxxxxxxxxxxx;Pwd=xxxxxx xx;"

MyQry.ReturnsRecords = True

If Forms![frm_MainMenu].[CurrentReport] = 18 Then
MyQry.SQL = "select * from reports
([Adviser_ID.Text],[Provider_ID.Text],[Introducer_ID.Text],[PlanGroup_ID.Text],[PlanType_ID.Text],[DateSpecific_Start.Text],[DateSpecific_End.Text],
Child24.Form.CurrentRecord , Child26.Form.CurrentRecord) as
(employee_first_name varchar,employee_last_name varchar,date_issued
date,client_first_name varchar,client_middle_names varchar,client_surname
varchar,tblplantypes.plantype_group varchar,plangroups.plangroups_groupvarchar,
tblproviders.provider_company varchar,policy_number varchar,sum_assured
numeric,benefit varchar, premium numeric,brokerage numeric, comments text);"
End If

MyQry.Execute (MyQry.SQL)

I've attached my SP if that helps.

Cheers,
Ben


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> MyQry.SQL = "select * from reports
>
([Adviser_ID.Text],[Provider_ID.Text],[Introducer_ID.Text],[PlanGroup_ID.Text],[PlanType_ID.Text],[DateSpecific_Start.Text],[DateSpecific_End.Text],
> Child24.Form.CurrentRecord , Child26.Form.CurrentRecord) as
> (employee_first_name varchar,employee_last_name varchar,date_issued
> date,client_first_name varchar,client_middle_names varchar,client_surname
> varchar,tblplantypes.plantype_group varchar,plangroups.plangroups_groupvarchar,
> tblproviders.provider_company varchar,policy_number varchar,sum_assured
> numeric,benefit varchar, premium numeric,brokerage numeric, comments text);"


I think I see your problem here. You still need to parse you sql strings with &.
So where ever you need to pass a integer it must be passed like:

"select * from reports (" & [adviser_ID.text] & ", .... " -- for integer parameters and

",'" & [dateSpecific_end.Text] & "', .... " -- for string parameters.


This link http://www.fontstuff.com/access/acctut20.htm show how to incorporate Access controls
into the sql string.

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings