Re: 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?

Greetings,

Zlatko

----- Original Message -----
From: "Hervé Inisan" <typo3(at)self-access(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, May 12, 2005 6:15 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures

>> How can I use stored procedures (functions) with MS-Access
>> 2002 connected to PostgreSQL 8.0 ?
>
> An alternative to Philippe's solution is to use ADO.
> Here is an sample function :
> (assuming ActiveX Data Object lib is checked in the Tools/References menu)
>
> Function ADO_PG()
> Dim cnn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim strSQL As String
>
> ' Open connection
> Set cnn = New ADODB.Connection
> cnn.CursorLocation = adUseClient
> cnn.ConnectionString = "DSN=<your ODBC DSN here>"
> cnn.Open
>
> ' Display resultset (SELECT...)
> Set rst = New ADODB.Recordset
> strSQL = "SELECT * FROM a_function_returning_rows()"
> rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
> While Not rst.EOF
> Debug.Print rst("one column name here")
>
> ' Next record
> rst.MoveNext
> Wend
> rst.Close
> Set rst = Nothing
>
> ' Execute function (e.g.: INSERT, UPDATE...)
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "another_pg_function()"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute
> Set cmd = Nothing
>
> ' Close resources
> cnn.Close
> Set cnn = Nothing
> End Function
>
> Of course, parameters can be sent to stored procedures.
>
> HTH,
> -- Hervé Inisan, www.self-access.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>