Solved

Error counting number of users logged into the same database

Posted on 2011-03-03
8
247 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Andy Brown
  • 5
  • 3
8 Comments
 

Author Comment

by:Andy Brown
ID: 35025882
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
 
LVL 84
ID: 35026978
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
 

Author Comment

by:Andy Brown
ID: 35028098
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35033052
< 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Andy Brown
ID: 35034763
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
 
LVL 84
ID: 35035201
No. The Schema method is the most reliable method of doing this.
0
 

Author Closing Comment

by:Andy Brown
ID: 35035397
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
 

Author Comment

by:Andy Brown
ID: 35067988
Just to let you know - the late binding worked fine - thanks again.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now