MS Access freezes when saving data to MS SQL Server (sometimes)

Question

Since last week tuesday (18-9-2018), Access sometimes freezes when saving data to the MS SQL back-end database.

It mostly occurs on moments were multiple people are using the application. This has never given any problems untill last week. So thats a bit strange.

I use the code below for saving:

Sub Realisatie_Opslaan()

Dim productielijnMW As Integer
Dim PloegNr As Integer
Dim Datum As String

Formnaam = Screen.ActiveForm.Name
productielijnMW = DLookup("productielijn", "TblAlgemeen", "[GebruikerNr] = 1")
PloegNr = Forms(Formnaam)!txtPloegNr
Datum = Forms(Formnaam)!txtDatum

'Productie van goede blokken'
Set db = CurrentDb
Set rst = db.OpenRecordset("Select Productielijn, Lijngedeelte, Type, Dienst, Datum, Aantal from TblProductie where productielijn = " & productielijnMW & " and format(Datum, 'dd-mm-yyyy') = '" & Datum & "' and dienst = " & PloegNr & "", dbOpenDynaset)

    'Realisatie_1_1
    If Forms(Formnaam)!txtReal_1_1.Enabled = True Then
        If Forms(Formnaam)!txtReal_1_1 <> "" Then
            rst.AddNew
            rst!Productielijn = productielijnMW
            rst!Lijngedeelte = Forms(Formnaam)!txt_lbl_1
            rst!Type = Forms(Formnaam)!txt_lbl_1_1
            rst!Dienst = PloegNr
            rst!Datum = Datum
            rst!Aantal = Forms(Formnaam)!txtReal_1_1
            rst.Update
        End If
    End If
End sub

Because it happens randomly i'm not able to find a clear answer on the problem. Is it possible that it happens when the db object and the recordset are not closes as following:

set rst = nothing
set db = nothing

I can't say this fixes it because of the randomness of the problem appearing.

EDIT: I've waited a while to see if I could get an error. The following error appeared:

"ODBC – delete on a linked table failed"

ElseIf rst.RecordCount > 0 Then
    'Verwijder de huidige productie getallen
    Do While Not rst.EOF
        rst.Delete
        rst.MoveNext
    Loop

the error is on the rst.delete statement. Don't know why this won't work suddenly

Thanks in advance,

Are these linked tables? I suggest you run something like `sp_whoisactive` in the SQL Server database and see what is going on — Nick.Mc, Sep 25 '18 at 09:16
@Nick.McDermaid Yes these are linked tabled. I don't know how to run the sp_whoisactive procedure though — Dennis vd Eijnde, Sep 25 '18 at 09:17
@nicomp nothing changed last tuesday. The problem just became way more frequent since then. — Dennis vd Eijnde, Sep 25 '18 at 09:18
"I don't know how to run the sp_whoisactive procedure ". Run `exec sp_whoisactive` either in a SQL Server client (e.g. SSMS) or you may even be able to execute it via Access — ADyson, Sep 25 '18 at 09:24
@ADyson the problem is that the procedure has to be installed first and I dont have permission for that — Dennis vd Eijnde, Sep 25 '18 at 09:26
In your question you say it started last Tuesday. In your comment you say it didn't start last Tuesday. — nicomp, Sep 25 '18 at 09:30
You don't need to install sp_whoisactive to run it. You can just download it, copy-paste the code in a passthrough query, modify it a tiny bit to just execute the code instead of creating a stored procedure, and then run it. — Erik A, Sep 25 '18 at 09:33
@ErikvonAsmuth tnx, I did that now but I don't know what to look for — Dennis vd Eijnde, Sep 25 '18 at 09:37

Erik A · Accepted Answer · 2018-09-25T09:51:18.880

3

Your code uses a filtered recordset to only add rows to a table. This is a major bad practice, and adds unnecessary load to the server, and risks causing locking conflicts.

There are numerous other ways to do this, for example, directly open a table-type recordset (much smaller risk of locking conflicts):

Set rst = db.TableDefs!TblProductie.OpenRecordset(dbOpenTable)

Alternately, don't use recordsets at all, but use an insert query:

Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.CreateQueryDef("", "INSERT INTO TblProductie (Productielijn, Lijngedeelte, [Type], Dienst, Datum, Aantal) SELECT p1, p2, p3, p4, p5, p6")
    'Realisatie_1_1
    If Forms(Formnaam)!txtReal_1_1.Enabled = True Then
        If Forms(Formnaam)!txtReal_1_1 <> "" Then
            qd.Parameters!p1 = productielijnMW
            qd.Parameters!p2 = Forms(Formnaam)!txt_lbl_1
            qd.Parameters!p3 = Forms(Formnaam)!txt_lbl_1_1
            qd.Parameters!p4 = PloegNr
            qd.Parameters!p5 = Datum
            qd.Parameters!p6 = Forms(Formnaam)!txtReal_1_1
            qd.Execute
        End If
    End If
End sub

By not using a recordset, you don't have to have a lock on the table for any duration, you just insert a record.

Further optimization would be to execute this query directly on SQL server, instead of executing it on a linked table and letting Access have it's way with the SQL first.

edited Sep 25 '18 at 09:51

answered Sep 25 '18 at 09:45

Erik A

31,639
12
42
67

Tnx for the tips Erik. I agree on them all. Unfortunately letting SQL server do everything is too difficult for me in this instance. I know how to do it with single inputs, but not with the 100+ records i'm trying to add at once. As I said in my edited question, the problem occurs on the delete statement not on the insert. – Dennis vd Eijnde Sep 25 '18 at 09:55
That code is incomplete. Especially if you're inserting many records, not having the overhead and additional instability of linked tables is important. If you want to delete records before inserting, use a `DELETE` query. – Erik A Sep 25 '18 at 09:57
Could you please share the specific syntax for me? I don't get it to work and the problem is that I need a quick fix because people are relying on the application – Dennis vd Eijnde Sep 25 '18 at 10:04
1

What do you mean? https://msdn.microsoft.com/en-us/library/office/ff195097.aspx is Access delete query syntax. I can't tell what you're trying to do from an incomplete code snippet. – Erik A Sep 25 '18 at 10:06
Nice answer, I never realized using paramaters was so simple in that case. – iDevlop Sep 25 '18 at 10:08
The problem is still not solved. I still get the error ODBC call on linked table failed
– Dennis vd Eijnde Sep 25 '18 at 11:36
Where? If you've followed all my advice you're only executing queries, and not doing anything with recordsets, right? Next step is to turn the queries into passthrough queries – Erik A Sep 25 '18 at 11:40
@ErikvonAsmuth on the ` db.Execute "DELETE * FROM TblProductie where productielijn =....`statement. Weird thing is it still works 9/10 times but still Access freezes sometimes – Dennis vd Eijnde Sep 25 '18 at 11:44
1

Gah! Use parameters, like I've shown in the sample I've shared...That delete query will fail if anyone's got a lock on any of the rows you're about to delete. Likely someone is viewing or editing a record you're about to delete. You'd probably have to rework your application to make sure these kind of locks don't happen. Make sure all forms viewing this table have their recordset type set to snapshot, and record locks to no locks, and no-one has direct access to the table – Erik A Sep 25 '18 at 11:50
I am using parameters. I'l look if the snapshot thing works. – Dennis vd Eijnde Sep 25 '18 at 11:53
@ErikvonAsmuth I'll say your answer helped me out. I guess the new code with the parameters and the query's don't lock the dataset anymore. I think the error I just descriped came from people using the old version without the updated code. Tnx! – Dennis vd Eijnde Sep 25 '18 at 12:22

MS Access freezes when saving data to MS SQL Server (sometimes)

1 Answers1