Show Users Logged On

[Scientific]

Hello all,

I found some sample code that shows which users are logged into the current
database. Based on the code though, you have to open the Immediate Window in
the VB editor in order to see who is logged on. Is there a way to modify the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Douglas J. Steele]

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

 

[Scientific]

Douglas,

Thanks a million for your input on this. I will try your suggested changes
and post back with results.

-S

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate Window
in
the VB editor in order to see who is logged on. Is there a way to modify
the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Scientific]

Douglas,

I made the changes you suggested but I get an error in VB with the following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created a
multi-user database with a few accounts and logged into the TestDB database
from two different computers. Since the database is split into FE and BE,
could this cause the error I get above?

-S

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate Window
in
the VB editor in order to see who is logged on. Is there a way to modify
the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Douglas J. Steele]

You want to point to the back-end, not the front-end. (Every user should
have his/her own copy of the front-end, ideally on his/her hard drive, so
you should never have sharing in a front-end anyhow)

Do you still have the

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

line? I'd actually recommend using the syntax that was used for cn2.

Note that you don't need the two connections that are in the sample code (cn
and cn2): they're simply there so that you know there are (at least) two
connections to the same database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I made the changes you suggested but I get an error in VB with the
following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created a
multi-user database with a few accounts and logged into the TestDB
database
from two different computers. Since the database is split into FE and BE,
could this cause the error I get above?

-S

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate
Window
in
the VB editor in order to see who is logged on. Is there a way to
modify
the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Scientific]

Douglas,

Sounds good. I'm at work right now but will try the modifications when I
get home and see how it works. Thank you very much for your response too.
Will post back later.

-S

You want to point to the back-end, not the front-end. (Every user should
have his/her own copy of the front-end, ideally on his/her hard drive, so
you should never have sharing in a front-end anyhow)

Do you still have the

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

line? I'd actually recommend using the syntax that was used for cn2.

Note that you don't need the two connections that are in the sample code (cn
and cn2): they're simply there so that you know there are (at least) two
connections to the same database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I made the changes you suggested but I get an error in VB with the
following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created a
multi-user database with a few accounts and logged into the TestDB
database
from two different computers. Since the database is split into FE and BE,
could this cause the error I get above?

-S

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate
Window
in
the VB editor in order to see who is logged on. Is there a way to
modify
the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Scientific]

Douglas,

Yeaaah! It's working like a charm thanks to your suggested changes. One
thing I noticed though is I have to close the form then open it again to know
when users have logged on or off. Is there any way to modify the code so it
updates automatically when users log on/off?

-S

You want to point to the back-end, not the front-end. (Every user should
have his/her own copy of the front-end, ideally on his/her hard drive, so
you should never have sharing in a front-end anyhow)

Do you still have the

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

line? I'd actually recommend using the syntax that was used for cn2.

Note that you don't need the two connections that are in the sample code (cn
and cn2): they're simply there so that you know there are (at least) two
connections to the same database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I made the changes you suggested but I get an error in VB with the
following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created a
multi-user database with a few accounts and logged into the TestDB
database
from two different computers. Since the database is split into FE and BE,
could this cause the error I get above?

-S

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate
Window
in
the VB editor in order to see who is logged on. Is there a way to
modify
the
code below so it will open a popup window and display the current users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Douglas J. Steele]

There's no way to be notified when users log on or off. You shouldn't need
to close the form, though: you could have a button that reruns the code that
updates the list box or you could use a timer to automatically refresh the
list box for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Yeaaah! It's working like a charm thanks to your suggested changes. One
thing I noticed though is I have to close the form then open it again to
know
when users have logged on or off. Is there any way to modify the code so
it
updates automatically when users log on/off?

-S

You want to point to the back-end, not the front-end. (Every user should
have his/her own copy of the front-end, ideally on his/her hard drive, so
you should never have sharing in a front-end anyhow)

Do you still have the

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

line? I'd actually recommend using the syntax that was used for cn2.

Note that you don't need the two connections that are in the sample code
(cn
and cn2): they're simply there so that you know there are (at least) two
connections to the same database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I made the changes you suggested but I get an error in VB with the
following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created
a
multi-user database with a few accounts and logged into the TestDB
database
from two different computers. Since the database is split into FE and
BE,
could this cause the error I get above?

-S


:

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value
List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate
Window
in
the VB editor in order to see who is logged on. Is there a way to
modify
the
code below so it will open a popup window and display the current
users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub

 

[Scientific]

Douglas,

I put a button on the form when clicked does a requery on the list box. I
considered using a timer to do the requery but don't know how that would
affect performance in real world situations. At any rate, everything works
great and I have you to thank for helping me make it work. U-R-D-Man

-S

There's no way to be notified when users log on or off. You shouldn't need
to close the form, though: you could have a button that reruns the code that
updates the list box or you could use a timer to automatically refresh the
list box for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Yeaaah! It's working like a charm thanks to your suggested changes. One
thing I noticed though is I have to close the form then open it again to
know
when users have logged on or off. Is there any way to modify the code so
it
updates automatically when users log on/off?

-S

You want to point to the back-end, not the front-end. (Every user should
have his/her own copy of the front-end, ideally on his/her hard drive, so
you should never have sharing in a front-end anyhow)

Do you still have the

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

line? I'd actually recommend using the syntax that was used for cn2.

Note that you don't need the two connections that are in the sample code
(cn
and cn2): they're simply there so that you know there are (at least) two
connections to the same database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

I made the changes you suggested but I get an error in VB with the
following
line highlighted:

cn.Open "Data Source=C:\TestDB\TestDB FE.mdb"

I thought it best to test this in a multi-user environment so I created
a
multi-user database with a few accounts and logged into the TestDB
database
from two different computers. Since the database is split into FE and
BE,
could this cause the error I get above?

-S


:

You could create a form that has a 4 column list box defined.

Replace

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

with

DoCmd.OpenForm "NameOfTheForm"

While Not rs.EOF
Forms![NameOfTheForm]![NameOfTheListbox].AddItem _
rs.Fields(0) & ";" & rs.Fields(1) & ";" & _
rs.Fields(2) & ";" & rs.Fields(3)
rs.MoveNext
Wend

Make sure that the RowSourceType of the list box is set to "Value
List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello all,

I found some sample code that shows which users are logged into the
current
database. Based on the code though, you have to open the Immediate
Window
in
the VB editor in order to see who is logged on. Is there a way to
modify
the
code below so it will open a popup window and display the current
users
logged in?

Any help on this will be greatly appreciated

-S

Code sample follows:

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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"

' 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
Wend

End Sub