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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 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