Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error counting number of users logged into the same database

Posted on 2011-03-03
8
Medium Priority
?
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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
 

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 85
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

618 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