Some SQL tables view only in Access others full edit, confused. (1 Viewer)

Zedster

Registered User.
Local time
Today, 13:45
Joined
Jul 2, 2019
Messages
168
I have an access database which connects to a series of tables on a SQL server. Each time the database is open All tables are linked using a function to create a DSN Less tables.

But when I go to add or edit records, some tables allow this, others allow no edits or additions (yellow star at bottom is greyed out).

The same function is used to link all tables, so I am assuming the issue is not with access.

I can add/edit all tables using TSQL in SSMS. I am no expert in SQL SERVER, but looking at properties/permissions between a table that allows edits in Access and one that doesn't they look the same.

It's driving me a bit mad, any help would be appreciated.
 

Minty

AWF VIP
Local time
Today, 13:45
Joined
Jul 26, 2013
Messages
10,355
If you don't identify a unique field combination key on connection or the table doesn't have a primary key the tables (Or views) will be read-only.
 

Zedster

Registered User.
Local time
Today, 13:45
Joined
Jul 2, 2019
Messages
168
Thank you Minty, indeed all the tables that were read only did not have a primary key set. I have not come across this before because I virtually always set a primary key. The offending tables were copied from another database some time ago for futureproofing and I must have forgotten to set primary keys on these. Good lesson learned (y)
 

Users who are viewing this thread

Top Bottom