By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Transactions, PostgreSQL and MS Access front end.

P: n/a
>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?

Apr 24 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Apr 24 '06 #2

P: n/a
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

May 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.