Access 2003 subform blank with different users

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.
Who is Participating?
HarrzackConnect With a Mentor Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
HarrzackAuthor Commented:

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

HarrzackAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.