Re: MS-Access and Stored procedures - Mailing list pgsql-general

""Zlatko Matic"" <zlatko.matic1@sb.t-com.hr> wrote in message
news:003401c5573f$10f1b7c0$61841dc3@zlatkovyfkpgz6...
>I was using ADO command object and both refresh method and method with
>creating parameter object while working with Access Project...but I didn't
>try to use it with PostgreSQL...
> I would rather like to have all queries on client side anyway. Therefore I
> use pass-through queries. But it doesn't allow using parameters (execept
> by concatenation). Also, you can't base subforms on pass-through queries,
> so now I use strange combination of local tables, append queries with
> parameters based on pass-through queries etc. It works but I'm aware that
> it is not very clever:)...

I find it hard to imagine why you would want to do it that way.  Perhaps if
you explain what you are trying to do, others can offer suggestions.

> I think that it would be great if pass-through queries could accept
> parameters. That would be a powerfull way for executing queries on client,
> while keeping all the code on front-end side...But I doubt that Microsoft
> will work on further Access improving anymore. It seems that Access is
> left behind while VS.NET is top technology. Too bad...
>
> IS there any good book covering MS Access usage as front-end for different
> database servers except MSDE ?

I find the "Access XX Developer's Handbook" (where XX is the Access version)
gives the best all round coverage of Access development issues, including
client-server.

>
> Do you have form/subform/subform...based on stored procedures ? If so, how
> do you synchronize form with subform ?
>
>
> Greetings,
>
> Zlatko
>
>
> ----- Original Message -----
> From: "Herv� Inisan" <typo3@self-access.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, May 12, 2005 11:06 PM
> Subject: Re: [GENERAL] MS-Access and Stored procedures
>
>
>>> Hello...This is very interesting. I have also asked myself
>>> how to prepare and execute stored procedures on POstgre from
>>> MS Access.
>>> Could you, please, give some example of Postgre function with
>>> parameters that is executed as stored procedure from MS
>>> Access? How would you pass parameters ? Using ADO Command object?
>>
>> AFAIK, there are 2 ways to send parameters from Access to a PG function,
>> using ADO:
>>
>> 1. Write the parameters as the CommandText string:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
>> cmd.CommandType = adCmdStoredProc
>> cmd.Execute
>> Set cmd = Nothing
>>
>> The CommandText string can be the result of a concatenation:
>> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
>> ")"
>>
>> 2. Another way is to use "true" ADO parameters:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction"
>> cmd.CommandType = adCmdStoredProc
>>
>> Dim prm1 As ADODB.Parameter
>> Set prm1 = New ADODB.Parameter
>> With prm1
>>    .Type = adVarChar
>>    .Direction = adParamInput
>>    .Value = "another string sent to PG"
>>    .Name = "param1"
>>    .Size = 30
>> End With
>>
>> Dim prm2 As ADODB.Parameter
>> Set prm2 = New ADODB.Parameter
>> With prm2
>>    .Type = adInteger
>>    .Direction = adParamInput
>>    .Value = 25
>>    .Name = "param2"
>>    .Size = 0
>> End With
>> cmd.Parameters.Append prm1
>> cmd.Parameters.Append prm2
>> cmd.Execute
>> Set cmd = Nothing
>>
>> Voil�!
>> -- Herv� Inisan, www.self-access.com
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>