Debugging VBA Classes and a List with one Macro (UserRoster) always pops up. Why?

[powerski]

I am trying to convert an Access 2003 application to Access 2013 and SQL Server. In the process of trying to understand and modify the code I am using the VBA debugger to step into the first form that gets loaded. However as soon as I press run there appears a popup box listing a single Macro "UserRoster". When I select Run in the popup box it shows the following in Immediate "PC1 Admin -1" At this point I cannot go any further in the debugging process - it just ends? Why does this get invoked? Here is what the code looks like:

 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                     UtilityFunctions                         '
'                                                              '
'       This module contains useful functions that you         '
'       can use in expressions on your forms and reports.      '
'                                                              '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit           ' Require variables to be declared before being used.
Option Compare Database   ' Use database order for string comparisons.
Public Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"

and again

'Sub UserRoster()
Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    'cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=y:\TPA Pro\TPAPRodata.mdb"
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TPA Pro\Working\TPAPRodata.mdb"
    cnn.Open
    
    Set rst = cnn.OpenSchema(adSchemaProviderSpecific, _
        , JET_SCHEMA_USERROSTER)
    Debug.Print rst.GetString
End Sub

Any ideas on how to get past this?

[June7]

I don't use macros, only VBA.

All the sub appears to do is open a recordset but then doesn't do anything with the recordset except Debug.Print a single field value. The datasource is an access mdb file. Does that file exist?

It appears a macro is calling the sub UserRoster(). Why? Where is the macro invoked? Is this form set to open by default when the db opens? Is the macro called in the form Load or Current or Open event? Is there an AutoExec macro that calls the sub?

[Dal Jeanis]

Okay, first, I survived Access migration 2003 to 2010, and I have the scars to prove it. I would never suggest adding an unnecessary change at the same time. I know it sounds like a good idea, but you can't size that marginal effort (SQL Server) until after you've already done the rest of the analysis. Maybe it's easy, and maybe it's adding double the complexity.

Second, you should be using a junk copy of the database for your testing. I hope you are doing that automatically. I would suggest not just using a different directory, as I see you are doing, but giving it a date-plus-incremented-alpha suffix like V2014-11-26A every time you do anything to it.

Save early and save often. When you get to coding and testing, use a separate backup every single time you think the thing is working and tested. I took backups five times day and needed them.

My process was to take a fresh (junk) copy of the production db, make the required changes and test fully. Any random flailing can occur here. After I believed it was working, and I knew exactly what was needed, I took ANOTHER fresh copy of production, ported in ONLY the changes that I thought were necessary, and tested the snot out of it again. This was my process, and it saved me from accidentally inserting test code into production, and also from accidentally implementing erroneous code. Word to the wise.

Third, UserRoster is checking to see who is logged onto the database. It's a nice to have, so in your TEST copy of the database, you can just bypass it for now. You also might compare your version to the 2010 update available in the utteraccess code archive here http://www.utteraccess.com/forum/Who...-t1897146.html, since there were revisions (obviously) since 2003 to the way Microsoft handles the user roster.

[powerski]

HI, Thanks for your response. I did a further test where I created a new database "Database1" and then created a single VBA module to get the logged in user ID. When I try to run this is also pops up the "Macros" dialog box even thought there are no macros in the new database. Is this a setting somewhere? As with the previous example once the "Macros" dialog pops up and I select "Cancel" - the only option available - the Debugging just ends with no messages.

[June7]

How did you try to run the procedure? Is it a Sub or a Function? If a Sub, did you click cursor into the procedure then click Run from the ribbon?

[powerski]

How did you try to run the procedure? Is it a Sub or a Function? If a Sub, did you click cursor into the procedure then click Run from the ribbon?

Hi Thanks for the reply.

It is a sub, the main problem is the debugger always opens the dialog to select a macro - even if there are no macros and then dies and does not allow me to step through code.

Also if I am running a form and for instance I get a message across the bottom of Access, like "The recordset does not have the primary key for table "tablename" I cannot press F5 to step into the code.

[June7]

The cursor is within the procedure? The procedure is in a general module?

I just noticed in your posted code the Sub declaration line has an apostrophe at the beginning. This causes the line to be a non-executable comment. Why is there an apostrophe?

[powerski]

This is a very large and complex app (at least to me) with dozens of forms, VBA Modules, and Class Modules,and hundreds of queries and reports and many tables.
The application has both ULS and its own granular level security.
The application when started goes directly to an Admin console (for the admin ID only) or the Main Menu. Exiting the Form quits out of Access
It also seems to stop key combinations that would open the debugger or quit back to the Access environment.
When it does throw an error it does not give the option to debug or step into the code.

I'm really at my wits end with this as I can't make any progress in cleaning up the issues caused by moving the back end to SQL Server.

Thanks for your help

[June7]

Doesn't really answer my question about the apostrophe in the code. The apostrophe would cause error. If it really is in your code module, fix.