nomatterwherewego
New member
- Local time
- Today, 08:27
- Joined
- Aug 10, 2021
- Messages
- 4
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:
(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!
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:
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!