Search Postgresql Archives

Re: MS-Access and Stored procedures

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux