Unique Record Identifier (1 Viewer)

deletedT

Guest
Local time
Today, 16:07
Joined
Feb 2, 2019
Messages
1,220
When I try to import a view from a sql server database into a Microsoft Access FE, I receive a message to select a Unique Record identifier.
I can't understand why a view needs a unique record identifier.
All tables in the view have their own primary key.

Why access asks to select an identifier? And which one of fields I have to select?

this is the view:



and this is the message I receive:


Any kind of advice is much appreciated.


-
 

Attachments

  • 2019-11-27_10-09-36.jpg
    2019-11-27_10-09-36.jpg
    54.3 KB · Views: 167
  • 2019-11-27_10-12-38.jpg
    2019-11-27_10-12-38.jpg
    66.4 KB · Views: 178

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
13,140
Hi. I am just guessing here, but you probably already know if you don't select a unique identifier, the linked view will be "read-only" in Access.
 

AccessBlaster

Custom title here:
Local time
Today, 08:07
Joined
May 22, 2010
Messages
2,816
My guess is that Access / SQL is trying not to introduce corruption.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:07
Joined
Jan 20, 2009
Messages
12,108
Access can't work it out. Choosing All Columns rather than the individual columns mingt make a different.

It will be a composite key. Choose the three primary keys from the tables.

BTW You can specify a unique clustered index on a View if all tables come from the same schema. This might let Access work it out for itself.
 

deletedT

Guest
Local time
Today, 16:07
Joined
Feb 2, 2019
Messages
1,220
Access can't work it out. Choosing All Columns rather than the individual columns mingt make a different.

It will be a composite key. Choose the three primary keys from the tables.

BTW You can specify a unique clustered index on a View if all tables come from the same schema. This might let Access work it out for itself.



I'm sorry but it seems I can't understand the point. I can understand why a unique field is necessary if it was a linked table, but a view made of three tables and having its own relationship.....What does this unique field will be used for?

Well, I will follow both suggestions (selecting all PK fields & specifying a unique index) as soon as I'm back to office to see what will happen.


One more question if you don't mind.
What will happen if I don't select any field? I didn't select any field and the linked view is working just perfect.
Will it cause any change in performance? or will it cause any data corruption?
 
Last edited:

deletedT

Guest
Local time
Today, 16:07
Joined
Feb 2, 2019
Messages
1,220
Hi. I am just guessing here, but you probably already know if you don't select a unique identifier, the linked view will be "read-only" in Access.

Sorry, but I can't understand. If it was a table, yes you are right. but it's a view made of 3 tables. Each table has its own unique identifier. Isn't it enough for Access? Does Access needs another unique identifier? in which one of tables?
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:07
Joined
May 11, 2011
Messages
10,126
Access doesn't know it's a View. Access just sees an external data set and couldn't automatically detect a primary key.

If you only need to read this data, carry on everything will work. If you need to add\edit\delete this data from Access then your hosed. In my experience even manually selecting a unique identifier for Access doesn't work in allowing it to add\edit\delete data.
 

plog

Banishment Pending
Local time
Today, 10:07
Joined
May 11, 2011
Messages
10,126
Actually, you are not hosed if you need to add/edit/delete this data. You just link to the individual tables that comprise this View and add/edit/delete them.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
13,140
Sorry, but I can't understand. If it was a table, yes you are right. but it's a view made of 3 tables. Each table has its own unique identifier. Isn't it enough for Access? Does Access needs another unique identifier? in which one of tables?
Hi. Perhaps this other thread might be able to explain it better.
 

deletedT

Guest
Local time
Today, 16:07
Joined
Feb 2, 2019
Messages
1,220
Access doesn't know it's a View. Access just sees an external data set and couldn't automatically detect a primary key.

If you only need to read this data, carry on everything will work. If you need to add\edit\delete this data from Access then your hosed. In my experience even manually selecting a unique identifier for Access doesn't work in allowing it to add\edit\delete data.

You are right. I tested it and the view is only read only. No matter I select any field or not.

I think it's better to use Access queries.
Thanks again.
 

deletedT

Guest
Local time
Today, 16:07
Joined
Feb 2, 2019
Messages
1,220
Hi. Perhaps this other thread might be able to explain it better.

Thanks for the link. I'll read it tomorrow morning in the train.
It seems I have to go back to Access queries.

I hate using Access queries when I have linked table to sql server. When I add several linked tables to a query, the relationships aren't added. I have to add the relations between the tables manually and what I hate most is that added relations have no 1 or many signs.

Thanks for the link.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
13,140
Hi. You're welcome. Hope it helps. Good luck!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:07
Joined
Jan 20, 2009
Messages
12,108
What will happen if I don't select any field? I didn't select any field and the linked view is working just perfect.
Will it cause any change in performance? or will it cause any data corruption?

Without a valid unique identifier Access can get very muddled.

A colleague in a similar situation just chose the PK of one of the tables. Every record that had the same value in the field that had been designated as the key displayed the identical record to the first one loaded with that value.
 

Users who are viewing this thread

Top Bottom