Find out who is connected to the back-end database.

  • Thread starter Thread starter Robin Guest
  • Start date Start date
R

Robin Guest

I have a small (10) group of users on a database and get
frequent (3 to 5 per week) corruptions of the back-end.

All of the users have the same front end and I (alone)
have a facility to see who is connected using the
OpenSchema (Fields 0, 2 & 3). This gives a 'Suspect State'
output, which may help with my faultfinding.

The problem is than when the back-end db crashes it will
not accept any new connections but existing connections
are serviced. The problem with my code

Private Sub Form_Open(Cancel As Integer)
Dim cnn As New ADODB.Connection
Dim rstOC As New ADODB.Recordset
Dim rstUC As ADODB.Recordset

DoCmd.RunSQL "DELETE * FROM tblUsersConnected"

Set rstUC = New ADODB.Recordset

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "Data Source=MyMDB"

Set rstOC = cnn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

rstUC.Open "SELECT * From tblUsersConnected",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

While Not rstOC.EOF
rstUC.AddNew
rstUC!fldComputerName = rstOC.Fields(0)
rstUC!fldConnectStatus = IIf(rstOC.Fields(2) =
True, "True", "False")
rstUC!fldSuspectState = IIf(IsNull(rstOC.Fields
(3)), "Null", rstOC.Fields(3))
rstOC.MoveNext
rstUC.Update
Wend

rstOC.Close
rstUC.Close
Set rstOC = Nothing
Set rstUC = Nothing
cnn.Close
Set cnn = Nothing

End Sub

... is that it makes a new connection to the back-end
database which, if the back-end has crashed obviously
fails.

Is there a way of obtaining connection information to the
back-end using the front-end connection?

The front-end uses links to the tables in the back-end.

Thanks for reading this far.
 
Robin,

To begin with, I hope "All of the users have the same front end" means each
user has his/her own copy of the same front end, not actually one common
front end opened by all users! In the latter case, this is the source of the
problem you are facing (BE corruption).

Whichever the case, I have an alternative to propose: use a login tracing
table with fields User (text), Login (date/time) and logout (date/time), and
use a startup event and a close event to record a time stamp by user logging
in and out, so you can now exactly who's in at any given moment without
needing a new connection, as long as the table is permanently connected to
your front end (plus you can retrieve various statistics if required).
In order to obtain the user name you can use Application.CurrentUser if you
have Access security in place, or you can use Environ("UserName"), which
returns the user's Windows logon name, if not.
A Startup event could be anything from an Autoexec macro to the Open event
of a startup form. A closing event could be the On Close event of a
switchboard or a main form that is always open; if you don't have one, you
could use a dummy, hidden form that opens at startup, just for the purpose.

HTH,
Nikos
 
Nikos thanks.

Each user is locally running their own instance of an
idenical front end.

My userlist and geographical spread is small enough that I
can easily find out who was (is) connected at the BS
corruption by asking them.

I was particultaly interested in the SuspectState property
of the OpenSchema metod of the connection object as this
may give me a clue as to which user's machine caused the
corruption.

Robin
 
Back
Top