Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-08-31 : 09:08:48
|
I have an Access query that appends some rows to a SQL Server table. The problem is that after the data is populated, there are X locks remaining on the SQL table and the developers can't do anything while they are in there. If I kill the process, then they can continue. What's the problem?Environment: Access 2003 Sp1 SQL Server 2000 8.00.818 SP3 Windows 2003 Canada DBA |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-31 : 10:23:08
|
| I seem to recall that when MS Access makes a connection (via odbc) to a sql server table, the table is exclusively locked for the duration of the connection! (not just during the transaction). That of course is devistating to concurrent sql server usage. You should either not allow access in that manor or perhaps only allow MS Access to connect to a staging table and then use a sql process to populate the actual table.Be One with the OptimizerTG |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-08-31 : 10:39:36
|
| The code was working until last week that we deployed a new compile of Access application. The Access query is not changed. The problem is like if I want to append 100 records, a number of 100 (or more) X locks appears on the table in SQL server. By ending the appendings, there should be a command from Access to SQL Server that update the indexes and unlock the table but this doesn't happen.Canada DBA |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-31 : 10:52:04
|
| I have no idea...If you don't get a response soon try posting this question in:Other Forums | MS AccessBe One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
|
|
X locks on the SQL table