Tracing suspended connections (1 Viewer)

[nomatterwherewego]

Hi!
I have inherited a MS access application (small ERP type system) and was asked to migrate the backend to SQL Server Express.
I did and after some changes to the frontend ('dbseechanges' for columns with identity columns, ...) it works fine and fast for the most part.

For one part of the application (order management/procurement) users are encountering 'random' extreme slowdowns and crashes.
It seems to appear after an order has been generated by a user, but I'm struggling to 1) reproduce it and 2) trace down the error as it does not always happen.

I was able to make a screenshot of the SQL activity monitor for one user during such a crash (attached). From there, I would suspect that some action is locking one of the order tables and making it impossible to execute an insert, causing a crash?

How can I trace this down further and attempt to fix the problem? Since it does not happen every time, my guess would be that when multiple users are accessing the procurement forms or generating orders simultaneously, somehow this causes tables to get locked.

Regarding the frontend design:
There's mainly an overview form consisting of two subforms showing two different kinds of orders that are in the same table.
New orders are generated with this code:

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field, rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Bestellungen", dbOpenDynaset, dbSeeChanges)
    With rst
    rst.AddNew       
    rst![Lieferanten-Matchcode] = Me.NeuerLieferant
    rst![Bestell-Nummer Extern] = Format(Date, "yy") & "-" & rst![Laufende Bestellnummer]

    rst![Projekt] = Me.ProjektNeu
    rst![Bestell-Datum] = Me.Bestelldatum
    rst![Anfrage/Bestellung] = "Bestellung"
    rst![Status] = "Offen"
    rst![Bemerkung] = Me.Bemerkung
    
    rst.Update
    
    rst.Bookmark = rst.LastModified
    Me.Laufende_Bestellnummer_Neu = rst![Laufende Bestellnummer]
    rst.Edit
    rst![Bestell-Nummer Extern] = Format(Date, "yy") & "-" & rst![Laufende Bestellnummer]
    rst.Update
    rst.Close
    Set rstSource = Nothing

    End With
    Forms![Lieferanten Bestellungen].[Lieferanten Bestellungen Liste].Requery

(As you might guess, I'm no expert in access or SQL server; I was kinda been persuaded to give this project a try lol.)

Thanks for any help!

 

[nomatterwherewego]

Sorry, some more information:
I'm using SQL Server 2017, users are using various access versions (I cannot fully control that), mostly Access Runtime 2016.

Orders ("Bestellungen") table does not have any primary key, but an identity column ("Laufende Bestellnummer").
Order have positions which are stored in another table ("Bestellungen Positionen"), this tables does have a primary key which is an identity column.

Table design seems a bit weird to me in general but what do I know.

 

[Minty]

The open recordset command is opening the whole table, if two users do that at a very similar time it could lock things up.
It would possibly be better to open it to an empty record or simply run an Append query.

To open to an empty record simply use something like

SELECT * FROM Bestellungen WHERE 1=0

 

[arnelgp]

use a Bound form and let Access control the locking.

 

[nomatterwherewego]

The open recordset command is opening the whole table, if two users do that at a very similar time it could lock things up.
It would possibly be better to open it to an empty record or simply run an Append query.

To open to an empty record simply use something like

SELECT * FROM Bestellungen WHERE 1=0

That's a good point, thanks!
So just to be sure, you mean something like:

Set rst = dbs.OpenRecordset("SELECT * FROM Bestellungen WHERE 1=0", dbOpenDynaset, dbSeeChanges)

I just tried and changed that line, seems to do the same job. I will distribute that frontend to the users and see if that solves it.

 

[nomatterwherewego]

use a Bound form and let Access control the locking.

Could you elaborate or guide me to a resource for that? I quickly googled and this seems to be an interesting approach, unfortunately I'm still very new to Access and don't know immediately how to do that.

 

[Galaxiom]

Could you elaborate or guide me to a resource for that? I quickly googled and this seems to be an interesting approach, unfortunately I'm still very new to Access and don't know immediately how to do that.

Just link the SQL Server tables and then treat them like they are Access tables in the designer. Just use the data connection wizard. Strongly encourage the creating of a file DSN when you get to that part.

Drag the tables into the designer. Access is built to work via ODBC with any back end.

This gets you going. Then you can start looking for how the power of the server can help improve performance in one place at a time.

The main thing is to avoid simultaneously drawing on resources split between the server and the Access database. Access knows about the server but the server knows nothing about the Access part. Avoid big joins between any Access local tables and the SQL server tables.

Don't use select criteria that include User Defined access functions that must be applied before the select. Try to avoid returning a lot of records with the queries.

 

[CJ_London]

you can make the form bound by a relatively simple change to your code - in the form load event you could put

Set me.recordset = dbs.OpenRecordset("SELECT * FROM Bestellungen WHERE 1=0", dbOpenDynaset, dbSeeChanges)

or

me.recordsource="SELECT * FROM Bestellungen WHERE 1=0"

you will then need to set the controlsource of each control to the relevant field and of course review what you are doing to update/insert new records since with bound forms the record will automatically be updated/inserted when you leave the record