Find users with database open, worked 2003 but not 2007 - KB285822

[Poduska]

Conversion of code from 2003 to 2007.

I have used this code from

http://support.microsoft.com/kb/198755#top

for several years to show who is actually attached to 2003 MDB, [red]IT IS PRICELESS[/red], however it does not run now since we have converted to 2007.

I made one change to the MS KM code where I use a form and combo box to provide the database path and name to the code instead of just Northwinds.mdb as in the MS example.

Here is the abbreviated code I have been using with 2003 MDB files.

Sub ShowUserRosterMultipleUsers()

Dim cn As New adodb.Connection
Dim cn2 As New adodb.Connection
Dim rs As New adodb.Recordset
Dim MDB As String
Dim i, j As Long
  

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

    MDB = [Forms]![frmDatabaseOpenUsers].[cboDatabaseName]

    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & MDB

    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

    Set cn = Nothing
    Set cn2 = Nothing
    
End Sub

The problem arose when we converted to 2007, I changed the file names passed to the code and of course I receive "Unrecognized database format" because the databases are now 2007 and are .accdb files.

So, I researched and found the latest MS version of this code at

http://support.microsoft.com/kb/285822

However this code only examines the CURRENT database.


When I ran the new code code from the above link it works fine however I cannot seem to convert my old code to work with 2007.

I did some poking around with the new code and discovered the value returned from

Set cn = CurrentProject.Connection

shows this in the immediate window and I thought

Provider=Microsoft.ACE.[red]OLEDB.12.0[/red];User ID=Admin;Data Source=T:\DDA Internal\WhosLoggedintoDatabase.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Documents and Settings\poduskd\Application Data\Microsoft\Access\System1.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\12.0\Access\Access Connectivity Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True

I saw the change from

"OLEDB.4.0  " used in 2003 example

to

"OLEDB.12.0" used in 2007 example

However when I changed 4.0 to 12.0 in my code (the sample above)
I receive an error message...

"Provider cannot be found. It may not be installed properly"

All the rest of the code I use in converted 2007 databases appears to run fine so I don't think it is a reference problem.

Does anyone have a suggestion I may try to adjust the

http://support.microsoft.com/kb/198755#top

code to run on 2007?

 

[kjv1611]

Perhaps should this line different?

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

That looks like a line that I'd imagine should be set by a variable rather than a direct reference if I had to guess.

 

[kjv1611]

Also, to verify you've got the ADODB reference right, you could look here:

http://www.connectionstrings.com/

..I think..