Error counting number of users logged into the same database

I have a function that counts the number of users logged into and Access 2003 front-end running on a server.  This code (originally supplied via an EE post) has been working for years, but this morning I went onto a site and got an Error number 13 (type mismatch) error.

I haven't changed anything on this code (that I am aware of), but am wondering why it might not work.

Any suggestions would be massively appreciated.
Public Function CountUsers() As Long

    Const USERROSTER_SCHEMA As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"  ' User roster schema GUID
    Dim rstUserRoster As ADODB.Recordset  ' User roster recordset 
    Set rstUserRoster = CurrentProject.Connection.OpenSchema(adSchemaProviderSpecific, , USERROSTER_SCHEMA)

    Do Until rstUserRoster.EOF
        CountUsers = CountUsers + 1  ' Can't use RecordCount!
        rstUserRoster.MoveNext
    Loop

end function

Open in new window

Andy BrownDeveloperAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
< I installed Win 7 SP1 over the weekend on my machine>

That could be your trouble. SP1 caused some catastrophic issues with MDAC/ADO:

http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13

Basically, Microsoft's answer is "use late binding", i.e.:


 Set rstUserRoster = CreateObject("ADODB.Recordset")
 Set rstUserRoser = CurrentProject.Connection.OpenSchema(adSchemaProviderSpecific, , USERROSTER_SCHEMA)

I"m not sure that would work either.

You can also uninstall the Service Pack, or do one of the other fixes mentioned.

0
 
Andy BrownDeveloperAuthor Commented:
Ah should have said, the error is caused when the:

Set rstUserRoster = CurrentProject.Connection.OpenSchema(adSchemaProviderSpecific, , USERROSTER_SCHEMA)

is run.  I do have some error correction on this function (I just removed it to make it easier to read).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My first thought would be a bad/corrupt installation of MDAC. You can determine this by running Component Checker (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9). This checks your installation of MDAC (of which ADO is a component) and tells you if there are issues.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Andy BrownDeveloperAuthor Commented:
Will do, the only other thing that I can think of is that I installed Win 7 SP1 over the weekend on my machine, compiled the front-end and and then took over the MDE file with me this morning.  Although I cannot see how this would have affected the code, it was the only thing that sprang to mind.
0
 
Andy BrownDeveloperAuthor Commented:
Thanks LSM,

Just before I start playing.  Are you aware of any other (potentially safer) ways to determine the number of users logged in (I'm not using an .mdw file)?

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No. The Schema method is the most reliable method of doing this.
0
 
Andy BrownDeveloperAuthor Commented:
Thanks LSM - I've updated the code, and will go on-site, on monday to give it a blast.

Thanks as always - have a great weekend.
0
 
Andy BrownDeveloperAuthor Commented:
Just to let you know - the late binding worked fine - thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.