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?

[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

[DFS]

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?

You need to submit the pass-thru as one statement:
qSQL = "BEGIN;"
qSQL = qSQL & "UPDATE accounts SET balance = balance + 100.00 WHERE acctnum
= 12345;"
qSQL = qSQL & "UPDATE accounts SET balance = balance - 100.00 WHERE acctnum
= 7534;"
qSQL = qSQL & "COMMIT;"

Set qItem = db.CreateQueryDef("")
With qItem
.Connect = "ODBC;DSN=xxxx;UID=xxx;PWD=xxx;"
.SQL = qSQL
.ReturnsRecords = False
.Execute
.Close
End With
Set qItem = Nothing