Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.

[arthurjr07]

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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.

[vladimir]

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.

[Karen Hill]

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?


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Transactions, PostgreSQL and MS Access front end.

[Karen Hill]

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?


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.

[Bruce Momjian]

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
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.

[Karen Hill]

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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster