Re: Run Stored procedure - function from VBA

[Rob Sargent]

On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA?
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsarg...@gmail.com 
<mailto:robjsarg...@gmail.com>>:



    On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jarys...@gmail.com
    <mailto:jarys...@gmail.com>> wrote:

    Hi Guys,

    i have example function :

    CREATE OR REPLACE FUNCTION totalRecords ()
    RETURNS integer AS $total$
    declare
    total integer;
    BEGIN
       SELECT count(*) into total FROM COMPANY;
       RETURN total;
    END;
    $total$ LANGUAGE plpgsql;

    and i want to run it from VBA using odbc connection.

    What is the best way to use it ?

    something like this:

    |Dim dbCon asnew ADODB.Connection Dim rst asnew ADODB.Recordset
    Dbcon.connectionstring=”Your connection string goes
    here!”Dbcon.openRst.openstrsql|
    where strsql is "Select * from totalRecords" or this is not a
    good solution?

    Best,
    Jacek

    You need the parentheses after the function name: “select * from
    totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for 
stored procedures/function, which you could build once and re-use with 
new parameter values if you're going to call this repeatedly.