How to execute PostgreSQL function from VBA code?

Question

How can I execute a function named Test1 that is stored in PostgreSQL from VBA code?

For example, we have a function definition as follows:

CREATE OR REPLACE FUNCTION "public"."Test1" (
)
RETURNS bit AS
$body$
BEGIN
    INSERT INTO test ("name") VALUES ('1');
RETURN 1;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Now I'm trying to execute this function this way:

Function TestCall()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strSQl As String

strSQl = "SELECT * FROM Test1();" 

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
'this doesnt work as well: syntax error'
dbs.Execute strSQl 


If Not (rst.BOF And rst.EOF) Then
    do some work here
End If

End Function

But I'm getting Syntax Error near FROM. I have no idea how to execute this, how can it be done?

HansUp · Accepted Answer · 2010-10-05 15:59:00Z

2

You can use an Access "pass-through" query for your PostGreSQL function.

I created your function and table on my PostGreSQL server. Then created a new pass-through query in Access. I used a PostGreSQL statement for the query's SQL property.

SELECT Test1();

(I didn't need a FROM clause.)

On the query property sheet, I assigned the ODBC connection string, and chose Yes for the "Returns Records" property. The query can then be run from the Access user interface, or used from VBA code to open a DAO recordset based on that query.

answered Oct 5, 2010 at 15:59

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dariusz Tarczynski Over a year ago

Also very interesting solution.

Kevin Ross · Accepted Answer · 2010-10-05 12:58:43Z

1

It is failing because you have set dbs to the current database. When you execute this statement access will look for the table “Test1()” and throw a hissy fit when it cant find it.

I am unsure as to how to do this with postgre but this is how I have gone about doing a similar thing with SQL server so I assume it will be the same but with a different connection string

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql

And so on

answered Oct 5, 2010 at 12:58

Kevin Ross

7,2253 gold badges24 silver badges27 bronze badges

Comments

Avagut · Accepted Answer · 2016-09-19 21:22:58Z

Way old post nonwithstanding, I came here via Google and finally managed to put together a function that works for me, it may help someone else with the same problem: In a public Module in MS Access, save the following

Option Compare Database
Public Function ProcessSTP_Fn(PostgresFnString As String)
    Dim qdf As DAO.QueryDef, rst As DAO.Recordset
''Server Settings
   On Error GoTo ErrHandler
    myServerName = "127.0.0.1"
    myDatabaseName = "my_db"
    myUserName = "my_user"
    myPassword = "my_pass"

''Connection String Components
    sqlConnString = ""
    sqlConnString = sqlConnString & "ODBC;Driver={PostgreSQL ANSI};"
    sqlConnString = sqlConnString & "Server=" & myServerName & ";"
    sqlConnString = sqlConnString & "Port=5432;"
    sqlConnString = sqlConnString & "Database=" & myDatabaseName & ";"
    sqlConnString = sqlConnString & "Uid=" & myUserName & ";"
    sqlConnString = sqlConnString & "Pwd=" & myPassword & ";"

''Create QueryDef and run the Postgres Function
    Set qdf = CurrentDb.CreateQueryDef("")
        qdf.Connect = sqlConnString
        qdf.SQL = PostgresFnString ''From the parameters
        qdf.ReturnsRecords = True
        Set rst = qdf.OpenRecordset
            ''Return any results expected
        rst.Close
        Set rst = Nothing
    Set qdf = Nothing
    Debug.Print "Query: " & PostgresFnString & " Passed Sucessfully At " & Now()
   Exit Function
ExitHandler:
   On Error GoTo 0
   ErrorState = True
   Exit Function
ErrHandler:
    Dim MyError As Error
    MsgBox Errors.Count
    For Each MyError In DBEngine.Errors
      With MyError
        MsgBox .Number & " " & .Description
      End With
    Next MyError
    GoTo ExitHandler
End Function

Public Sub TestPostgresFn()
    PostgresFnString = "SELECT * FROM Test1();"
    Call ProcessSTP_Fn(PostgresFnString)
End Sub

Collectives™ on Stack Overflow

How to execute PostgreSQL function from VBA code?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related