How to execute PostgreSQL funcion from VBA code?

Question

How to execute funcion named Test1 that is stored in PostgreSQL from VBA code?

For example. We have function definition as follow:

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 it that 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. Thanks in advance.

Answer 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

Answer 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.