Thread: Transactions, PostgreSQL and MS Access front end.

From Access I'd like to run pass the following from MS Access to
PostgreSQL 8.1 using VBA:

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

It won't let me.  Any ideas solutions?

Karen Hill wrote:
> >From Access I'd like to run pass the following from MS Access to
> PostgreSQL 8.1 using VBA:
>
> BEGIN;
> UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> COMMIT;
>
> It won't let me.  Any ideas solutions?

What error does it show?

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Karen Hill wrote:
> > >From Access I'd like to run pass the following from MS Access to
> > PostgreSQL 8.1 using VBA:
> >
> > BEGIN;
> > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> > COMMIT;
> >
> > It won't let me.  Any ideas solutions?
>
> What error does it show?
>

Error on character 7.

I suspect it only allows one SQL statement to go through and thinks
everything after the BEGIN; is an error.  I thought of creating a
function in pl/pgsql  that would allow me to do this.  Something like
this in postgresql: NOTE:pseudocode
function(sql_statement_1, sql_statement_2){
BEGIN;
sql_statement_1;
sql_statement_2;
COMMIT;
}

And then I'd run that function from access: NOTE:pseudocode

DoCmd.RunSQL "function('UPDATE accounts..WHERE acctnum = 12345',
'UPDATE accounts...WHERE acctnum = 7534')"

Karen Hill wrote:

>From Access I'd like to run pass the following from MS Access to
> PostgreSQL 8.1 using VBA:
>
> BEGIN;
> UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> COMMIT;
>
> It won't let me.  Any ideas solutions?

it should work in pass-through query, and the query could be generated by
code (AFAIK, it has been a while i did Access).

vlad

Cool.  I knew ADO could do transactions on Access's JET database
engine, but didn't know they could do so on another RDBMS like
PostgreSQL.  So basically I can use the BeginTrans and CommitTrans to
do the work of PostgreSQL's BEGIN; and COMMIT;

On a adjacent topic, how does PostgreSQL know that BeginTrans and
CommitTrans are psuedonyms for BEGIN and COMMIT?  Is it the ODBC driver?

Try to use ADO

Dim con as ADODB.Connection
set con = new ADODB.Connection
con.Open "DRIVER={PostgreSQL};
                SERVER=ipaddress; port=5432;
                DATABASE=dbname;
                UID=username;PWD=password;"

con.BeginTrans
con.Execute "UPDATE accounts SET balance = balance + 100.00
                     WHERE acctnum = 12345"
con.Execute "UPDATE accounts SET balance = balance - 100.00
                     WHERE acctnum = 7534"
Con.CommitTrans