We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

Appending a record to my SQL Server is causing Access to crash and reboot

53 Views
Last Modified: 2021-03-17
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.


Any ideas?


Comment
Watch Question
Senior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 28 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE