cn.OpenSchema SUSPECT_STATE (1 Viewer)

[BiigJiim]

Hi use the following to return a recordset of users connected to an Access 2010 database. All works fine. My question is: it returns a field called SUSPECT_STATE which I ignore. Can anyone tell me what this field represents and in what conditions it is set?

Many thanks,
Jim

Here's the code:

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset

Const conDatabase As String = <full database path>

' Open connection to Access backend
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & conDatabase & ";Persist Security Info=False;"

' Open recordset
Set rs = cn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

 

[NauticalGent]

your code is incomplete: <full database path> should be replaced with the actual path to your DB.

Is there a reason you are using ADOdb instead of DAO? Are you linking to an external source like SQL?

 

[cheekybuddha]

IIRC, Suspect State is used for SQLServer connections - but I could well be wrong.

 

[HiTechCoach]

When an Access application connected to a database does something to cause JET/ACE to flag the database for a need to be required, the Suspect State can often be set to True for the possible offender.

If the Suspect State is every set to true, I have often have found there is a reason for concern.

 

[The_Doc_Man]

Regarding "suspect state" I found some references.

This reference suggests that "SUSPECT STATE" is the indicator that database recovery is needed. (See "4. Multi-user Access")

Access/VBA Tutorials - Access Database Corruption Repair Guide

Microsoft Access Database Corruption Self-Repair Guide. Learn how to detect corruption, how to deal with corruption and how to prevent it!

www.everythingaccess.com

More than one reference implies (but never actually confirms) that "SUSPECT_STATE" is a DB flag that is set before Access starts any DB udpate of the BE file and clears that flag when the write-back is complete. The main implication is that if Access does not get back to the DB to reset the flag, then it knows the DB write-back was not completed, and that is one of the sure-fire causes of corruption.

https://support.microsoft.com/en-us/help/283849/how-to-troubleshoot-and-to-repair-a-damaged-access-2002-or-later-datab

There are articles for SQL Server as a Back End that also mention SUSPECT states:

Recover Database From Emergency Mode in SQL Server - IT Tutorial

Recover Database From Emergency Mode in SQL Server -

ittutorial.org

Some of the references only refer to JET (which makes them pretty old by now.) Others refer to ACE and the last of my links is a 2019 reference to SQL Server, so that is not very old at all.

 

[isladogs]

The code to check the JET roster of database users dates back to Access 97 at least and so is now fairly ancient technology.
If the SUSPECT STATE boolean field is true, this indicates that the monitored database may be corrupt.

See Logged in users for more information