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@xxxxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, May 12, 2005 6:15 PM
Subject: Re: 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@xxxxxxxxxxxxxx
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
