Re: calling a pg-function from vba (Access 2002)

Hello,

Here is a small example:

Two routines; the first stores the pass-through query in order to use is from a form or a report, and a second for
internaluse. 

I hope this helps.

Note: global_dsn_name() supplies the DSN name of your ODBC connection.

-----------------------------------------

Sub query_store(query As String, p As String)
On Error GoTo query_storeError

    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef

    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

query_storeExit:
    Exit Sub

query_storeError:
    HandleErrors "Error in query_store."
    Resume query_storeExit
End Sub

-----------------------------------------

Sub query_run(query As String, p As String)
On Error GoTo query_runError

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

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

    With MyRecordset
        Do While Not .EOF

            Debug.Print _
            MyRecordset("col1") & " / " & _
            MyRecordset("col2") & " / " & _
            MyRecordset("col3") & " / " & _
            MyRecordset("col4") & " / " & _
            MyRecordset("col5")

            .MoveNext
        Loop
    End With

    MyRecordset.Close
    Set MyRecordset = Nothing

    MyConnection.Close
    Set MyConnection = Nothing

    MyWorkspace.Close
    Set MyWorkspace = Nothing

query_runExit:
    Exit Sub

query_runError:
    HandleErrors "Error in query_run."
    Resume query_runExit
End Sub

-----------------------------------------


-----Message d'origine-----
De : Wilhelm Graiss [mailto:Wilhelm.Graiss@bal.bmlfuw.gv.at]
Envoyé : mercredi, 12. novembre 2003 12:26
À : 'pgsql-odbc@postgresql.org'
Objet : [ODBC] calling a pg-function from vba (Access 2002)


Hello,

We have a question concerning the vba-code which is posted on
http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-accessvba:

We have a function in postgres (Version 7.3.2) and would like to call it
from vba on Access 2002 (a.k.a XP) with different parameters each time.

We searched the Net and played with the code, but with no success. The
connection to postgres works, the problem is how to trigger the "select
myfunction(param1, param2, param3)" - thing from vba...

The main problem is, I think, that it is our first try in vba ;-) The other
time we spend the time with php...

Would be great if somebody could help us, and thanks in advance!

Greetings from Austria!

Albin Blaschka, Wilhelm Graiss

--
===================================================
Wilhelm Graiss, Dipl. Ing.
Federal Reasearch Agency for Alpine Agriculture
8952 Irding, Styria
Austria, Europe


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly