Anyone up for a challenge?
I want to say in advance that I believe this problem will be a challenge for all but the most experienced experts here on EE familiar with Access 365 FE and a SQL Server BE.
I am running an append query to the SQL BE from the accdb that is causing Access to crash.
When Access crashes it immediately exits to the operating system and makes a backup of the source code.
The query that I am attempting to append has about 2000 rows. Each record has 240 fields. I run this query each night as part of the system maintenance. No code changes have been made although it is possible that the data has changed.
While the problem was showing up while running a query, I was able to determine which record is causing the problem by creating code to append the same data using addnew.. update.
The system locks up after executing the update command after successfully updating the first 13 records. The 14th record is causing the problem.
I also have been able to determine that the field: searchnumberentered a short text field with a size of 75 has the contents: IR30CPQ045
When I completely delete the data and then retype the problem still occurs
When I add an xxx before and after the data the append completes successfully
When I delete the row and retype the data into another row the query completes successfully
Other things I've tried without help:
I have run a C&R on the back end--no change
I have decompiled the front end--no change
I have searched the string that is causing the problem for unprintable characters two ways... I used the len function to verify the number of characters is correct and I used the ASC function to verify each character is the correct asci character.
I solved the problem without ever finding a solution by deleting the row.
Now I am having the same problem when I append another table, so I really want to get to the bottom of this
VERY important is that the problem did not begin until I created the new field that is causing the problem. I created in the SQL back end (field:searchnumberentered )
I also created that same field in the Access front end and populated the field.
Yes, I refreshed the link to the BE.
I am completely stumped.
I deleted the field in the back and then recreated it .
I also rebooted the SQL Server. (all no help)
Since I am now having the problem when appending another table I intend to proceeded to track down the problem field while waiting for advice from the experts here.
I am hoping that this will shed some light.. however, I cannot continue solving the problem by deleting rows. A solution needs to be found to insure robust operation.