Continuous form won't show 'new record' while using SQL Server linked tables

1
votes

I have an Access/VB application that was using Access for its database, but is now using SQL linked tables. My problem is that my continuous forms have quit displaying the 'new record' row. When it was hooked to the Access db, the continuous form would display X amount of rows, and then one additional row, filled in with the database default values, waiting for the user to click 'update'. Now my program is missing this additional row and my users are unable to add a new line of data. I have checked the form properties and the "Allow Additions" setting is set to 'Yes', as well as Allow Edits.

Any help would be appreciated. Thank you!

1
sql-serverms-accessvbams-access-2010
You should check the SQL server permissions to make sure whatever credentials you're using have the rhe right to Insert. – RubberDuck
And just to confirm, you're using linked tables, not adodb recordsets? – RubberDuck
ckuhn203 - I've checked and the credentials do have the rights to insert. Also, yes it's linked tables rather than adodb recordsets – Snicklefritz
Another kind of dumb question... You're using MS SQL Server as a back end? I just assumed so. – RubberDuck
Yup! i guess I should have clarified, but it is 2008R2 and Access 2010 – Snicklefritz

1 Answers

2
votes

If you are linking to a table without a primary key, or to a view for which MS Access cannot determine the primary key, you can get this effect, as Access can't properly uniquely identify the record to be changed.

The solution to such problems is to add a primary key to your SQL Server table and refresh the link, or if you are linking to a view, to use Access DDL to artificially add a primary key:

CREATE INDEX <Index Name>
ON <Linked Table Name> (<Field Name>[, <Field Name>]... )
WITH PRIMARY