Thread: Passing parameters to postgreSQL from MS Access 2000

Passing parameters to postgreSQL from MS Access 2000

From

"ben sewell"

Date:

22 August 2006, 13:57:28

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=xxxxxxxx;"

   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_group varchar,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

Attachment

reports_sp.txt

Re: Passing parameters to postgreSQL from MS Access 2000

From

Richard Broersma Jr

Date:

23 August 2006, 20:05:12

>     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.