select a unique record identifier for linked tables

[TonyLackey]

Hi,

I use access as a tool to manipulate data and then update our master database (Sybase).

When selecting tables in the first instance you can select the "unique record identifier" and Access will allow you to update the specific data happily.

If i send this database to a collegue in a different office, even if they use an ODBC with the same DSN, Access quite often loses the "unique record identifier". Access then gives the following error when trying to run an update query "Operation must use an updateable query!).

Apart from re-linking the actual tables and selecting the correct unique record, is there a method in Access to re-set these manually? (I know you can assign the key, but this doesn't work for linked tables)

Any help would be appreciated as it would simplify my instructions to my collegues (most of which don't use access on a daily basis).

NOTE:-

Why send instructions to none access users?
I'm posting a solution that literally saves days of work and increases accuracy on evry implementation we carry out. The post is displayed on our solutions intranet, thus giving everyone access to it...... I can't write code, but can use Access fairly well.

Thanks in advance.....

 

[Pat Hartman]

Are you linking to a view rather than to a table? If so, change the link to link directly to the table. If there are multiple indexes defined for the Sybase table, Jet assumes that the first one is the primary one. If that is not the case, drop and recreate the indexes and make sure that the unique one is the first to be defined. I don't remember whether "first" means physically first or alphabetically first so you may need to change the index name to get it to be alphabetically first.

If none of that helps, you can run an SQL DDL (Data Definition Language) statement to define a psuedo index. This index exists only in your Access database. It does not in any way affect the linked table. Look up Alter table in the MS Jet SQL reference. To find it, open the help file from the main Access window and go to the table of contents tab. You will find the relevant section near the bottom of the list. If you have a version of Access newer than A97, the search features will not bring up any help with SQL even though the help file is installed. Here's an example from one of my databases:

ALTER TABLE dbo_EC_Employee ADD CONSTRAINT NaturalKey UNIQUE(Person_ID, Company_ID);

 

[TonyLackey]

Unique record identifier

Pat,

Thanks for you r help with this matter, i used the
ALTER TABLE routine to solf my problem.

Thanks for your help, it's appreciated

Cheers

Tony