Current User List

[Sprinks]

Litwin, Getz, & Gunderloy, in Access 2002 Enterprise Developer's Handbook
offer a BuildUserList() procedure that lists all current users in the
database, setting an ADODB.Connection variable to the current project:

Set cnn= CurrentProject.Connection

As I do not have the CD and will have to type this sub in manually, I
wondered if anyone knew whether this would find all users connected to the
shared back-end through their local front-ends.

Thanks for any assistance.
Sprinks

 

[Douglas J. Steele]

See whether http://support.microsoft.com/kb/198755 is what you're looking
for.

(Don't worry that it says "Access 2000": it applies to any ADO connection to
the database)

 

[Sprinks]

Douglas,

Thank you for your response.

I added the sub to a global module, changing the name of the datasource
parameters in the Open method calls to the name of my back-end file.

On running the procedure from the Immediate Window, I first got a "You do
not have appropriate permissions to open...contact your SysAdmin..." error.
After consulting VBA Help, I added the optional username and password
parameters to both calls. It now returns the following error:

Run-time error ‘-2147217843 (8004e4d)’:

Cannot start your application. The workgroup information file is missing or
opened exclusively by another user.

I don't see, however, how I can pass the workgroup file name to the method.
Do you have a suggestion?

Thank you.
Sprinks

 

[Douglas J. Steele]

Instead of

Set cnn= CurrentProject.Connection

try

Set cnn= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User
ID=myUsername;Password=myPassword;"

(You can find any connection string you might need at
http://www.carlprothman.net or at http://www.connectionstrings.com.
Unfortunately, Carl's site seems to have some technical problem at the
moment.)

 

[Sprinks]

Douglas,

When I entered:

Set cn =
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=K:\BF\Data\Backend\MainBE.mdb;Jet
OLEDB:System Database=
K:\BF\Data\WrkGroup\BFSystem.mdw;UserID=ksprinkel;Password=wgpwnitg"`

I got a type mismatch error. Thinking that you may have meant for me to use
the supplied string as a parameter to the Open method, I tried:

cn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=K:\BF\Data\Backend\MainBE.mdb;Jet
OLEDB:System Database=
K:\BF\Data\WrkGroup\BFSystem.mdw;UserID=ksprinkel;Password=wgpwnitg"

This generated run-time error -2147467259 (80004005): Could not find
installable ISAM.

I read Microsoft's white paper on this error, but frankly, don't have the
knowledge to understand it. Do you have any suggestion?

Thank you.
Sprinks

 

[Douglas J. Steele]

Sorry, yes you need to use the Open event.

Looks like you're missing a space between Data and Source in "Data
Source=K:\..."

And Carl's site is back up now, and he recommends:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet

 

[Sprinks]

Douglas,

The corrected code shows me as a user, and if I open a 2nd connection using
another new ADODB.Connection variable, it shows me twice.

My entire purpose was to see who was on so I could ask them to get off while
I made a change to a table structure. Interestingly, while the .ldb file had
been on all day, when I exited after running the sub, it was missing and I
was permitted to load the back-end exclusively to make the changes. I'm
guessing that there was a suspect connection that was somehow cleared in
running the sub.

While the sub works, it doesn't perform as I'd hoped. I asked another user
to load their local front-end file which has tables linked from the back-end
file (to which the connection is made in the ShowUsers sub). The sub does
not show them as being connected.

Since it's always recommended to split a multi-user database into front-end
and back-end files, it seems hard to believe that there wouldn't be a way to
determine who is "connected" to the back-end.

I'll keep searching.
Sprinks

 

[GeoffG]

Could you put the BuildUserList() code and its associated form
"frmViewUsers" in the backend database, open the backend in Access, and run
the form there? Would that work for you?

It worked in a test I did on my non-networked PC. I opened the backend
database in Access and opened the Developer's Handbook form "frmViewUsers"
in the backend. I then opened the frontend database. The frontend's use of
the backend did not show up in "frmViewUsers" until a form was opened in the
frontend that accessed the backend.

Geoff

 

[GeoffG]

PS

Don't use "frmViewUsers" in the backend database to Disallow New Users. If
you do, you won't prevent users from opening the frontend. Anyone who then
opens the frontend may find that it freezes on their machines. It seems,
using "frmViewUsers" in the backend database is only good for revealing
who's using it.

Geoff