CODEWITHSUNDEEP
sql-serverms-access
Radio Doc
Radio Doc

Reputation: 397

Access form will not populate records from or write to ODBC table

I have a split database running in Access 2016 that needed the back end tables migrated from an .accdb file located on a shared drive to SQL Server (specifically MSSQL Server 14 Express) due to an impossibly aggravating "file in use" that was shutting down any chance of multiple users continuing to use the database at the same time. After the migration, one of the main forms that I use for personnel management has decided that it doesn't want to function at all, and I'm at a loss for how to get this going again.

I'm having a couple of issues here, and to be honest I'm not sure if they're related.

  1. I can open the personnel table directly, however any attempt to update a record is instantly met with a Write Conflict error.
  2. The form that interfaces with said table will not populate any records unless the Recordset Type is set to Snapshot (great for reading data, but doesn't help me when it comes to updating personnel records).

Does anyone have any suggestions at where I can look, either in Access or SSMS, to get my Access 2016 front end writing to the table again?

Upvotes: 0

Views: 162

Answers (1)

Radio Doc
Radio Doc

Reputation: 397

Consolidating the fixes in this answer here for anyone else with this issue in the future:

  1. Access allows YesNo fields to be null, so when converting to SQL server, YesNo gets remapped as Bit which cannot contain null values (and when you're running the data import tool it's just a good idea to ensure the allow null checkbox is cleared for Bit fields). Again, thanks to user Kostas K. for pointing me towards that solution.
  2. Sometimes the answers that we need just require asking the right questions! I was finally able to come across this post from 2008 that solved this problem. Turns out the issue was the Data Entry setting on the form, which was set to yes. Changed to no, everything works again.

Upvotes: 1

Related Questions