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 internal use.

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(dot)Graiss(at)bal(dot)bmlfuw(dot)gv(dot)at]
Envoyé : mercredi, 12. novembre 2003 12:26
À : 'pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly