When I create a DSN connection to SQL Server using the wizard, I am able to link it to a view. In this case, Access recognizes it as an editable table.
But if I use a DSN-less connection to a view using vba code (Method 1 from https://support.microsoft.com/en-us/kb/892490), it is linked as a table that is NOT updatable.
I don't know why there is a difference, but how can I make a connection to a view in SQL Server (either as a table or a query in Access) and have it be updatable?
Edit: When I make a DSN-less connection using a table in SQL Server rather than a view, it is updatable in Access. I would have guessed my problem has to do with views not having a unique ID, but I'm confused why a DSN connection can be updatable while DSN-less cannot.
It's not because it's DSN-less, but because you created it via VBA. If you link the view via the Access GUI, it asks you for the primary key.
But via VBA, it doesn't know the primary key, so the linked view is not updateable. With a table, Access gets the primary key automatically via ODBC, so the table works.
Solution: set the primary key after linking the view via VBA:
If you have many views, and re-link them regularly (e.g. going from dev to production database), it becomes impractical to hardcode their names and PKs. I wrote a function to retrieve all primary key indexes from linked views, and re-create them after linking.
If you want, I can dig it up.
Edit:
This is what I do:
To link a table or view the first time, I use this (strTable is the table/view name):
For tables, the primary key (PK) is determined automatically. For a view, I get the Access dialog window to specify the PK, same as if I link the view manually.
The PK information is stored in the TableDef object for the linked view, so I never have to hardcode it anywhere.
To store the PK information for all linked views, I have this table (it's a local table in the Access frontend for simplicity):
and this function. All Views (and only Views) are called "v_*", so I can list them by name.
I'm actually not sure if you can determine from a TableDef object whether it points to a table or view.
When I make changes to table or view structures, or change the source database (this is done by changing the output of
ODBC_String()
), I call this function:Note:
Instead of the table
t_LinkedViewPK
, a dictionary object could be used. But while developing this, it was very useful to have it as an actual table.