delete on a linked table failure 1

[ykfc]

This is a VBA module created in an MS Access environment. This MS Access Db has a linked table called "lkTable" setup to access data in a MS SQL table (via odbc.)

The current codes read:
dim RS as DAO.recordset
set RS = currentdb.openrecordset("Select * from lkTable where xxx='xxxx'")
do
rem read data, create report based on data
rem no data modification statement
RS.delete
RS.movenext
if RS.eof then exit do
loop

That is a very simple piece of code and it simply delete the record after reading its information. The codes runs 99% of the time. But it gives out Runtime error 3156 occasionally when the statement RS.delete is run within the do loop.
The error message reads:
ODBC-delete on a linked table lkTable failed.

I bet the codes will never fail if this is the only process that access the underlying table.

What if there are other processes that will add/delete entries to the table? In fact I know there are and I know exactly what they are doing. Can I modify the above piece of codes so that it could always delete the records it has processed?

 

[jrbarnett]

Yes - after you finish the loop and close the recordset, run something like

DoCmd.RunSQL "delete from lkTable where xxx='xxxx'"

John

 

[ykfc]

<quote> docmd.runsql "delete from lkTable where..." </quote>

That is not what the application needs to do. Assume the above appliction selects ALL the 10 records when it starts the processing. We only want those 10 records being deleted after they are processed. In the middle of processing, another process may add 2 records to the same time with xxx="xxxx" but these two record are not supposed getting removed. The same table is accessed by more than one procees at the same time, got it?

 

[jrbarnett]

OK, if there is a likelihood of another process adding records at the same time, add a new field called DelRec of type varchar (1) to the source table.

Then, instead of rs.Delete, use
rs.Edit
rs!DelRec = 'Y'
rs.Update

then after the loop has completed and recordset closed, you can change my suggestion to say:

DoCmd.RunSQL "delete from lkTable where DelRec='Y'"

This way, only the records that you have processed will be deleted. Any added in the intervening period will not be removed, and so will get processed the next time it is run.

John

 

[ykfc]

Thank you jrbarnett for the suggestion and I'd prefer to code it in your way, at least I feel safe.

But in reality, is there any problem with the original codes? Can you explain if there is a real problem? Or if there might be a potential problem.

As what I'd made as an example. Suppose at the time the query statement is run there were 10 records. After processing 4 records those 4 were deleted. Then a second user adds 1 records. Is the existing software going to process another 6 or another 7 records? This is not a big task for me to simulate. And I found it only processes another 6 records. No problems, that is what I want it to behave.

But are we sure it will do the same thing in all situations? What if the number of records is 500 and not 10, and if this is a very large record? Is there any different?

 

[jrbarnett]

Apologies for the delay in replying.

Your original code works by deleting one record at a time after processing. This method is relatively slow in that the set oriented nature of SQL allows all to be removed together, and executing an SQL statement is far faster than using a Recordset object.
The overhead of opening, using and then closing a recordset object will impose significant performance hits on your application over the equivalent SQL option, especially if it is being used for real time data processing.

There is nothing wrong with your original code, it is just not the way I would do it if I were writing that application, for performance reasons.

One of the truisms about programming is that there are often multiple ways of accomplishing the same goal. This situation shows that exactly.

John

 

[ykfc]

Logically I am thinking along the same way as yours: performance matters. If I wrote it in the first place I won't do such a silly thing. At present the codes are running too slowly and definitely I will change it.

But have you got an answer to my other question? My concern with multi-user access to the underlying table.

I think the previous programmer coded that way to "better avoid" any problems when another process adds records to the same table it is accessing.

 

[jrbarnett]

I've just done a test to see if recordsets only operate on the data that's in them at the time of opening, or whether they take account of new data by:
1) Creating a test table and populating it with 10 records
2) Creating a small VBA procedure which opens it as a DAO recordset, debug.print's it and loops through, but at row no. 5 runs an insert query to add a new row to the table.

The new row was included in the debug.print statements, so it does include the output.
I've not really had to experiment with the record locking options in the OpenRecordset parameters, but it should be possible to lock the data to generate an error if another process acceses it simultanteously. You will need appropriate error handling code in all applications to ensure it is handled properly.

John