Set timeout for CurrentDb.Execute?

[Vita]

Hey, I am using the following code to update some tables but sometimes the tables are being used by others and it causes the program to lock up for 1-2 minutes and I am trying to find a way to set the timeout to like 10 seconds.

CurrentDb.Execute "UPDATE " & TableFound & " SET StatusCode = 'COM' WHERE " & StatusCriteria

I tried using CurrentDb.QueryTimeout but that didn't work. I saw something about SqlCommand.CommandTimeout but It was very confusing and seemed like I would need to rewrite a lot of stuff.

I use a connection string to create linked tables at the moment and then write data to those linked tables using statements like the above code.



Thanks for any advice/help.

[Edgar]

What is causing the program to lock up? Can you check if it's locked up through vba?

[Minty]

If those tables are in use on other forms and assuming you are in a multiuser environment, how are you opening the queries / tables on those other forms?
If they are only being used as lookups or are on non-editable forms try switching them to a snapshot view, that will reduce the number of locks on the records/tables.

Also make sure the other forms where you are editing content only open the records you need to edit, e.g. view lots of records on snapshots, open one record to edit.

[Vita]

What is causing the program to lock up? Can you check if it's locked up through vba?

I am not sure what you mean by checking it through vba? a separate program is locking the record so the vba query freezes access while it keeps retrying.

If those tables are in use on other forms and assuming you are in a multiuser environment, how are you opening the queries / tables on those other forms?
If they are only being used as lookups or are on non-editable forms try switching them to a snapshot view, that will reduce the number of locks on the records/tables.

Also make sure the other forms where you are editing content only open the records you need to edit, e.g. view lots of records on snapshots, open one record to edit.

I don't have another form using them. My company has an ERP software that could also be using the records. I am able to create linked tables with the odbc connection.
The problem lies in if someone is using the ERP to modify a specific record and the program tries to update the same record, which happens more than you would think.

I am looking for a way to detect if that specific record is locked rather than have access freeze up for a whole 2 minutes.

I have some error code written incase that happens but it only goes to the error section after querying/running the command for 2 minutes.

[Minty]

Hmm, have you tired using a pass through query to do the updates?
That wouldn't involve access in the process of locking a record, only the server, and it will release locks in a much more efficient manner than Access will I suspect.

There are T-SQL methods for obtaining locked record data, but by the time you have checked for it and returned that status to the Access FE, that status might have changed?

[Vita]

Hmm, have you tired using a pass through query to do the updates?
That wouldn't involve access in the process of locking a record, only the server, and it will release locks in a much more efficient manner than Access will I suspect.

There are T-SQL methods for obtaining locked record data, but by the time you have checked for it and returned that status to the Access FE, that status might have changed?

I am not familiar with pass through queries so I googled it and the learn.microsoft.com page says they are read only statements but I am running UPDATE statements so it wouldn't work.
If there was some way to lower the time the query would attempt to connect it would work perfect as it would error and tell the user it can't update the record so they have to try again later.

Is there anyway to do that?
Is there perhaps a different way of updating data that could detect locked records or shorten the query attempt time?
Maybe there is a way to detect if a record is locked before attempting to run the update command? that would also work.




I appreciate the help!
I was going to use DAO to update the records but it seemed easier and more efficient to use CurrentDb.Execute and I believe there was a different issue I encountered using DAO that I cannot recall at the moment.

[Minty]

A pass through can run anything that can run on the server, assuming your connection has permissions.
If you create a pass through with something like

UPDATE MyTable
SET MyField = 'String Data'
WHERE MyOtherField = 999

It will work.
So you can create a generic query def, and simply replace it's SQL and set returns records = False and it will perform an Action query directly on the server.

[Vita]

A pass through can run anything that can run on the server, assuming your connection has permissions.
If you create a pass through with something like

UPDATE MyTable
SET MyField = 'String Data'
WHERE MyOtherField = 999

It will work.
So you can create a generic query def, and simply replace it's SQL and set returns records = False and it will perform an Action query directly on the server.

When googling I found this as a way to write them in VBA. Gonna try this out but I was wondering if you had a different recommendation on how to write them? I use some variables to decide tables and criteria.

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Se qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=.\SQLEXPRESS;Trusted_Connection=Yes;"
qdf.SQL = "SELECT GetDate() AS qryTest"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst!qryTest
rst.Close
Set rst = Nothing Set qdf = Nothing

Source: https://stackoverflow.com/questions/...dset-in-access

[Gicu]

You almost got it, you don't need the recordset, to run it you simply use the .Execute method of the QueryDef object:

qdf.SQL = "UPDATE tblYourTable SET Field=....."
qdf.ReturnsRecords = False 'your update queries don't return records
qdf.Execute

Cheers,

[Edgar]

I am not sure what you mean by checking it through vba? a separate program is locking the record so the vba query freezes access while it keeps retrying.

I meant if you can check with your vba code whether the table is locked before executing the update query.

[Vita]

You almost got it, you don't need the recordset, to run it you simply use the .Execute method of the QueryDef object:

qdf.SQL = "UPDATE tblYourTable SET Field=....."
qdf.ReturnsRecords = False 'your update queries don't return records
qdf.Execute

Cheers,

When I use qdf.execute I get ODBC connection failed even though I am using the same string as I was to connect to linked tables.

Public Function WTUpdate()
        On Error GoTo Error_Connection
        Call SetVars
        Dim WTqdf As DAO.QueryDef, WTrst As DAO.Recordset, Number
        Dim UpdateSQL(0 To 6) As String
        UpdateSQL(0) = "UPDATE " & TableFound & " SET StatusCode = 'COM' WHERE " & StatusCriteria
        UpdateSQL(1) = "UPDATE " & TableFound & " SET StatusTime = '" & Format(Time, "hhmm") & "' WHERE " & StatusCriteria
        UpdateSQL(2) = "UPDATE " & TableFound & " SET CurrentStatusComment = 'T/T COMPLETED' WHERE " & StatusCriteria
        UpdateSQL(3) = "UPDATE " & TableFound & " SET TimeUpdated = '" & CurrentTime() & "' WHERE " & StatusCriteria
        UpdateSQL(4) = "UPDATE " & TableFound & " SET DateUpdated = '" & Date & "' WHERE " & StatusCriteria
        UpdateSQL(5) = "UPDATE " & TableFound & " SET UserUpdatedKey = '" & sUserKey & "' WHERE " & StatusCriteria
        UpdateSQL(6) = "UPDATE " & TableFound & " SET StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' WHERE " & StatusCriteria
        Set WTqdf = CurrentDb.CreateQueryDef("")
        WTqdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=server;Database=database;Network=DBNMPNTW;UID=UID;PWD=PWD;"
        
        
        
        For Each Number In UpdateSQL
            WTqdf.SQL = Number
            WTqdf.ReturnsRecords = False
            WTqdf.Execute
        Next Number
Exit Function


Error_Connection:
    Call Msgbox("Cannot mark step complete. Someone may be editing the record." & vbCrLf & "Error: " & Error & " " & Err.Number)
End Function

[Gicu]

I think this

WTqdf.SQL = Number

should be

WTqdf.SQL = UpdateSQL(Number)

And to make sure you have a valid connect string I usually "borrow" it from one of the linked tables:

 WTqdf.Connect = CurrentDb.TableDefs("tblYourLinkedTable").Connect

Cheers,

[Vita]

I think this

WTqdf.SQL = Number

should be

WTqdf.SQL = UpdateSQL(Number)

I thought this too but when I tried it I got type mismatch and in debug mode I hovered over just "Number" and it showed the update string. I guess since its a For Each it does it that way.

And to make sure you have a valid connect string I usually "borrow" it from one of the linked tables:

 WTqdf.Connect = CurrentDb.TableDefs("tblYourLinkedTable").Connect

Cheers,

I tried this with no luck. Same error
Not sure what is causing it. I checked that the sql is getting written properly by using

debug.print WTqdf.SQL

and it came out perfect (I know it works because I was using it just as currentDb.execute prior to realizing I needed to calculate for locked records).
Could it be because I have that same table setup as a linked table in access?

[Vita]

I did some more googling and I found a this https://stackoverflow.com/questions/...-through-query
Which tells you how to get more info for it.

Turns out I don't have access to run UPDATE directly using passthrough. I should be able to get that permission.
What's weird is I could do it via CurrentDb.Execute and it worked fine and would update the SQL server too.
It worked fast enough using CurrentDb, is there anyway to detect if a record is locked so I can just skip the update if its locked?

[Edgar]

is there anyway to detect if a record is locked so I can just skip the update if its locked?

Are you using MS SQL? Can you run this?

EXEC sp_lock mytable