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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] De la part de Ets ROLLAND
Envoyé : jeudi, 12. mai 2005 17:28
À : pgsql-general@xxxxxxxxxxxxxx
Objet : 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
