Transactions, PostgreSQL and MS Access front end.

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')"

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

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?