Question Simple select recordset suddenly not updatable (AC2013)

[AOB]

Hi there,

I have an Access DB which has been in production for several years. This morning, users reported issues loading it up and running any of the functionality. It appears that recordsets which were previously used for data manipulation, are no longer updatable (??)

These recordsets abide by the rules for updatability - the first one that fails is driven by a very simple select, i.e. :

SELECT *
FROM tblTable
WHERE fldField = "someValue"

But as soon as the recordset is loaded, if I debug and check the attributes, the Updatable flag is set to False (??)

Never been an issue before - like I say, this DB has been in play for a number of years - and no recent development changes so the code is as it has been all along.

Suggesting this may be a result of a patch by Microsoft?

KB4484119 springs to mind : https://support.office.com/en-gb/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

However, I'm not getting that specific error (no mention of corruption)

Any ideas? Causing me no end of grief as a lot of people depend on my DB's!

Thanks!

AOB

 

[isladogs]

Recheck whether the query is set to unique values =yes or record locks is set to all records

 

[AOB]

Thanks Colin,

It's not a saved query, it's generated at run-time - but very vanilla and has ever been an issue before :

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb
With dbs
    strSQL = "SELECT [tblTable].* " & _
                "FROM [tblTable] " & _
                "WHERE [tblTable].[fldField] = " & Chr(34) & strSomeValue & Chr(34)

    Set rst = .OpenRecordset(strSQL)
    With rst
        If Not (.BOF And .EOF) Then
            ....
        End If
    End With
End With

After the recordset has been opened, rst.Updatable = False - but I can't see why that would be the case?

And this code has been in production for literally years without issue?

(dbs.Updatable = True for what it's worth)

I'm stumped, I've never seen this before?

Thanks

AOB

 

[isladogs]

I never use Chr(34) as I find it confusing.
I may be wrong but think you have missing quotes at the end as its a string value

Try

Dim dbs=DAO.Database
Dim rst=DAO.Recordset
...
strSQL = "SELECT TableName.* FROM TableName WHERE FieldName = """ & strsomevalue & """;"

'OR
'strSQL = "SELECT TableName.* FROM TableName WHERE FieldName = '" & strsomevalue & "';"

Set rst = .OpenRecordset(strSQL, dbOpenDynaset)
....

If its a linked table in SQL Server then

Set rst = .OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If none of that helps please upload something for one of us to test

 

[AOB]

Thanks again Colin

The SQL is correct (as it has always worked)

The issue seems to be the production backend - I switched in a backup and the recordsets are all updatable so there must be some corruption issue in the live version. If there is, it's not immediately obvious as there are no errors other than the runtime errors when trying to update.

So am going to restore a backup to production and see if that solves the problem. May just be a freak network thing?

Thanks for your feedback!

 

[CJ_London]

users reported issues loading it up and running any of the functionality

I presume each user has their own copy of the front end? If not, that can cause issues such as this

 

[AOB]

I presume each user has their own copy of the front end? If not, that can cause issues such as this

Hi CJ,

Correct, it's a standard split DB, everybody has their own local copy of the front end and a single production backend on the server

It seems to be the backend that's corrupt

Thanks

AOB

 

[AOB]

Just to confirm - backend was indeed the source of the problem

Restoring the last backup has rectified it

Can only guess that one of the local front ends forced a permanent record lock on one or more tables that was immutable - to be honest, I've never experienced that before, nor can I see any way that could have happened (I'm pretty careful in my design) and, like I say, this particular DB has been running since 2015 and it's never happened before

Incidentally, there was no record locking file (.laccdb) on the production backend to suggest a link was being maintained unexpectedly. So I'm fairly stumped!

Thank goodness my backup process is robust!!

 

[CJ_London]

no locking file implies the db had been opened exclusively - which means other cannot make changes. But that should be released once whoever opened it exclusively closes it again.

 

[AOB]

Indeed - I would have thought opening exclusively was a very specific process rather than anything that could happen accidentally?

A front end .accde couldn't open the backend exclusively "by accident" could it? Unless it was designed / coded to do so (it's not)

I also hide the backend (as best I can anyway) so users don't actually know where to look for it in order to try to open it manually (and exclusively)

 

[CJ_London]

it is - you need a full version of access and choose the exclusive option before opening the backend directly

or vba code you can open exclusively by setting opendatabase options parameter to true.

As I'm sure you are aware, the path to the backend is easily visible if using linked tables

 

[AOB]

As I'm sure you are aware, the path to the backend is easily visible if using linked tables

Oh, yes, 100% - assuming a) the user knows how to navigate their way around the skeleton of Access and b) they're motivated to even bother trying

I have the Navigation Pane hidden and locked so F11 won't let prying eyes into the engine room, intentionally or otherwise. Yes, one could hold the Shift button on launch to override that but to what end? Most users really just want to make use of the functionality and don't really care about the inner workings - there's no benefit to it for them.

But I take your point...