Unique Table issue.

Question

Unique Table issue.

fryere

Ten Centuries

Points: 1054
More actions
January 22, 2004 at 7:01 am

#65004

I have an Access Project that a form demands that Unique table property be set for a user, but not for me. I kept thinking that it had something to do with a lack of user roles to the data behind the form, but I finally discovered the Unique table problem. Does anyone know why it wouldn't care if the property was set for me, but would for another user. BTW: I am the DBO.

Thanks.
fryere

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

lawrencek SSC-Addicted Points: 444 More actions · Answer 1

Which version of access are you using?

I have seen similar inconsistent behavior. I believe that for some reason Access uses user.table for the default unique table. Therefore, when you the dbo are logged in it works fine because dbo.table exists, but then other non dbo users have a problem. When you specify a unique table then just the table is used and sql server looks first for user.table then dbo.table for you.

Just my hypothesis not been tested by me.

fryere Ten Centuries Points: 1054 More actions · Answer 2

We are both using Access 2000.

I felt like it had something to do with me being the DBO.

This issue was really causing be to rack my brain, since I thought that it had something to do with the user not having the correct permissions to the tables.

Thanks for the response.

fryere

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 3

Hi,

Is your form based on a view or a table?

If a table, you should not need the unique table property to be set at all. I think in this case it is more likely there is a problem with the table. Check that you have correctly set your primary keys and identity values in SQL. Also ensure that your users have sufficient security rights to perform whatever actions they need to do on the table.

If a view, I would still check the above, but this is where you will need to use unique table to make the dataset updatable.

HTH

Chris

David.Poole SSC Guru Points: 76000 More actions · Answer 4

Do you have tables created by users other than the dbo?

For example, do you have a dbo.orders and a myuser.orders table?

LinkedIn Profile

noeld SSC Guru Points: 96590 More actions · Answer 5

Make sure you qualify the owner on the on the record source!

eg: instead of Tblame use dbo.TblName

HTH

* Noel

fryere Ten Centuries Points: 1054 More actions · Answer 6

cwedgwood50: The Form uses a Sql Statement that is set to the Form's Recordsource when the Form Opens.

D Poole: All Table are created by me dbo.table.

noeld: I have seen Board Members discuss putting the owner's name in the SQL statement, but I never have. Probably should start.

Thanks.

fryere

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 7

If your sql statement has table joins within it, it will effectively be a view and need to have the unique table property set in order to be updatable.

I cannot see how you would be able to perform an update under these circumstances if unique table were not set. This is information Access needs to perform the update.

You can however view data without getting any warning messages. Could it be that you are only viewing the data, but your users have tried to change it?

HTH

Chris

fryere Ten Centuries Points: 1054 More actions · Answer 8

cwedgewood:

What happen was that I didn't have the Unique Table property set on the Form. The other user wasn't able to insert a record (which makes sense because the form was base on a SQL statement with a Join). What caused the confusion was that the same Form would let me insert records, which led me to believe the user didn't have the correct rights to update the table. When I discovered it was a Unique Table setting issue, I thought that I should have had the same problem, but didn't.

I will be the first to admit that I am not an expert on table owners, but it seems like Access should need to know the Unique table regardless who owns it.

fryere

lawrencek SSC-Addicted Points: 444 More actions · Answer 9

Just because a view or sql select has a join does not mean it is not updatable. Check BOL on this.

The reasion I think it has to do with the way Access determins the table owner is Microsoft added the Record Source Qualifyer property in later versions of Access, and it happens that the default is dbo.