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 5 4105
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 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]
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
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 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] This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Freelobly Li |
last post by:
Hi all, I have encountered two problems when using linked tables; the linked
table is connected to SQL server 2000.
1) How can I add the N prefix of a string constant in a query in order to...
|
by: annie |
last post by:
Hi all,
I have recently ported my Access 2000 app to SQL Server, keeping the
Access client as the front end using linked tables.
I am also using triggers on my SQL tables to trap orphan...
|
by: Neil Ginsberg |
last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked
tables do not have the password stored in them, so the first time the user
accesses them, they need to enter the SQL...
|
by: Bri |
last post by:
Greetings,
I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record...
|
by: Zlatko Matić |
last post by:
Hi!
What happens with linked tables if they were linked using File DSN, when I
copy the Access file on some other PC without File DSN ?
What is the difference between DSN on linked tables and...
|
by: Biguana |
last post by:
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...
|
by: Wayne Wengert |
last post by:
I am trying to create a VB.NET Windows application to move some data from a
local Access DB table to a table in a SQL Server. The approach I am trying
is to open an OLEDB connection to the local...
|
by: TC |
last post by:
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP,...
|
by: mgworek |
last post by:
access xp, sql server 2000.
Just converted entire database to sql and keeping access front end.
Problem is only with one table.
inserting a new record works fine. If I go back to that record...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |