Re: MS-Access and Stored procedures

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 kind of 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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] De la part de Ets ROLLAND
Envoyé : jeudi, 12. mai 2005 17:28
À : pgsql-general(at)postgresql(dot)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