Acces front end SQL back end Update problem

[hockley]

Good morning
I am strong on Access but weak on SQL
My problem is as follows:

1) I am upgrading an existing MS Access system which is not handling the load

2) I have created an Access DB front end

3) I have created an Access back end and imported all the data from the "old system"

4) All testing went according to plan without any issues - so the Access front end is OK when connected to the Access backend

5) The IT department created a SQL 2005 database

6) I exported each of the 163 tables in the backend to the SQL DB - each table has a primary key as the first field in SQL - I allowed SQL to create the field types - - there are no joins or relationships

7) After exporting the data I opened each of the tables and checked the data - all OK

8) User testing went OK for all the tables except for 3 - most of the tables are small but the 3 that failed all have more than 1200 records and multiple fields

9) All three tables are failing in the same place - the code that is failing works fine on 160 tables and I have been using the code in other applications for years

10) I cut the number of rows down to 800 for each of the tables and all works OK - as soon as I add the full data the error returns

11) I cannot put an error trace on the SQL DB myself - but it can be arranged by the IT department

This is the DAO code - all it does is open a Recordset on a SQL linked table, update a field to the current user name "John" and close the Recordset


Dim curr_db As Database
Dim curr_rs as Recordset
Dim hold_crit as Variant
Dim curr_rec as Long
Dim num_of_recs as Long

Set curr_db = DBEngine.Workspaces(0).Databases(0)

hold_crit = "SELECT * FROM t_broker where [broker_id] = 352"
Set curr_rs = curr_db.OpenRecordset(hold_crit, dbOpenDynaset, dbSeeChanges)
If curr_rs.RecordCount > 0 Then
curr_rs.Edit
curr_rs!cu = "John"
curr_rs.Update
endif
curr_rs.Close


The "curr_rs.Update" command will not execute - it just hangs - after 30 seconds the "ODBC - Call failed" error appears

The strange part is - if I step through the code using the F8 key there is no problem - why ?

If I put a 20 second "Sleep" command in after the "curr_rs.Update" command it works OK - but this is obviously not the way to fix it


Any help to get over this problem will be appreciated

Regards

Ken

 

[Axworthy]

Ken,

I had a similar problem with an Access front end / SQL Server back end. Wherever my combo boxes would be based on a recordset of over 1000 or so records, it seemed that SQL Server would send part of the query result and leave the underlying table locked so no other users could update the table.

I found this out by going into SQL Server's Management Studio, looked under Management then Activity Monitor. I could see that the processes to populate the combo boxes kept the table locked.

My workaround for this was to do an rst.MoveLast then an rst.MoveFirst which forced SQL Server to send all the records and then it released the lock.

In your circumstance, could you do this instead:

docmd.RunSQL "update t_broker set cu = 'John' where broker_id = 352"

 

[seaport]

I had the same problem before and was only to resolve it partially.

Regarding releasing the lock, I agreed with Axworthy. I used the similar technique to force a continuous form to release the lock.

Creating an index will help, if the broker_id is not an indexed column yet.

Adding a timestamp field to the table may help.

It seemed that once a lock was formed, there is no way to go around it. Whether you make the update with DAO, or docmd.runsql, or even a stored procedure on the sql server, it does not matter and I still got the "ODBC timeout".

In my case, the "ODBC" error occurred irregularly and infrequently and it was very hard to me to diagnose. What I ended up doing was to create a special screen for the office manager to see which users created the lock. The manager will ask that user to close the Access and go back in. It was not perfect but worked.

 

[seaport]

A link that may help.

http://msdn.microsoft.com/en-us/library/bb188204(SQL.90,printer).aspx#optaccsql_topic3

 

[hockley]

Hi All

Thank you very much for your interest in our problem.

We have found someone locally who can solve the immediate problem and supply the ongoing support.

To others that may encounter this problem - we had a list_box on screen that was simply showing records from the t_broker table - as soon as we changed the rowsource of the list_box the lock was removed

So we built two generic small procedures - one to remove the rowsource from the list_box just before the .update command and then another to restore the rowsource after the .update command

The error only occurs when there is a fair amount of data - say 1200 records or more

Thanks for all the suggestions