Thread: Access VBA-Code and pgsql-function

Hello,
    Heres a function I use for pass through select queries. cs is the
connection string and pg_bend is an open ODBC database connected to pg. You
should just be able to use currentdb (I'm experimenting to find the best way
of doing this).

Public Function ptsq(sqlstr As String) As Recordset
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = pg_bend.CreateQueryDef("", sqlstr)
qdf.Connect = cs
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Set ptsq = rst
Set rst = Nothing
Exit Function
End Function

This is actually in a form that is opened and creates a login dialog,
records the password and user name in a connection string (in mem only) and
then uses this to connect, build tabledefs, etc... The first string is with
out user name and password and the second is with.
cs = "ODBC;DRIVER={PostgreSQL};DATABASE=" & DBName & ";SERVER=" & DBSrvr &
";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="

csp = "ODBC;DRIVER={PostgreSQL};UID=" & unm & ";PWD=" & pd & ";DATABASE=" &
DBName & ";SERVER=" & DBSrvr &
";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="

Set pg_bend = DBEngine.Workspaces(0).OpenDatabase("", False,
dbDriverNoPrompt, csp)

These three are global to the form module.
This form then hangs around (hidden) and when I want to do a pass through
query I just send use the function (btw, I call the form hub) eg:
    Set rs = Forms!hub.ptsq("select edtodofunc();")
Hope this helps,
- Stuart

> -----Original Message-----
> From:    DI Hasenöhrl [SMTP:i.hasenoehrl@aon.at]
> Sent:    Tuesday, May 22, 2001 9:32 PM
> To:    pgsql-odbc@postgresql.org
> Subject:    Access VBA-Code and pgsql-function
>
> Hello,
>
> in my VBA code I execute a plpgsql-function via a pass through query and
> ODBCdirect, but I don't know, how I can use the returned value for further
> calculations in VBA. Is there a possibility to realize?
>
> Please, can anyone give me some help?
>
> Thanks,
> Irina
>
> E-Mail: i.hasenoehrl@aon.at <mailto:i.hasenoehrl@aon.at>