Re: Run Stored procedure - function from VBA

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 <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>>:
>
>
>
>> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek(at)gmail(dot)com
>> <mailto:jaryszek(at)gmail(dot)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.