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

Access won't commit (to SQL Server linked table)

P: n/a
Hi,

Wondering if anyone can help with an append query I'm running in
Access. It should add records from an Access table to a linked SQL
Server table (linked via ODBC). I run the query by double clicking it
in Access - nothing clever.

Trouble is, the first thing Access does when I run the query (judging
by the SQL trace) is "set implicit_transactions on". Then, after the
query has run, it just doesn't commit the transaction.

In fact, the transaction stays open until the whole Access App (not
just the database) is closed. Obviously this means the table is locked
to all other connections in the mean time, and the appended data is
lost in the end anyway (because the transaction is never committed).

This seems really buggy to me, and I wondered if anyone had seen
it\solved it before.

Thanks,

Tim

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Biguana wrote:
Hi,

Wondering if anyone can help with an append query I'm running in
Access. It should add records from an Access table to a linked SQL
Server table (linked via ODBC). I run the query by double clicking it
in Access - nothing clever.

Trouble is, the first thing Access does when I run the query (judging
by the SQL trace) is "set implicit_transactions on". Then, after the
query has run, it just doesn't commit the transaction.

In fact, the transaction stays open until the whole Access App (not
just the database) is closed. Obviously this means the table is locked
to all other connections in the mean time, and the appended data is
lost in the end anyway (because the transaction is never committed).

This seems really buggy to me, and I wondered if anyone had seen
it\solved it before.


I've not noticed this but I do notice if the table has an identity
column and you insert into it, it turns IDENTITY_INSERT ON before the
query but doesn't turn it off again afterwards.

You might try executing the query from VBA e.g.
with currentdb
.execute "queryname",dbSeeChanges + dbFailOnError
end with

or wrap it in your own transaction although now that SQL Server supports
multi level transactions that might not work either.

--
[OO=00=OO]
Nov 13 '05 #2

P: n/a
Biguana wrote:
Hi,

Wondering if anyone can help with an append query I'm running in
Access. It should add records from an Access table to a linked SQL
Server table (linked via ODBC). I run the query by double clicking it
in Access - nothing clever.

Trouble is, the first thing Access does when I run the query (judging
by the SQL trace) is "set implicit_transactions on". Then, after the
query has run, it just doesn't commit the transaction.

In fact, the transaction stays open until the whole Access App (not
just the database) is closed. Obviously this means the table is locked
to all other connections in the mean time, and the appended data is
lost in the end anyway (because the transaction is never committed).

This seems really buggy to me, and I wondered if anyone had seen
it\solved it before.


I've not noticed this but I do notice if the table has an identity
column and you insert into it, it turns IDENTITY_INSERT ON before the
query but doesn't turn it off again afterwards.

You might try executing the query from VBA e.g.
with currentdb
.execute "queryname",dbSeeChanges + dbFailOnError
end with

or wrap it in your own transaction although now that SQL Server supports
multi level transactions that might not work either.

--
[OO=00=OO]
Nov 13 '05 #3

P: n/a
Cheers.

Unfortunately the query was running from VBA originally when the
problem was discovered. I started it running "manually" to try and
simplify things. The weird thing is, it works sometimes but I can't
work out what why.

Anybody else got any ideas? Maybe I should try a SQL Server group.

Tim

Nov 13 '05 #4

P: n/a
Cheers.

Unfortunately the query was running from VBA originally when the
problem was discovered. I started it running "manually" to try and
simplify things. The weird thing is, it works sometimes but I can't
work out what why.

Anybody else got any ideas? Maybe I should try a SQL Server group.

Tim

Nov 13 '05 #5

P: n/a
Biguana wrote:
Cheers.

Unfortunately the query was running from VBA originally when the
problem was discovered. I started it running "manually" to try and
simplify things. The weird thing is, it works sometimes but I can't
work out what why.

Anybody else got any ideas? Maybe I should try a SQL Server group.


You could try, but they'll probably send you back here :-) But then
again, it wouldn't hurt to try.

--
[OO=00=OO]
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.