Can not enter new record via Access front end after linking tables to SQL Express

Question

I have a Time & Billing Access program. I just migrated the tables to a SQL Express database and using it as the back end: tables all linked to the front end Access program.

I can enter a new records to all of the tables except the TimeSheet table. There are only 84,365 records in the table. I can only enter a new record into the table using Microsoft SQL Server Management Studio 18.

I saw in this discussion group a similar post with the same issue: SQL 2005 table that I can not add records to in MS Access Front end. Appears to be the same issue I am having. That posting did not get a reply that solved the issue.

Hope there is an answer.

Answer 1

Well, first I would open the table in question from Access as a linked table. Test edit a row, move off the row, does that work? (if yes, we we at least verify that the table can have and allow edits). If you can't edit the table from the Access UI, then of course it not going to work when trying to use forms + code + the application parts.

So, if the table allows updates and editing from just opening that linked table?

Then next is to try adding a row to that table (again, using + opening the linked table in Access. And again, no use attempting to use the forms + complex code that may well exist in the applcation for this simple test.

This test may well require to you enter a value for a colum that used in table relationships.

And even before you do above? Try opening the linked table in design view in access. You can ignore the nag messge about "read only". The issue is now with the table in design view, does Access correctly SEE and HAVE and KNOW the table has a PK colum defined?

Access tables can work without a PK, but when you link to sql server, they cannot be updated. (they are read only).
So, after a migration, most tables will and should work (and you noted this).

However, while most things can and should JUST work, some access tables may well not have a PK defined, and they need such a column. This would suggest you add a PK row to that table (using sql server tools). And make sure it is PK set, and ALSO has identity set, and auto increments.

You then have to re-link the given table(s) in Access for this to work. After you re-link, then again open that offending table in design mode (from access) and again verify that Access now sees a PK setting for that table.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada