I have an Access FE and an SQL BE, The tables are linked via ODBC everything works fine. Every now and then a table will stop allowing new records. Tables are of various amounts of records even as small as 16 today. Refreshing the link doesn't fix it. I have to delete the link and get external data again.

Any clues as to why?

Does your SQL Server database allow the database size and transaction log to automatically grow? Also, do you truncate/shrink your transaction log after a backup or on a regular basis if it is not allowed to automatically grow?

----

Or ... it this a time out issue?

Side note: If the transaction log is full ... it makes no difference as to the size of the table, since a full transaction log will pretty much halt all functionality of all tables.

When this happened,

1. Did you check the "Current Activity" in EM/SSMS to see if deadlocks or blocking connections occurred?

2. Did you try to edit one of the existing records and see if edit was still allowed?

3. Do you have an (SQL Server) TimeStamp Field in your Table?

I have no access to the SQL server other than thru SQL server management. But, if it was the server then wouldn't it effect all my tables at the same time? When this happens I can't edit or add. But, delete the table from the DB, which only removes it from the DB and not the server and then link it again and everything works fine.

Not sure what you meant by "SQL Server management" here. If you meant SSMS (SQL Server Management Studio) or EM (Enterprise Manager - for SQL Server 2000) then you have access to the "Current Activity" in one of the branches of the SQL Server explorer. The "Current Activity" shows you users currently connect to the server/databases, locks (and types of locks) placed on databases/Tables by various ProcessIDs (spids) and if there are deadlocks or blocking/blocked connections there are visual indications, i.e. different lock icons so that you can see deadlocks and blocking/blocked connections easily.



No. Deadlocks and blocking/blocked connections can happen on a single Table or two (e.g. deadlocks due to locking required for R.I.) but other Tables are fine. I have never seen all Tables not accessible at the same time due to deadlocks or blocking/blocked connections.



It is likely that if you close the database, leave it for a while (to give SQL Server time to clear the deadlocks, blocking/blocked connections if your database/process is a party involved in these - SQL Server is fairly good at clearing these once a connection is released but it does take time) and re-open your database then the Linked Table will work fine again without deleting and re-creating.


See also UA Topic How to Transition to SQL Express regarding TimeStamp Field which I use in every SQL Server (User) Tables if I used ODBC-linked Tables to access these SQL Server Tables.

will look for all that if and when it happens again.

thanks everyone

You're welcome... Glad we could contribute a few ideas...

BTW, if you have access to the "Current Activity" and if you can see deadlocks or blocking/blocked connections, you can clear these manually, usually by killing one of the involved spids (Process ID).

after talking to the user who had the problem, he said that he still had the AC2003 version on one machine and the AC2007 version on another. There are 2 different FE but both use the same BE. He only ran into the problem on the AC2007 as I did as well. But he said he finished what he needed with the AC2003 FE. That makes me think it has something to do with the FE but wouldn't even venture a guess. I should just stay to being a network engineer and leave the coding to the experts.