Returning the current users of a database, JET_SCHEMA_USERROSTER?.

[Guest]

I am using a sub that I pulled from a MS website to return the current users
of a database. The "JET_SCHEMA_USERROSTER" method.

I keep getting errors that state either I don't have the necessary
permissions to use the "database path here" or another error that states "The
database has been placed in a state by user 'my user name' on machine 'my
machine name' that prevents it from being opened or locked".

My database is tied to a WIF that limits user level security. I have tried
opening in exclusive and shared. Any suggestions? Thanks in advance for your
help.

Here is the code:

Global Const JET_SCHEMA_USERROSTER = _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}"

Sub ReturnUserRoster()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=W:\PCM Interface Recycling\Database\PCM Interface
Recycling.mdb;"
Set rst = cnn.OpenSchema(adSchemaProviderSpecific _
, , JET_SCHEMA_USERROSTER)
Debug.Print rst.GetString
Set rst = Nothing
Set cnn = Nothing
End Sub

 

[Todos Menos [MSFT]]

don't use MDB for anything; the security cannot hold water

use SQL Server; and Access Data Projects

it has real tools for implementing security

Hope that helps

-Todos

 

[Tony Toews]

Todos Menos is not a Microsoft employee. This posting is by A a r o n K e m p f.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[David W. Fenton]

I am using a sub that I pulled from a MS website to return the
current users of a database. The "JET_SCHEMA_USERROSTER" method.

I keep getting errors that state either I don't have the necessary
permissions to use the "database path here" or another error that
states "The database has been placed in a state by user 'my user
name' on machine 'my machine name' that prevents it from being
opened or locked".

My database is tied to a WIF that limits user level security. I
have tried opening in exclusive and shared. Any suggestions?
Thanks in advance for your help.

I just implemented UserRoster this morning for the first time (I
don't do ADO, so sue me). I didn't have any problems. But none of my
files are secured.

Have you checked the actual file system permissions?

Can you actually open the databases that are said to be a problem?

 

[Jason W. Martin]

Have you tried looking at the .LDB file using a text editor? If all you
need is to just see the current users, this might be easier.

 

[Guest]

I understand your opinion but I'm already 4 months into development. No going
back now.

 

[Guest]

How do I check file system permissions? The database opens fine when the
"User Roster" code is not active. I got it to work when I'm polling the users
on another Access database but when I try to poll the current database itself
it gives me the error. I suspect that possibly you cannot call "User Roster"
on a current database by the current database?

 

[Guest]

I've heard that that method works. I've never worked with opening files in VB
or Access. Do you have some sample code that would allow me to open the .ldb
file and pull i into VB in a usable form? Or maybe a link to another good
resource for achieving those operations in VBA? Thanks

 

[David W. Fenton]

How do I check file system permissions?

Open up Windows Explorer, right click and open Properties and go to
the Security tab. It will tell you what level of access which users
have to the database.

The database opens fine when the
"User Roster" code is not active. I got it to work when I'm
polling the users on another Access database but when I try to
poll the current database itself it gives me the error. I suspect
that possibly you cannot call "User Roster" on a current database
by the current database?

Yes, of course you can. It sounds like you have some other problem,
but I don't have enough information to suggest what.

 

[David W. Fenton]

I've heard that that method works. I've never worked with opening
files in VB or Access. Do you have some sample code that would
allow me to open the .ldb file and pull i into VB in a usable
form? Or maybe a link to another good resource for achieving those
operations in VBA?

I have code I either got from somewhere I've forgotten, or that I
wrote mysefl (very doubtful!) that reads the LDB file. I've included
it after my signature.

I can't vouch for its reliability. I had it in one of my old
administrative databases, but hadn't used it for about 7 years. I
revived the admin db for another client, but used UserRoster instead
(which worked just fine, though the code was much messier than it
should have been, because of the way it's implemented, i.e., in a
very stupid and user-unfriendly manner; I personally prefer the
msldbusr.dll approach, but it was for Jet 3.5, and not all of its
functions work right with Jet 4, though the basics still work).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Option Compare Database
Option Explicit

Type UserRec
bMach(1 To 32) As String * 1 ' 1st 32 bytes hold machine name
bUser(1 To 32) As String * 1 ' 2nd 32 bytes hold user name
End Type

Function WhosOn(strDatabase As String) As String
'--------------------------------------------------------------------
' Subject : WhosOn()
' Purpose : Will read *.LDB file and read who's
' currently logged on.
' LDB files have a 32 byte starter & trailer
' Log-in names start at 33rd byte and then
' every 64th thereafter.
' Parameters : strDatabase = database to check for LDB
' Output : DatabaseName|AccessUserName;WorkstationName
'--------------------------------------------------------------------
On Error GoTo Err_WhosOn

Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim x As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ' Defined in General

strDatabase = Left(strDatabase, InStr(1, strDatabase, ".")) +
"LDB"
' Test for valid file, else Error
x = Dir(strDatabase)
iStart = 1
iLDBFile = FreeFile
Open strDatabase For Binary Access Read Shared As iLDBFile
iLOF = LOF(iLDBFile)
Do While Not EOF(iLDBFile)
Get iLDBFile, , rUser
i = 1
sMach = vbNullString
While rUser.bMach(i) <> Chr$(32) _
And rUser.bMach(i) <> Chr(0)
sMach = sMach & rUser.bMach(i)
i = i + 1
Wend
i = 1
sUser = vbNullString
While rUser.bUser(i) <> Chr$(32) _
And rUser.bUser(i) <> Chr(0)
sUser = sUser & rUser.bUser(i)
i = i + 1
Wend
sLogStr = sUser & "," & sMach
If InStr(sLogins, sLogStr) = 0 Then
sLogins = sLogins & sLogStr & ";"
End If
iStart = iStart + 64 'increment to next record offset
If iLOF <= (iStart + 64) Then Exit Do
Loop
Close iLDBFile
If sLogins = ",;" Then
WhosOn = "Note: No users are logged on at present to " _
& strDatabase
Else
WhosOn = strDatabase & "," & strDatabase & "|" _
& Left(sLogins, Len(sLogins) - 1)
End If

Exit_WhosOn:
Exit Function

Err_WhosOn:
If Err = 68 Then
MsgBox "Couldn't populate the list", 48, "No LDB File"
Else
MsgBox "Error: " & Err & vbCrLf & Error$
Close iLDBFile
End If
Resume Exit_WhosOn
End Function

 

[Guest]

I changed the file permissions to "Full Control". I'll see if it works.

Again the only info I can give is that when I have the file set to open
exclusively it errors with "I don't have the necessary permissions to use the
W:\Database.mdb"

and the other problem is when I make the file shared it errors with: "The
database has been placed in a state by user 'my user name' on machine 'my
machine name' that prevents it from being opened or locked".

I'll post after I get a chance to try the file permission changes. Thanks
for your help.

 

[David W. Fenton]

Again the only info I can give is that when I have the file set to
open exclusively it errors with "I don't have the necessary
permissions to use the W:\Database.mdb"

That's sounds like a Jet user-level security message.

and the other problem is when I make the file shared it errors
with: "The database has been placed in a state by user 'my user
name' on machine 'my machine name' that prevents it from being
opened or locked".

That, too, sounds like a Jet user-level security message.

Are you using a custom workgroup file? Perhaps you need to examine
the ULS permissions on the database object.

 

[Todos Menos [MSFT]]

you dont do ADO?

ADO is faster, David.. what is wrong with you?

oh is the widdle baby programmer scared of adapting to the 1990s?

 

[Guest]

I'm now trying WhosOn. Can you give me an example of how to call the function
properly. I'm getting an error that states "cannot use object that way" What
is the object (datatype) that I need to set = to WhosOn to get the Output of
WhosOn returned to me? Sorry for the novice question but I'm still unclear
about many things in VBA.

 

[Guest]

I set the file permissions to "Full Control" and "Allow" for each other type.
I'm now getting a message stating "Could not use "; file already in use". I
have no restrictions set in the security features. I have set the file
permissions to "allow" and I have the default set to "Shared" and I'm not
opening it in exclusive mode.

Any other suggestions for getting this thing to open and give me the
User_Roster? I mean other than using WhosOn.

I'm going to try WhosOn next.

 

[Guest]

I got it open, sorry for the last reply. I do have a question though. I got a
message returned that Note: No users are logged on at present to w:\Recycling
Database.mdb". Shouldn't it find me as being logged on and return my ID and
info?

 

[Guest]

WhosOn works great. Thank you so much for your help. I worked through all of
my other issues also. Thanks again!

 

[Guest]

Thanks for the suggestion. I got a function called WhosOn from David W.
Fenton that opens the .ldb file and returns the user and computer name. I
implemented it in my application and it works great.

 

[David W. Fenton]

I'm now trying WhosOn. Can you give me an example of how to call
the function properly. I'm getting an error that states "cannot
use object that way" What is the object (datatype) that I need to
set = to WhosOn to get the Output of WhosOn returned to me? Sorry
for the novice question but I'm still unclear about many things in
VBA.

It returns a string value. Look at the documentation at the head of
it -- it tells you exactly what it returns. I formatted it that way
so that I can strip off the database name and then use the remainder
to populate a listbox.

 

[David W. Fenton]

I set the file permissions to "Full Control" and "Allow" for each
other type. I'm now getting a message stating "Could not use ";
file already in use". I have no restrictions set in the security
features. I have set the file permissions to "allow" and I have
the default set to "Shared" and I'm not opening it in exclusive
mode.

Any other suggestions for getting this thing to open and give me
the User_Roster?

Sounds like you're adjusting NTFS permissions when I said it was a
problem with Jet user-level security permissions. If you're not
using the Access UI to make the alterations to security, then you're
not working with Jet ULS.