Subform no longer accepts new records

[RayMilhon]

I have an Access 2010 Database there is a main form with member demographic information on the main form is a tabcontrol with 2 tabs each containgin a subform. 1 To track user comments specific to that member 1 to track the events that member attends. Yesterday it was working fine.


Today I added a new report to the reports form did not tough the main form or any of it's subforms. Now no new comments can be added. Even opening the table that the form has as it's recordsource won't let me add new records. The table is a linked table to SQL Server 2008 R2. If I go to SQL Server 2008 I can add new records but the link from Access won't let me???????? Like I said it worked fine yesterday. What happened? There are 15,832 Records in that table is there a limit to how many records Access will support on Linked Tables? I've never run into this before. Any assistance would be appreciated.

[ssanfu]

Sounds like corruption of the Access FE. Create a backup (or 2) of the Access FE.
Have you tried a "Compact and Repair"?

If that doesn't fix the problem, you can try the undocumented decompile command. (it has saved me more than once )



READ ALL OF THE FOLLOWING BEFORE ATTEMPTING TO USE THE decompile command!!!


Use a COPY of the dB!!!!!!

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "C:\SQL 2014 Database\FE\FE_sql_2016_02_03.accdb" /decompile

The part in BLUE is the path to the Access program.
The part in RED is the path to the Access dB.

The quotes are required. Don't change them.
The space between the path to Access and the path to the Access file is required.

Copy the entire string (after you have modified the paths as necessary for your dB) and paste the string into the Run command, then hit enter...

Good luck...

[RayMilhon]

Actually now that I think about it I did a Compact and Repair this morning prior to discovering this error.

[RayMilhon]

Nothing has helped still getting the error.

[ssanfu]

is there a limit to how many records Access will support on Linked Tables?

No, as long as the tables is properly linked, the number of records is limited by file size.

Have you tried creating a new, empty dB, then importing all objects from the old dB to the new dB?

Or delete the links to SQL and recreate them.

Create a new dB, link to SQL, try adding a record. Maybe somehow the permissions have changed??

[Micron]

Did anyone make any changes to the linked table such as add a field with a data type that Access doesn't like? Or add a PK with Identity set to "no". Can you edit a record? I'd try creating a new db with only that table in it and try to add or edit after determining if you can edit the current connection. Don't know if you're using ODBC to connect or what, but if you can edit other tables, I guess it's not an issue of rights having being altered.

[RayMilhon]

Well worked on this all day finally got frustrated and dropped the attached table and re-linked it. That fixed it. Still don't know what caused it but it is corrected now. thanks for the help.