Solved

Whos connected to MS Access?

Posted on 2000-02-28
5
313 Views
Last Modified: 2011-04-14
Hi experts.

I am using MS Access as a network database.
is MS Access resides on a server and clients would point to the server's mdb file to use as their database file.

If at any time I would like to see whos connected to the database, how would I do that? (Programmatically ..)

Thanks experts :)

Kaiser
0
Comment
Question by:kaiser18au
5 Comments
 
LVL 1

Accepted Solution

by:
jelliott720 earned 100 total points
ID: 2568377
Microsoft has a set of utilities for downloading that can help. These utilities are contained in the file jetutils.exe. The Msldbusr.exe example contained therein is what your looking for... just follow the link...
 
http://download.microsoft.com/download/access97/utility1/1/WIN98/EN-US/JETUTILS.EXE


Hope this helps...

0
 
LVL 4

Expert Comment

by:gcs001
ID: 2568477
You should perhaps look at setting up Access security.

This would enable you to log access to the Access database you require.
You could then read the Access log programmatically to determine which users are connected.

Let me know if you need some guidelines in setting up Access security.

Regards,
Grant.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2568830
0
 

Author Comment

by:kaiser18au
ID: 2575253
Guys

I wonderering if anyone could help in with this.

I found this article, which allows me to show up the users connected to the DB, but it is implemented as a  function in the MS Access module.

I need to call this from VB to show up connections in my app. Is there any possibility to return the recordset from this function to my app.

Thanks

Kaiser





PSS ID Number: Q198756
Article last modified on 05-26-1999
 
WINDOWS:2000
 
WINDOWS
 

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access 2000
-------------------------------------------------------------------------------
 
SUMMARY
=======
 
Advanced: Requires expert coding, interoperability, and multiuser skills.
 
Using Microsoft Visual Basic for Applications in Microsoft Access, you can
prevent users from logging on to a Jet database. If users are already in the
database, they will remain logged on; however, no other users will be able to
open the database. This is called a passive shutdown.
 
The code example in this article demonstrates how to perform a passive shutdown
and as well how to output a list of users who are already logged on to the
database.
 
MORE INFORMATION
================
 
The Connection Control
----------------------
 
The connection control (also known as passive shutdown) feature prevents users
from connecting to a database. This capability is useful for a database
administrator who needs to acquire exclusive access to a database to perform
maintenance, for example, compacting the database, or who needs to make updates
to the database schema or applications.
 
When connection control is invoked, users currently connected to a database will
remain unaffected until the disconnect. At that point, they are unable to
reconnect until connection control is revoked.
 
The following scenarios provide additional insight into how this capability
works:
 
 - Five users are in the database. User five initiates passive shutdown. User
   six tries to connect to the database, but is denied access and an error
   message is returned stating that user five is preventing the database from
   being opened.
 
 - Five users are in the database. User five initiates passive shutdown. User
   one closes the database and tries to reconnect to the database, but is denied
   access and an error message is returned stating that user five is preventing
   the database from being opened.
 
 - Five users are in the database. User five initiates passive shutdown. User
   five closes the database. User six tries to open the database and is
   successful. This is because passive shutdown only persists while the user
   that called it remains connected to the database.
 
 - Five users are in the database. User five initiates passive shutdown. Users 1
   through four exit the database. User five calls the user list functionality
   and determines that no other users are in the database. User five closes the
   database and immediately compacts the database.
 
The User List
-------------
 
The user list feature provides a way of determining who is currently connected to
a Microsoft Jet database. The list can be obtained via the ADO programming
interface and returns the following information for each user:
 
 - Name of the computer being used.
 
 - Security name, that is, the user ID.
 
 - Whether or not the user is currently connected to the database (A user's ID
   remains in the lock database until the last user disconnects or until the
   slot is reclaimed for a new user connection.).
 
 - Whether or not the user connection was terminated normally.
 
The user list capability can be used in conjunction with the connection control
capability to determine which users are still connected, so that they can be
asked to disconnect.
 
The user list capability is also useful in isolating problems with database
corruption that is associated with the activities of a specific user.
 
Code Example
------------
 
Microsoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied
warranties of merchantability and/or fitness for a particular purpose. This
article assumes that you are familiar with the programming language being
demonstrated and the tools used to create and debug procedures. Microsoft
support professionals can help explain the functionality of a particular
procedure, but they will not modify these examples to provide added
functionality or construct procedures to meet your specific needs. If you have
limited programming experience, you may want to contact a Microsoft Certified
Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200.
For more information about Microsoft Certified Solution Providers, please see
the following page on the World Wide Web:
 
   http://www.microsoft.com/mcsp/
 
For more information about the support options available from Microsoft, please
see the following page on the World Wide Web:
 
   http://www.microsoft.com/support/supportnet/refguide/
 
To demonstrate the code example, follow these steps:
 
1. Copy the Northwind example database to the C:\ directory.
 
2. In Microsoft Access, open C:\Northwind.
 
3. Create a new module called ShutDownDB.
 
4. On the Tools menu, click References.
 
5. In the References dialog box, click to select the following reference:
 
   Microsoft ActiveX Data Objects 2.1 Library
 
6. With the above Reference selected, use the Priority button to move the
   reference above the reference to Microsoft DAO 3.6 Object Library.
 
7. Type the following code in the new module:
 
   Sub ShowUserRosterAndPassiveShutdown()
      Dim cn As New Connection
       Dim cn2 As New Connection
       Dim cn3 As New Connection
       Dim rs As New Recordset
      Dim i, j As Long
 
      On Error GoTo ErrHandler
 
      cn.Provider = "Microsoft.Jet.OLEDB.4.0"
      cn.Open "Data Source=c:\Northwind.mdb"
 
      cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=c:\Northwind.mdb"
 
      ' Restrict other users from opening the database
      cn.Properties("Jet OLEDB:Connection Control") = 1
 
      ' Attempt to open another connection to the database
      cn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=c:\Northwind.mdb"
 
      ' The user roster is exposed as a provider-specific
      ' schema rowset in the Jet 4 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 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
      Loop
 
      ' Close one of the remaining connections
      cn2.Close
 
      ' Reopen the user roster to verify that no other users are in the
      ' database Output the list of all users in the current database.
 
      Set rs = cn.OpenSchema(adSchemaProviderSpecific, , _
      "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
 
      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
      Loop
 
      cn.Close
 
      Exit Sub
 
   ErrHandler:
 
      For j = 0 To cn.Errors.Count - 1
         Debug.Print "Conn Err Num : "; cn.Errors(j).Number
         Debug.Print "Conn Err Desc: "; cn.Errors(j).Description
      Next j
 
      For j = 0 To cn2.Errors.Count - 1
         Debug.Print "Conn Err Num : "; cn2.Errors(j).Number
         Debug.Print "Conn Err Desc: "; cn2.Errors(j).Description
      Next j
 
      For j = 0 To cn3.Errors.Count - 1
         Debug.Print "Conn Err Num : "; cn3.Errors(j).Number
         Debug.Print "Conn Err Desc: "; cn3.Errors(j).Description
      Next j
 
      Resume Next
 
   End Sub
 
8. Close the Northwind database and when prompted, save changes to ShutDownDB.
   (This must be done to release an exclusive lock on the database due to the
   code you have just added.)
 
9. Re-open C:\Northwind.mdb.
 
10. Press CTRL+G to bring up the Immediate Window in the Visual Basic Editor.
 
11. Type the following in the Immediate window and press ENTER:
 
   ShowUserRosterAndPassiveShutdown
 
Note the list of database users displayed in the Immediate window.
 
Additional query words: inf
 
======================================================================
Keywords          : kbdta AccCon KbVBA
Version           : WINDOWS:2000
Platform          : WINDOWS
Issue type        : kbhowto
=============================================================================
Copyright Microsoft Corporation 1999.


 
0
 

Author Comment

by:kaiser18au
ID: 2589297
Jelliot


Many thank yous for the link. That utility had the DLL that I need to do what I wanted to do.

Thanks again.

kaiser



0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now