Solved

Access 2003 subform blank with different users

Posted on 2009-03-30
6
294 Views
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.
0
Comment
Question by:Harrzack
  • 3
6 Comments
 
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.
0
 

Author Comment

by:Harrzack
ID: 24030873
LSM -

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

0
 

Author Comment

by:Harrzack
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!
0
 

Accepted Solution

by:
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

12 Experts available now in Live!

Get 1:1 Help Now