Set timeout for CurrentDb.Execute?

[Minty]

Unless I'm missing something those seven queries could and should be one simple update statement:

 
    strSql = "UPDATE " & TableFound & " SET StatusCode = 'COM' "
    strSql = strSql & " , StatusTime = '" & Format(Time, "hhmm") & "' "
    strSql = strSql & " , CurrentStatusComment = 'T/T COMPLETED' "
    strSql = strSql & " , TimeUpdated = '" & CurrentTime() & "' "
    strSql = strSql & " , DateUpdated = '" & Date & "' "
    strSql = strSql & " , UserUpdatedKey = '" & sUserKey & "' "
    strSql = strSql & " , StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' WHERE " & StatusCriteria

As the table and where clause are the same for each statement.

[Vita]

Are you using MS SQL? Can you run this?

EXEC sp_lock mytable

I tried this and got an error so I looked up the syntax for sp_lock and it seems that it needs a session ID instead of a tablename. I couldn't seem to find anything on how to get the session ID of the table. It also wouldn't just check the single record I am attempting to update, it seems like it would check the entire table which would just throw false positives when someone's editing a different record.

Unless I'm missing something those seven queries could and should be one simple update statement:

    strSql = "UPDATE " & TableFound & " SET StatusCode = 'COM' "
    strSql = strSql & " , StatusTime = '" & Format(Time, "hhmm") & "' "
    strSql = strSql & " , CurrentStatusComment = 'T/T COMPLETED' "
    strSql = strSql & " , TimeUpdated = '" & CurrentTime() & "' "
    strSql = strSql & " , DateUpdated = '" & Date & "' "
    strSql = strSql & " , UserUpdatedKey = '" & sUserKey & "' "
    strSql = strSql & " , StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' WHERE " & StatusCriteria

As the table and where clause are the same for each statement.

They could be haha. I was thinking the same thing recently after thinking about it but my priority is getting rid of this bug first and then cleanup.

[Minty]

I suspect your problem will go away if you run it as a pass-through. SQL server is really good at handling locks and multiple access.
You may also find that changing your login's permissions might make things better as well.

I haven't found any way of finding if a specific record is locked either.
The locking could be down to poor design in the source database.

[Vita]

I suspect your problem will go away if you run it as a pass-through. SQL server is really good at handling locks and multiple access.
You may also find that changing your login's permissions might make things better as well.

I haven't found any way of finding if a specific record is locked either.
The locking could be down to poor design in the source database.

I actually found a method that works a lot quicker to check if its locked.

Public Function WorkticketUpdate()        On Error GoTo Error_Connection
        Call SetVars
        
        Dim WTqdf As DAO.QueryDef, WTrst As DAO.Recordset, WTdb As DAO.Database, UpdateSQL As String, Number
        
        'Checks to see if the record is locked
        Set WTdb = CurrentDb
        Set WTqdf = WTdb.CreateQueryDef("", "SELECT * FROM " & Replace(TableFound, "_", ".", , 1) & " WHERE " & StatusCriteria & ";") 'Replace is needed because when access saves linked tables it replaces "." with "_"
        WTqdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=server;Database=database;Network=DBNMPNTW;UID=UID;PWD=PWD;"
        Set WTrst = WTqdf.OpenRecordset
        Set WTqdf.ReturnsRecords = False
        WTrst.Edit
        
        'Shortens the SQL statement
        UpdateSQL = "UPDATE " & Replace(TableFound, "_", ".", , 1) & " SET StatusCode = 'COM', " & _
                                                                            "StatusTime = '" & Format(Time, "hhmm") & "', " & _
                                                                            "CurrentStatusComment = 'T/T COMPLETED', " & _
                                                                            "TimeUpdated = '" & CurrentTime() & "', " & _
                                                                            "DateUpdated = '" & Date & "', " & _
                                                                            "UserUpdatedKey = '" & sUserKey & "', " & _
                                                                            "StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' " & _
                                                                            "WHERE " & StatusCriteria
        'Sets the sql string for the actual update and executes it
        WTqdf.SQL = UpdateSQL
        WTqdf.Execute

Exit Function


Error_Connection:
    Dim dbeError As Error
    For Each dbeError In DBEngine.Errors
        Debug.Print "(" & dbeError.Number & "): " & dbeError.Description
    Next
    Call Msgbox("Cannot mark step complete. Someone may be editing the DHR" & vbCrLf & "Error: " & Error & " " & Err.Number)
End Function

If the record is locked then WTrst.edit will throw an error reading "(3027): Cannot update. Database or object is read-only."

Originally I was using WTrst.findfirst when I tried this method because that is what was recommended in the answers I found on google for locked records but that was slow so then I searched for faster ways to do the findfirst function and queries were recommended. Now it works instantaneously.

I do have that huge ugly block of empty space by the UpdateSQL variable. Any tips on how I could clean that up? (Also tips on how to clean the rest up would be cool too!)

edit: or atleast it seems to work. I need to do a bit of testing after I get updated permissions. I keep getting the read only. I need to get updated permissions and then check.

[Minty]

To tidy it up use the code I supplied, it's pretty much identical, it just uses strSQL as the variable for the statement.
It's my preferred method of concatenating long strings, there is a limit to using the continuation lines ( & _ ) of about 15 lines so you never hit that.

If you use something like Notepad ++ you can press shift and alt to block select text and insert / delete blocks of text in a really efficient way. SSMS has the functionality.
Once you get used to it you'll never look back, and get really annoyed that you can't do the same in the VBA editor.

[Vita]

To tidy it up use the code I supplied, it's pretty much identical, it just uses strSQL as the variable for the statement.
It's my preferred method of concatenating long strings, there is a limit to using the continuation lines ( & _ ) of about 15 lines so you never hit that.

If you use something like Notepad ++ you can press shift and alt to block select text and insert / delete blocks of text in a really efficient way. SSMS has the functionality.
Once you get used to it you'll never look back, and get really annoyed that you can't do the same in the VBA editor.

So before I go asking for updated permissions, I just wanna verify this code works. I can update the SQL servers tables through the use of linked tables in access but when attempting to use a passthrough query (on both my read and RW account) I just get the error saying that the database or object is read only. Do you happen to know if writing through linked tables vs passthrough are different permissions on SSMS?

[Minty]

So before I go asking for updated permissions, I just wanna verify this code works. I can update the SQL servers tables through the use of linked tables in access but when attempting to use a passthrough query (on both my read and RW account) I just get the error saying that the database or object is read only. Do you happen to know if writing through linked tables vs passthrough are different permissions on SSMS?

In theory RW permissions would be sufficient for an SELECT or an UPDATE statement to be executed.
I assume you are using a generic User account in your connection string, If you connect to the same server in SSMS with those same credentials can you execute the query from the pass-through?

If you can but you can't from access as a pass-through that would be strange.

[Vita]

In theory RW permissions would be sufficient for an SELECT or an UPDATE statement to be executed.
I assume you are using a generic User account in your connection string, If you connect to the same server in SSMS with those same credentials can you execute the query from the pass-through?

If you can but you can't from access as a pass-through that would be strange.

So I got the permissions updated but the line WTrst.Edit still gives me an error of read-only. Even in the test database that isn't being used anywhere else.
Any ideas?

[Gicu]

Usually with DAO recordsets based on SQL Server tables you need to use the option dbSeeChanges, so your line should be:

Set WTrst = WTqdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Cheers,

[Vita]

Usually with DAO recordsets based on SQL Server tables you need to use the option dbSeeChanges, so your line should be:

Set WTrst = WTqdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Cheers,

I tried this and I get the same error.
(3027): Cannot update. Database or object is read-only.

I also tried dbOpenSnapshot and got this:
(3251): Operation is not supported for this type of object.

[Vita]

I believe its because of the fact that the query is a passthrough query. Though now I feel like I've gone in a circle as I'm back to trying to figure out how to read if the record is locked or not. (or set time/retry amount for the odbc command.)

[Gicu]

You said you're getting the error on the WTrst.Edit line so you didn't get to the pass-through, I thing setting the ReturnRecords=False might have something to do with it. Can you try this updated code:

Public Function WorkticketUpdate()        \
On Error GoTo Error_Connection
Call SetVars
        
Dim WTqdf As DAO.QueryDef, WTrst As DAO.Recordset, WTdb As DAO.Database, UpdateSQL As String, Number


'Checks to see if the record is locked
Set WTdb = CurrentDb


Set WTrst = WTdb.OpenRecordset("SELECT * FROM " & Replace(TableFound, "_", ".", , 1) & " WHERE " & StatusCriteria & ";",dbOpenDynaset,dbSeeChanges) 'Replace is needed because when access saves linked tables it replaces "." with "_"


WTrst.Edit 'if locked should go to error handler




Set WTqdf= WTdb.CreateQueryDef("")


WTqdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=server;Database=database;Network=DBNMPNTW;UID=UID;PWD=PWD;"
WTqdf.ReturnsRecords = False


'Shortens the SQL statement
UpdateSQL = "UPDATE " & Replace(TableFound, "_", ".", , 1) & " SET StatusCode = 'COM', " & _
		"StatusTime = '" & Format(Time, "hhmm") & "', " & _
		"CurrentStatusComment = 'T/T COMPLETED', " & _
		"TimeUpdated = '" & CurrentTime() & "', " & _
		"DateUpdated = '" & Date & "', " & _
		"UserUpdatedKey = '" & sUserKey & "', " & _
		"StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' " & _
		"WHERE " & StatusCriteria


'Sets the sql string for the actual update and executes it
WTqdf.SQL = UpdateSQL
WTqdf.Execute 


Exit Function




Error_Connection:
    Dim dbeError As Error
    For Each dbeError In DBEngine.Errors
        Debug.Print "(" & dbeError.Number & "): " & dbeError.Description
    Next
    Call Msgbox("Cannot mark step complete. Someone may be editing the DHR" & vbCrLf & "Error: " & Error & " " & Err.Number)
End Function

Cheers,

[Gicu]

Have you tried Minty's suggestion to log in the SSMS with your updated credentials and see if you can run the pass-through in there (debug.Print the UpdateSQL variable and pasted in the query window in SSMS)?

Cheers,

[Vita]

You said you're getting the error on the WTrst.Edit line so you didn't get to the pass-through, I thing setting the ReturnRecords=False might have something to do with it. Can you try this updated code:

Public Function WorkticketUpdate()        \
On Error GoTo Error_Connection
Call SetVars
        
Dim WTqdf As DAO.QueryDef, WTrst As DAO.Recordset, WTdb As DAO.Database, UpdateSQL As String, Number


'Checks to see if the record is locked
Set WTdb = CurrentDb


Set WTrst = WTdb.OpenRecordset("SELECT * FROM " & Replace(TableFound, "_", ".", , 1) & " WHERE " & StatusCriteria & ";",dbOpenDynaset,dbSeeChanges) 'Replace is needed because when access saves linked tables it replaces "." with "_"


WTrst.Edit 'if locked should go to error handler




Set WTqdf= WTdb.CreateQueryDef("")


WTqdf.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=server;Database=database;Network=DBNMPNTW;UID=UID;PWD=PWD;"
WTqdf.ReturnsRecords = False


'Shortens the SQL statement
UpdateSQL = "UPDATE " & Replace(TableFound, "_", ".", , 1) & " SET StatusCode = 'COM', " & _
        "StatusTime = '" & Format(Time, "hhmm") & "', " & _
        "CurrentStatusComment = 'T/T COMPLETED', " & _
        "TimeUpdated = '" & CurrentTime() & "', " & _
        "DateUpdated = '" & Date & "', " & _
        "UserUpdatedKey = '" & sUserKey & "', " & _
        "StatusDate = '" & Format(Date, "yyyy-mm-dd") & "' " & _
        "WHERE " & StatusCriteria


'Sets the sql string for the actual update and executes it
WTqdf.SQL = UpdateSQL
WTqdf.Execute 


Exit Function




Error_Connection:
    Dim dbeError As Error
    For Each dbeError In DBEngine.Errors
        Debug.Print "(" & dbeError.Number & "): " & dbeError.Description
    Next
    Call Msgbox("Cannot mark step complete. Someone may be editing the DHR" & vbCrLf & "Error: " & Error & " " & Err.Number)
End Function

Cheers,

This doesn't seem to work. While it doesn't give me an error, it also doesn't detect when the record is locked by the ERP/third party software. It jumps over to the update and then freezes access up for about 2 minutes then gives me this:

(0): [Microsoft][SQL Server Native Client 11.0]Query timeout expired(3146): ODBC--call failed.

Have you tried Minty's suggestion to log in the SSMS with your updated credentials and see if you can run the pass-through in there (debug.Print the UpdateSQL variable and pasted in the query window in SSMS)?

Cheers,

I have not. I plan to but I don't have direct access to the SQL server so I am trying to schedule some time with one of the 2 people that do.

[Minty]

You don't need direct access to the server, just an ability to connect to it?
You can run SSMS from your local pc as long as you can connect to the SQL server.

There's no point running the query on an Admins login, if they can't run it nobody could