We help IT Professionals succeed at work.

Access 2003 subform blank with different users

Last Modified: 2012-06-27
This is an Access 2003 Project developed on my PC. Recently moved the backend to Server 2000. Minor design flaws aside, the project is running well.  I've copied the development version, renamed it and connected it to the production database.

This is a work-order tracker, and the essential structure is a Work Order header, with two child-tables - Materials and Time.  The primary form shows header detail with the material and time as two subforms

When I log on to my PC under under the Win ID I used to develop it, all works well.  When I log on to a PC as another user (like the final users who will use the app), two subforms appear blank, and when I try and add a material item, it errors out saying the object doesn't exist or is closed.

Only the work order editor form (with it's two subforms) has a problem. All other forms seem to work ok.

This appears to be a PC-User related issue as when I put the app on another machine were the primary user is an Admin the subforms display correctly.
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

In many cases this is a Reference issue. On the machine where you're having trouble, open the db, then open the VBA Editor (Ctrl + G), then click Tools - References. If any are marked MISSING, you'll need to remedy this. In many cases, you simply have the reference incorrect - just note what reference you're dealing with, then uncheck it, close/reopen the references dialog, then locate and re-reference that item (note: this also forces Access to rebuild the refs, which often goes a long way towards fixing these problems).

If the reference isn't available on the machine - for example, you've used an ActiveX control that is not available on that machine - then you'll have to deploy, install and register that control to each end user machine.

If you can provide a list of references the app uses, we can point out the likely candidates.

This can also be caused by corruption, or by inadequate rights on the server.

To combat corruption, you should compact and repair your db. Make a backup first.

How does the server authenticate users? If it uses Windows authentication, make sure that the users have sufficient permissions on the server to work with the data.



Went over the users and permissions and all seem fine. Access (on this machine) appears to have the correct references. I printed a screen shot if them, then logged in as the other user and ran app and they were the same.

Note that I have two log-ons on the development PC. The one which is the ID I normally log in with and which the app was developed, and another recently created to replicate a typical user. When I start the app on this PC as "me" it works fine. When I log out and log in as the "maint" user, it breaks. Same machine, same Access.

Enclosed is the code that fails when logged in as a reg users (with full PC Admin rights). Also - on other machines the main admin user can run the app correctly. If it was corruption, it would not seem to be so selective... No?

All the users I've created to attach to the server have dbo permissions.  It is just this one form will not load/instantiate these two subforms.  The subforms will open and display data individually.

The two subforms are displaying child records of the parent Work Order Header. It is the "selective breakage" that has me flummoxed!  It isn't that the subform is empty - it isn't even populated on the main form - all that shows are two white boxes.

????????    :-)

=Alan R.
Call Forms("WOEditor").Controls("WOTimeSubForm").Form.AddWOTime(Me.RegHrsFld, Me.OTHrsFld, Me.WrkDunFld, Me.DatOfTimeFld)

Open in new window


Further details:  (upping the points for hopefully faster help!)

It appears that individuals who have full admin to the MS File Server can run this application correctly. They are NOT users under the MS SQL Server 2000 databse security. Those users have been created as login's (probably incorrectly) and given same access as "dbo".  They can connect but have this problem, where the sub-forms are blank.

Rephrasing the question: With a simple Access 2003 project having it's backend on an MS SQL Server 2000 backend, how should users be created?  The application maintains a simplistic user access control, and all reading/writing to the db is done via VBA code so they could prob get full read/write access to SQL Server. Users can't really 'break' access stuff they aren't allowed to.

This shouldn't be so hard!  Obviously I don't know much about how to how to setup up rudimentary user-structure for MS SQL 2000. There are so freekin' many options!
This one is on us!
(Get your first solution completely free - no credit card required)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.