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

Hi,

1) The simplest way to call a function from MS Access is to use a "pass-through query", like:

SELECT * FROM public."search_article"();


2) If the parameter is/are dynamic, that's more complicated. You have to edit the query at run-time, like with this
kindof code: 

----------------
Sub search_store(query As String, p As String)
On Error GoTo search_storeError

    Dim MyDatabase As DAO.DataBase
    Dim MyQueryDef As DAO.QueryDef

    cmdSourisSablier

    Set MyDatabase = CurrentDb()
    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
    Set MyQueryDef = MyDatabase.CreateQueryDef(query)

    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    MyQueryDef.ReturnsRecords = True

    MyQueryDef.Close
    Set MyQueryDef = Nothing

    MyDatabase.Close
    Set MyDatabase = Nothing

search_storeExit:
    cmdSourisNormal
    Exit Sub

search_storeError:
    MsgBox "Error in search_store."
    Resume search_storeExit
End Sub
----------------

That's fine if your query is linked to a report, for example.


3) You can also call a function from code without using a pass-through query, just to retreive a result:

----------------
Function charge_disponible_semaine(code_etape As String, semaine As Integer, année As Integer) As Double
On Error GoTo charge_disponible_semaineError

    Dim MyWorkspace As DAO.Workspace
    Dim MyConnection As DAO.Connection
    Dim MyRecordset As DAO.Recordset
    Dim MySQLString As String
    Dim MyODBCConnectString As String
    Dim query As String

    query = "charge_disponible_semaine"

    Set MyWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    MyODBCConnectString = "ODBC;DSN=" & global_dsn_name() & ";"
    Set MyConnection = MyWorkspace.OpenConnection("Connection1", dbDriverNoPrompt, , MyODBCConnectString)
    MySQLString = "SELECT * FROM public." & """" & query & """" & "('" & code_etape & "', " & semaine & ", " & année &
");"
    Set MyRecordset = MyConnection.OpenRecordset(MySQLString, dbOpenDynamic)

    With MyRecordset
        If Not .EOF Then
            charge_disponible_semaine = MyRecordset("charge_disponible_semaine")
        Else
            charge_disponible_semaine = 0
        End If
    End With

    MyRecordset.Close
    Set MyRecordset = Nothing

    MyConnection.Close
    Set MyConnection = Nothing

    MyWorkspace.Close
    Set MyWorkspace = Nothing

charge_disponible_semaineExit:
    Exit Function

charge_disponible_semaineError:
    MsgBox "Error in charge_disponible_semaine."
    Resume charge_disponible_semaineExit
End Function
----------------


I hope this helps. One or two utility function are needed:

----------------
Public Function global_dsn_name() As String
    global_dsn_name = "you_dsn_name"
End Function

Public Function QueryExists(QueryName As String) As Boolean
On Error Resume Next

    QueryExists = IsObject(CurrentDb().QueryDefs(QueryName))

End Function
----------------


Philippe Lang

________________________________

De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Ets ROLLAND
Envoyé : jeudi, 12. mai 2005 17:28
À : pgsql-general@postgresql.org
Objet : [GENERAL] MS-Access and Stored procedures


Hello !

How can I use stored procedures (functions) with MS-Access 2002 connected to PostgreSQL 8.0 ?

Best regards.

Luc