Thread: Transactions, PostgreSQL and MS Access front end.

Transactions, PostgreSQL and MS Access front end.

From
"Karen Hill"
Date:
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?


Re: Transactions, PostgreSQL and MS Access front end.

From
Bruce Momjian
Date:
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. +

Re: Transactions, PostgreSQL and MS Access front end.

From
"Karen Hill"
Date:
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')"


Re: Transactions, PostgreSQL and MS Access front end.

From
vladimir
Date:
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

Re: Transactions, PostgreSQL and MS Access front end.

From
"Karen Hill"
Date:
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?


Re: Transactions, PostgreSQL and MS Access front end.

From
arthurjr07@gmail.com
Date:
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