Excel VBA error as Multi-Users cannot input to open RecordSet and blocks SQL Table queried

Question

I am having an issue with multi-user "blocking" on my VBA recordset which is trying to apply an input to SQL database table, the VBA code in review is the following:

Dim rsHeader as New ADODB.Recordset

With rsHeader
     .CursorLocation = adUseClient
'Open Recordset
     .Open SQL_StoredProcedure, ADOConn, adOpenKeySet, adLockBatchOptimistic
'Add New Record
     .AddNew
'Loop through Fields
     For lngField = 0 to 59
'Ignore Fields if they are null
       If Not IsNull (vRsHeader(lngField)) And Not IsEmpty(vRsHeader(lngField)) Then
'Set field values to array values where not null
       .Fields(lngField).Value = vRsHeader(lngField)
       End If
     Next lngField
'Update Changes
     .UpdateBatch
'Close RecordSet
     .Close
End With

So users are going and running this VBA code the first user will run fine and populate the table in SQL but second user will run and it won't populate (due to locking I believe) I believe a sort of Loop needs to be applied so the multi users who can't get in (at the time multiple inputs are run) they are in a queue like loop which then runs the code when available but need support on how to build/where to apply?

I attempted to apply a Loop around the open and close of the RecordSet but to no success

Answer 1

I think this needs further troubleshooting. Maybe if it is a blocking issue, but if the first user has completed its task, that should not block the next one, unless a transaction has been left open. And there is no visible transaction in the code.

On my web site, you can find beta_lockinfo, which is a stored procedure to investigate blocking, and which also gives you the current activity in the server.

Answer 2

Hi @ZM85

You could start from SELECT * FROM master.sys.sysprocesses. sys.sysprocesses contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes.

Find out if there is a connection that the blocked field is not 0? If it is not 0, and it is not -2, -3, -4, then it is blocked by the connection where the SPID is equal to the value of this field. In general, the blocked field of the blocking source will be Null. If it is not equal to 0, it means that it is also blocked by others, and you must continue to find the connection that blocked it.

Take a look at waittime to know when this blocking occurred. If you run multiple queries and the spid waittime is blocked each time is short, it means that the single block does not last very long, and the blocking may not be very severe (of course, this will also affect the response speed).

Check dbid and find which database the blocking is on.

For more details, please refer to this doc: What is sysprocesses and what can it do for you?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

sql="insert into ...."

ADOConn.execute sql