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

[Biguana]

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

[Trevor Best]

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]

[Trevor Best]

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]

[Biguana]

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

[Biguana]

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

[Trevor Best]

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]