> 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