how do I tell if a user from a different machine is accessing a particular

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

how do I tell if a user from a different machine is accessing a particular
record?

what vba code do I use?
 
There is code to tell you the users of the database ..if you want that code,
let me know.

I know of none for a specific record...

Bob
 
Dennis

This code runs in the front-end and tells you who is in the back-end:

Function WhoIsInTheDatabaseLockFile()

'http://home.bendbroadband.com/conradsystems/accessjunkie/kickoff.html
' Written by Ken Snell (January 31, 2005)

' *** OUTPUTS A LIST OF USERS IN THE DATABASE:
' *** 1. COMPUTER NAME ("COMPUTER NAME")
' *** 2. LOGON NAME ("LOGIN_NAME")
' *** 3. WHETHER USER IS STILL CONNECTED TO THE DB (USER ID
' *** REMAINS IN .LDB FILE UNTIL LAST USER EXITS OR
' *** UNTIL THE SLOT IS CLAIMED BY ANOTHER USER)
' *** ("CONNECTED")
' *** 4. WHETHER USER'S CONNECTION TERMINATED UNDER NORMAL
' *** CIRCUMSTANCES ("SUSPECT_STATE")
' *** ADAPTED FROM MICROSOFT KNOWLEDGE BASE ARTICLE 285822

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String

' Replace the string in the next step with the name of a real
' linked table in the database

Const strLinkedTableName As String = "DT_Plan Info"
'"Name_of_A_Linked_Table"
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="

On Error GoTo Err_Msg

strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb

strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))

Debug.Print "File containing the data tables: " & strNewDataSource

cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)

cn.Open

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

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

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

Debug.Print rs.Fields(0).name, "", rs.Fields(1).name, _
"", rs.Fields(2).name, rs.Fields(3).name

While Not rs.EOF

Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)

rs.MoveNext

Wend

Exit_Function:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Function

Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " &
Err.Description
Resume Exit_Function

End Function

'**************Code End*********************

Bob
 
Thanks I will try one of the next days.
Is a "real linked table" just a tabel in the active database ?

Dennis

"bob" skrev:
 
Curiously, I was asking one of our tech people this a few days ago - i.e. how
can I tell who is logged in to a database. In my case the mdb is on a network
and is not split. Any code for that?

Thanks.
 
Try to apply an exclusive lock to the record. If another
user has a read lock on the record, the exclusive lock
will fail. If the information is available, the error message
will contain the machine name.

(david)
 
That code will work on any Access 2000 or newer MDB or MDE.

You really should split your database, though, and only have the back-end on
the server, with each user having his/her own copy of the front-end,
preferably on his/her hard drive. Multiple users in the same MDB file
significantly increases the risk of corruption.
 
Try this (not my code; but my notes don't indicate where I got it)..

Function ShowUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

Set cn = CurrentProject.Connection

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
' http://support.microsoft.com/default.aspx?kbid=285822

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

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

Debug.Print rs.Fields(0).name, "", rs.Fields(1).name, _
"", rs.Fields(2).name, rs.Fields(3).name

While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend

End Function

Bob
 
Doug,

We don't have access to any hard drives. There is a secure network drive
that our employees have access to. We have about 10 people working at once.
Would creating folders with work area names then putting the a front end in
each one work? When the user is working in a particular area he/she would
use that folder and front end. The back end would be in another folder on
that same drive.

Thanks,
Linda
 
It would be better than everyone sharing the same database, but it wouldn't
reduce network traffic the way having it on the hard drive would.
 
What is the benefit of reducing network traffic from the application's point
of view? I am still not understanding how having the front end and back end
helps? Can 2 or 4 people be updating and making changes at the same time?
If 2 people for instance add a new record at the same time, how does access
decide which record to assign the autonumber for that record?

Thanks,
Linda
 
Reducing network traffic is always a laudable goal. Not only will it make
the application faster, it will allow other applications on the network to
run faster. Network traffic is like freeway traffic. The more cars on the
road, the slower it goes.

Access is a multi user capable platform. It is designed to allow multiple
users to be working with the same tables and the same data at the same time.
There are some issues you need to deal with in a multi user environment.
With 2 to 4 users, the issues will likely not be as critical as 25 users.
Most of the conflicts will take care of themselves, but there are some
techniques you need to employe. The autonumber issue is a non issue provided
you are using autonumbers correctly. The first record in gets the next
autonumber. It is never a good idea to use the value of an autonumber field
for anything other than relating parent/child records.

Having multiple copies of data and trying to keep them in sync is just about
impossible. Sooner or later, one of them will get out of step and cause
problems.
The one front end on the desktop per user all linked to a single back end is
the correct way to configure an Access application. Trust in this.

The main issue to deal with in a multi user environment is when two or more
users are working with the same data. For example, both users 1 and 2 pull
up Jane Doe's employee record. User 1 gives her a pay increase and user 2
changes here home phone number, but not the pay scale. User 1 saves the
record first, then user 2 saves the record. If you are not using good
practices, user 2 just wiped out the pay raise, but changed the phone number.
When user 2 got her copy of the record, it had the old pay scale, so when
she wrote back her update, it is different from what is in the table because
user 1 had changed it, but now user 2's old pay scale just blew it away.

Having a front and back end is beneficial to the developer as well. Let's
say you have a single database and you are working on an enhancement in your
test copy. In the meantime, the users are pounding away entering and
modifying data. Now you are ready to do your update. Rather than just
creating a new copy of the front end and distributing it, you have to kick
all the users out, copy each changed object to the production copy, retest to
make sure you didn't miss anything, go back and import the ones you missed,
retest again, then let the users back in.

When you see MVP behind a poster's name, you can be sure they know what they
are talking about. I would pay close attention and seriously consider their
recommendation.
 
Thank-you. I can see I need to probably consult with our computer
department to see what our options are.

Linda
 
Back
Top