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

From Hervé Inisan
Subject Re: MS-Access and Stored procedures
Date
Msg-id 20050512161315.E346117347F@postfix3-1.free.fr
Whole thread Raw
In response to MS-Access and Stored procedures  ("Ets ROLLAND" <ets@rolland-fr.com>)
List pgsql-general
> 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



pgsql-general by date:

Previous
From: "Philippe Lang"
Date:
Subject: Re: MS-Access and Stored procedures
Next
From: Tony Caduto
Date:
Subject: Re: Delphi 2005, Postgresql, ZEOS & optimistic locking