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