troubleshooting Question

Error running append query

Avatar of bfuchs
bfuchsFlag for United States of America asked on
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL
29 Comments1 Solution136 ViewsLast Modified:
Hi Experts,

I have an Access application that is in the process of being converted to SQL (BE).

However encountering an issue as follows.
When trying to run an append query I get the attached error.

The following is the SQL we are trying to execute.
INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, VisitDateInt, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, Int([V_Visit_Note_Export].[Visit_Date]) AS Expr1, V_Visit_Note_Export.Visit_Date, V_Visit_Note_Export.Date_Of_Birth, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, V_Visit_Note_Export.Date_Signed, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) OR (((V_Visit_Note_Export.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) AND ((V_Visit_Note_Export.Deleted)=True));

Skilled_Nursing_Visit_Note is the SQL table
V_Visit_Note_Export is a linked Access table.

FYI- We tried adding the following before executing but did not help.
DBEngine.SetOption dbMaxLocksPerFile, 200000 ' set max locks per file



Thanks
Untitled.png
Untitled1.png
ASKER CERTIFIED SOLUTION
Mark Edwards
Chief Technology Officer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 29 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 29 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros