Appending a record to my SQL Server is causing Access to crash and reboot
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?
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?
240 fields is a lot of fields.
Have you calculated the total number of characters allowed based on your field definitions? I believe there is a limit of 4000 characters in an Access record (excluding memo/long text fields). Is there a chance that this record exceeds that number?
Do you have a valid reason for having 240 fields in this table? that is generally considered quite excessive. Have you considered normalizing the data to get each table down into manageable chunks (30-50) where all the fields are directly related to one another?
Have you calculated the total number of characters allowed based on your field definitions? I believe there is a limit of 4000 characters in an Access record (excluding memo/long text fields). Is there a chance that this record exceeds that number?
Do you have a valid reason for having 240 fields in this table? that is generally considered quite excessive. Have you considered normalizing the data to get each table down into manageable chunks (30-50) where all the fields are directly related to one another?
<<I believe there is a limit of 4000 characters in an Access record (excluding memo/long text fields). >>
That's only on an Access DB.
<<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.>>
a. Make sure you are bracketing field names. Never know when Access/SQL is going to have a problem with a field name.
b. As a thought, break it up in to an append, followed by an update query.
However with that said, I don't think the size of the operation has anything to do with it, but rather the data and the field.
Jim.
That's only on an Access DB.
<<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.>>
a. Make sure you are bracketing field names. Never know when Access/SQL is going to have a problem with a field name.
b. As a thought, break it up in to an append, followed by an update query.
However with that said, I don't think the size of the operation has anything to do with it, but rather the data and the field.
Jim.
Jim,
re:<<I believe there is a limit of 4000 characters in an Access record (excluding memo/long text fields). >>
your comment: That's only on an Access DB.
I just wasn't sure whether violating that constraint in an Access record could cause problems with the Append query.
re:<<I believe there is a limit of 4000 characters in an Access record (excluding memo/long text fields). >>
your comment: That's only on an Access DB.
I just wasn't sure whether violating that constraint in an Access record could cause problems with the Append query.
The crash itself can be Access, the driver (ODBC) or SQL Server.
Check the data types first, in SQL Server in involved tables and the data type of the linked table.
Also check the other columns, especially the primary key columns. Is an imprecise data type involved?
Also check the SQL Server side for constraints and triggers.
And of course: Did you check the involved tables in SQL Server (DBCC CHECKTABLES)? Are they okay?
Check the data types first, in SQL Server in involved tables and the data type of the linked table.
Also check the other columns, especially the primary key columns. Is an imprecise data type involved?
Also check the SQL Server side for constraints and triggers.
And of course: Did you check the involved tables in SQL Server (DBCC CHECKTABLES)? Are they okay?
ASKER
Anders...
I'm not sure what you mean by ASCW.
The field should not contain Unicode characters. In Access I have unicode compression set to NO and on the SQL Server I'm using varchar NOT nvarchar.
I have wondered whether it is a unicode issue.. is there something else I can do? Sometimes the staff uses cute and paste from email into the Access db.. that has caused unicode issues in the past which is why I use varchar.
Any other thoughts?
Dale...
Mostly properties of a component which need to be searchable in simple queries. I definitely have fields that are no longer used but identifying which I can delete without causing other problems has been a challenge. (Thats one reason I bought FMS)
Ste5an.. in access it is short text, in the SQL server it is varchar. To triggers or contraints.
No, I have not tried db checktables.. I will read about that now.
I'm not sure what you mean by ASCW.
The field should not contain Unicode characters. In Access I have unicode compression set to NO and on the SQL Server I'm using varchar NOT nvarchar.
I have wondered whether it is a unicode issue.. is there something else I can do? Sometimes the staff uses cute and paste from email into the Access db.. that has caused unicode issues in the past which is why I use varchar.
Any other thoughts?
Dale...
Mostly properties of a component which need to be searchable in simple queries. I definitely have fields that are no longer used but identifying which I can delete without causing other problems has been a challenge. (Thats one reason I bought FMS)
Ste5an.. in access it is short text, in the SQL server it is varchar. To triggers or contraints.
No, I have not tried db checktables.. I will read about that now.
ASKER
DB CHECKTABLEs seems more complicated than I expected. Any suggestions on how to run it on my table?
I have wondered whether it is a unicode issue.. is there something else I can do? Sometimes the staff uses cute and paste from email into the Access db.. that has caused unicode issues in the past which is why I use varchar.This kind of copy and paste may lead to invisible data being copied. Copy the content of that column and paste into a text editor like notepad++.
ShortText is per se Unicode compatible. VARCHAR() is NOT Unicode compatible. Here you need NVARCHAR(),
and varchar() fills the contents of the field with spaces to reach the number of characters defined.
Try something like:
Try something like:
SELECT Len([VarCharFieldName]) as FieldLen, Sum(1) as RecCount
FROM yourTable
GROUP BY Len([VarCharFieldName])
btw, once I had a similar case, where the root cause was minor corruption of the Access database file. So you should create a new one and import all your objects as compact and repair cannot cure allow problems.
Delete that 14th record - after having noted the content of the single fields.
If the query now runs, the record was bad - then recreate it by typing the contents into a new record.
If the query now runs, the record was bad - then recreate it by typing the contents into a new record.
ASKER
Gustav.. yes, that's exactly what I did the first time the problem showed up, but now it has happened again. I'm hoping to understand whats going on so I can prevent it..
Ste5an.. I'll give that a try.
Ste5an and Dale...Prior to this, I have demonstrated that there is a "bug" that becomes exposed when you use Access as a FE and SQL as the BE when the PK contains a nvarchar field and the field includes a variable that uses unicode. The problem I demonstrated, which was submitted to Microsoft by Anders, was that queries run from Access appear as #Deleted.
I was able to solve that problem by changing from nvarchar to varchar, in a field where the first asci 128 is all that I care about.
I'm using SQL driver 11.. is there any reason to try a new driver?
Thanks
Ste5an.. I'll give that a try.
Ste5an and Dale...Prior to this, I have demonstrated that there is a "bug" that becomes exposed when you use Access as a FE and SQL as the BE when the PK contains a nvarchar field and the field includes a variable that uses unicode. The problem I demonstrated, which was submitted to Microsoft by Anders, was that queries run from Access appear as #Deleted.
I was able to solve that problem by changing from nvarchar to varchar, in a field where the first asci 128 is all that I care about.
I'm using SQL driver 11.. is there any reason to try a new driver?
Thanks
Just run the append query via Pass through (as action)...if that runs then,
probably you have some underlying code that has some code that is not spotted.
Decompile your application and recompile it....if no errors pop up then you might have a case (rare but i have seen it) where the structure of your code is performing an "illegal operation"...transferring to a new form should resolve it.
probably you have some underlying code that has some code that is not spotted.
Decompile your application and recompile it....if no errors pop up then you might have a case (rare but i have seen it) where the structure of your code is performing an "illegal operation"...transferring to a new form should resolve it.
is there any reason to try a new driver?
I think you know the answer. Indeed as the current version is 17.
he problem I demonstrated, which was submitted to Microsoft by Anders..[]Please post a link to the repro.
ASKER
Keep in mind, everything was working correctly until I added the new fields on the BE...
Now the data in one of the new fields is causing the problem.
I don't believe in coincidence...
Its my understanding that if I update the driver, I need to copy the new driver to all the workstations???
Now the data in one of the new fields is causing the problem.
I don't believe in coincidence...
Its my understanding that if I update the driver, I need to copy the new driver to all the workstations???
Have you tried the pass through ?
Did you relink the table after adding the columns? How are these columns named (in the past some reserved words did cause weird driver issues)? Is the PK the same? Is the data type of the new columns a precise one?
Yes, you are correct that if you update the driver in the connection string, you will need to deploy that new driver on all of your users computers.
Dale
Dale
ASKER
John.. <<Have you tried the pass through ? >>
Not yet, I need to wait until the staff leaves for the day. I did however create a routine using the addnew/update method to update one row at a time.. when I get to the problem row the system hangs but does not reboot. Using the query it reboots...
Ste5an <Did you relink the table after adding the columns? How are these columns named (in the past some reserved words did cause weird driver issues)? Is the PK the same? Is the data type of the new columns a precise one? >
Relink--Yes
ordernumberentered and searchnumberentered are the two new field names
Did not change PK
datatype varchar(75)
Dale.. thanks what I thought.... That would definitely be a huge undertaking for me.
ASKER
The query I am running that crashes the system is :
INSERT INTO Parts4Sales SELECT [Table0074].* FROM Table0074 WHERE [Table0074].OrderNumber <> 'NA' AND [0074].OrderNumber <> 'RESERVED' ;
The system does NOT crash if I removed the WHERE clause.
Any ideas?
INSERT INTO Parts4Sales SELECT [Table0074].* FROM Table0074 WHERE [Table0074].OrderNumber <> 'NA' AND [0074].OrderNumber <> 'RESERVED' ;
The system does NOT crash if I removed the WHERE clause.
Any ideas?
ASKER
Gustav.. Dale.. I am actually using the Native driver 11Do I need to upgrade?
You don't have to upgrade, but you have to test something to get rid of the error. This upgrade is one option.
Though it shouldn't make any difference, try this SQL:
Though it shouldn't make any difference, try this SQL:
INSERT INTO Parts4Sales
SELECT [Table0074].*
FROM Table0074
WHERE [Table0074].OrderNumber NOT IN ('NA', 'RESERVED')The system does NOT crash if I removed the WHERE clause. Any ideas?
INSERT INTO Parts4Sales
SELECT [Table0074].*
FROM Table0074
WHERE [Table0074].OrderNumber <> 'NA'
AND [0074].OrderNumber <> 'RESERVED';1) Use explicit column names for your insert and and select.
2) Is the second table name in the WHERE condition a typo or is there a table involved which has a numeric object name? In the latter case rename the table.
3) What is the data type of OrderNumber in Access, in your linked table and on the SQL Server side? Execution order is different from the logical order. So missing casts can be the error.
ASKER
Gustav.. We may be onto something.
I tried your code and got the message:
Cannot open database. It may not be a database that your application recognizes or the file may be corrupt.
After receiving the message I changed the table number to point to a different table (0073) and the code executed without issue. The table which worked is in the same database as the table which is throwing errors.
Relinking did not make a difference.
C&R of the database that contains table0074 did not make a difference.
When I view the data with a select query I do not see anything obvious.
ste5an.. Thanks for the code and Yes, the table name was a typo. I got Gustav's message first so I tried his code.
I will let you know what I find..
I tried your code and got the message:
Cannot open database. It may not be a database that your application recognizes or the file may be corrupt.
After receiving the message I changed the table number to point to a different table (0073) and the code executed without issue. The table which worked is in the same database as the table which is throwing errors.
Relinking did not make a difference.
C&R of the database that contains table0074 did not make a difference.
When I view the data with a select query I do not see anything obvious.
ste5an.. Thanks for the code and Yes, the table name was a typo. I got Gustav's message first so I tried his code.
I will let you know what I find..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would try modifying your driver on your dev computer and see whether that makes a difference. If not, just switch the driver back again.
ASKER
Ste5an got it right. There is corruption in my back-end data file.
Now that I'm thinking about it, I did do a C&R on that DB about a month ago, and I got a strange error.
I had forgotten to set the affinity to one before doing the C&R.. as Microsoft has advised should always be done.
Everything seemed to work correctly...but perhaps this table became corrupt.
I recreated the table structure from a backup, and used an append query to copy the data, but that did not work.
Ultimately,, I imported an older version of the table into the BE and presto, the problem went away.
I wrote a routine to check every field of every record for non-printable characters, but none exists.
So that was the solution I used and I never found a way to identify what it was in the data that caused the problem.
Thanks to all for your help!
Now that I'm thinking about it, I did do a C&R on that DB about a month ago, and I got a strange error.
I had forgotten to set the affinity to one before doing the C&R.. as Microsoft has advised should always be done.
Everything seemed to work correctly...but perhaps this table became corrupt.
I recreated the table structure from a backup, and used an append query to copy the data, but that did not work.
Ultimately,, I imported an older version of the table into the BE and presto, the problem went away.
I wrote a routine to check every field of every record for non-printable characters, but none exists.
So that was the solution I used and I never found a way to identify what it was in the data that caused the problem.
Thanks to all for your help!
Let me know if that reveals anything. Then we could potentially write code to strip all existing unicode chars, or if memory serves me correct there is an access setting somewhere regarding unicode chars. (but lets cross that bridge once you have tested the field with ASCW)