Access 2003 subform blank with different users

Posted on 2009-03-30
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.
Question by:Harrzack
  • 3
LVL 84
ID: 24027416
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.

Author Comment

ID: 24030873

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


Author Comment

ID: 24050607
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!

Accepted Solution

Harrzack earned 0 total points
ID: 24108567
By assigning the database users to the sysadmin role, as well  as database creator, the problem went away. Not sure if this is a 'proper' solution, but in this case it is safe and does the job. Case closed.

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 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

22 Experts available now in Live!

Get 1:1 Help Now