Delete Query to SQL Database Times Out

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2003 front end to an SQL 2000 database. On an employee form
with a subform for related records that I include a button to delete the
employee record. This will run a query to delete the related records and
then another to delete the employee record. I have a couple of users that
seem to always get a time out error when trying to delete an employee. I
only had the problem happen once for me and can't duplicate it. I've never
had this happen before and I've written lots of databases.

Has anyone experienced this? Is it possible that the record is somehow
locked and that is why the query won't work? I checked all SQL user rights
and they are identical so I know that is not the problem.


John
 
There could be some reasons for that error
1. The SQL server works slow, to many users loged at the same time, or to
many process working at the same time, or the server is to slow
2. To much traffic on the network

To take care of the traffic problem you can do one thing, change the queries
to pass through query, that way the delete command will be executed on the
server, it much faster with less traffic on the server, try it.

Almost every query that I have with SQL tables I changed to Pass through
query, and the different is amaizing
 
I tried using pass through queries and it still does not work on the other
users PC. The database is stored on a network location and all users run it
from there so there is nothing different about the front end. I just don't
get what is going on here. It seems like Access is not talking to the SQL
server for some reason. The odd thing is that adding new records works fine
as does deleting the records associated with the employee record but the
employee record time out if we try and delete that.

I'm lost at this point and don't know what to try next.

John
 
I can advice you of things I would check in that case
1. The permission this users have on the table, in the SQL
2. The owner of the table, is it dbo?
3. In the SQL declaration in the ODBC, check the odbc time out for this users
4. In the pass through query, you can define the ODBC time out, so you can
extand it
 
1. The permissions for all users in SQL is the same.
2. The table owner is dbo.
3. ODBC settings for all users are the same.
4. ODBC time-out is set to 60 seconds which should be plenty of time. I
even set it as high as 5 minutes and it still times out.

I just don't get why it works ok for me but not for these 2 users. Only 5
people have access to the database and network traffic is not so heavy that
it should cause a problem.

John
 
Back
Top