Error running append query

When I've experienced this it was when I was running complex queries over large recordsets.  

The other thing to note is that this error seems to occur more and more frequently in newer versions of Access (older version would run queries without issue, yet in a newer version you get the error, so obviously there are memory issues with the newer versions which seem to be getting worse over time).

Anyways, what I've done to get around the problem is to run such queries over batches of records.  So instead of processing a whole table in one shot, I'll process groups of records until the whole recordset is processed.   Sometimes a C&R becomes required in between batches.

@Daniel,

Okay I changed to the following

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 top 1000 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));

Select allOpen in new window

Added SELECT top 1000, now the error message is gone, however something else happening, really strange...
This query supposed to import all (at least 1000 with this modification) new records according to SNV_ID, however I see every time I run that, only 5 new records are being added to the table, cant make heads or tails of this...

Thanks,
Ben

Joining attached and local tables is problematic.  The fastest way around that is to first run a make-table query to extract the joining data into a local table.  Then change the FROM clause to reference the new local table you just created.

I agree with aikimark, that using a maketable query as an intermediate step and then using that in the import may improve processing.

Then, what I would do is look closely at your where clause to determine what each segment of the where clause is doing.  Given the extra () that Access adds to queries, I find that it is easier for me to examine each of the separate elements of the WHERE clause in the query design grid, where I can add one row of the criteria at a time and see what records are being returned.  My guess is that there is some conflict between the two segments of the query where you in one segment you use

V_Visit_Note_Export.Status)<>"draft"

and in another segment you use:

V_Visit_Note_Export.Status ="draft"

I've had the same experience as Daniel.  When I ran complex queries using a join between a linked SQL Server table and a local (in the Access db) Access table, the query ran quick and easy.  But, when I moved the local table to SQL Server and linked it and ran the same query, it took hours!  No idea why.  I solved the issue by using T-SQL in a pass-thru query to run the process in SQL Server, which took a fraction of a second.

I wonder if any of the experts has a "white paper" on mixing/matching linked SQL Server tables with linked/local Access tables and seeing what the performance results are, as well as any design tips/issues.  Sounds like a good subject for our research here at VBAOS.

Hi Experts,

Joining attached and local tables is problematic.

I see there is some misunderstanding here...

Just to clarify, I have 2 databases and one FE application.
One database is an Access file and the other is a SQL DB.

Then I have the Access FE application trying to move data from the Access DB to the SQL DB.
Not sure how will a make table query help here...

FYI- When I switch to datasheet view, I can see all those records which are to be inserted into the SQL table, so there is definitely not a problem with the joining, something must be stuck on the SQL site while the insert is taking place.

My guess is that there is some conflict between the two segments of the query where you in one segment you use

V_Visit_Note_Export.Status)<>"draft"

and in another segment you use:

V_Visit_Note_Export.Status ="draft"

No, its not a conflict, depending on the condition, if record is not marked as deleted then I want exclude 'draft' records (as they are not finalized yet), however once its marked as deleted, then I want bring them over.

I solved the issue by using T-SQL in a pass-thru query to run the process in SQL Server, which took a fraction of a second.

I cannot use pass-through query to import data from Access files.

Does anybody have a sensible explanation why are only 5 records being added each time that query is being executed?

Any other suggestion on site?

Thanks,
Ben

Looking at your query, "TOP 1000" means select the first 1000 records based on how the records are sorted.  You will always get the same records if you don't remove any records that are appended to the SQL Server table.  If the appended records are NOT getting filtered out, then something in your criteria is wrong.  I see you are using a right join properly, but it is not working (so you say) to filter out the appended records.  Are you getting different ID values on the SNV_ID field, which is your linked field?

Next, is there ANY active "On Error Resume Next" condition on your query.  When you run a query and any of the selected records fail to append, then you SHOULD get a query failure indication.

Try removing any SetWarnings False/True and putting an "On Error GoTo 0" right before you run the query and see if you get an error that breaks on the query execution line.  If you do, what is the error message?  

If you are running a Jet or ACE query appending linked Access table data to a linked SQL Server table, make sure you aren't trying to pump any data into a SQL Server table field that is an "Identity" field (the equivalent of an Access autonumber field).  Unlike an Access table, a SQL Server table won't let you do that, and should generate an error message.

Hi Mark,

You will always get the same records if you don't remove any records that are appended to the SQL Server table.  If the appended records are NOT getting filtered out, then something in your criteria is wrong.

If you look at the criteria, I am looking for none existing records according to Unique ID (((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)).

Since we're inserting V_Visit_Note_Export.SNV_ID into Skilled_Nursing_Visit_Note.SNV_ID, this should cause every time the total records being added to be less with a 1000.
See attached.

Next, is there ANY active "On Error Resume Next" condition on your query.

I am running this now from the database container, no code.

Not using auto number field here.

We have to think why are some records being entered (5! in my case), if there would be an auto number involved, there would be an error message and none of those records would be added.

Thanks,
Ben
Untitled.png

This looks like the most bizarre thing I encountered for a long time...

I started with table having 338 records.

changed query to select top 100 and each time added 12, so after running first time I got 350, then 362 and later 374.
See attached messages that popped up when appending those records.

Then I changed to Top 1000, started with 374, got 427, then 480, then 533.
After that, every time I run, it only appends two records, 537, 539...

Can someone help figure this out!!!

Thanks,
Ben
Untitled.png
Untitled1.png

Ben

Please read my earlier comment.

@Aikimark,
As mentioned earlier, I did read it but was waiting for some explanation why this should rather work.
Anyway I tried that now, and the make table query works.
However the append query (from that temp table) does not.
If I run for all records, I get "not enough resources..." as in OP.
If I change to top 1000, I get those messages attached, but none of those records are getting thru...
Thanks,
Ben
Untitled.png
Untitled1.png

Ben:  I see in your latest attached image (.png) file, that your linked "V_Visit_Note_Export" table does NOT seem to have a primary key (on SVD_ID).  Try putting a primary key on that table, relink, and try again.  I know it seems like a long shot, but stranger things have happened... or we can say "nah, that can't be it", and just keep guessing....  Does your "local" table have a primary key on it?

I've seen too many cases here in EE where "nah, that can't be it" ended up being the problem.  They were so sure that something couldn't be the problem that they would rather keep guessing than test.

After thinking about it while listening to music on the couch, if your query is showing you all 1000 records (with an empty SQL Server (SS) table) when you run it in "SELECT" mode, but it doesn't append them when you run it in normal mode (with the red exclamation button on the tool bar), then we need to start looking at just getting the simplest thing to work.

Try deleting ALL of the records in the target table (so we can start fresh), and using "TOP 1" and an "ORDER BY [SVC_ID]" clause in your query (SQL Server hates using the "TOP" predicate without an ORDER BY).  Let's see if we can get the first record to append to the SS table.  If it doesn't, then we need to figure out why before bothering to go any further.  If it does, then let's try another.  Then, start increasing the number of "TOP" records until you hit a snag.  If the query starts working as desired, you can take off the "TOP" predicate and just try and append the rest of the records.

@Mark,

Tried your first suggestion about setting this up as PK, and same results.
However I realized something that may shed some light here...

When looking at the ID column which is set in SQL as auto increment by 1, I realized that besides for the 5 records a actually being created after each run of the query, the number gets incremented each time by 1000 ! (see attached).
So that shows those records are actually getting created in SQL, and somehow they're getting deleted...

What are your thoughts?

Thanks,
Ben
Capture.PNG

Ben:  Your image does not show the column headings or explain what the viewer is looking at, except the "(New)" at the bottom of the first column indicates an auto-increment field.  Is that the SQL Server table (is that what you meant by "SQL") or the Access table?  Is it the "SNV_ID" field?

Check the design of your sql server table and see if the field property "Identity Specification" is set to "Yes".  If the table was made from importing the Access table, the import would have made any Access autonumber field an "identity" column (similar to an Access autonumber field) with a data type of "int".  It sounds like you are trying to get the identity field in the SQL Server table to increment to the same numbers in your autonumber field in the Access table.

SQL SERVER WILL NOT LET YOU APPEND NUMBERS TO AN IDENTITY COLUMN!!!!  It doesn't work like an Access Autonumber data type except for incrementing when a new record is appended -that's where the similarities end.  In order to append the numbers you want to an identity column, you have to set a bunch of switches that requires sysadmin rights, and you have to set a switch on the table itself to allow appended values into the indentity field.  If your ID field is identity and the values are being changed from what's in your Access records, then the right join on your primary key fails to properly filter as the values don't match.

Also, you can't do it (append autonumber into indentity) through an Access front-end -  that is you can't run the records from a linked Access table into a linked SQL Server table as the linked SS table does not recognize the switch that allows the identity field to accept autonumber data.  I've got a utility app I built in Access to move Access table data to SQL Server.  There are 4 setting you have to set to be able to use the "OPENDATASOURCE()" function in SS to copy Access table records straight into SQL Server, and then a switch on the SQL Server table to get autonumber values into the Identity column.  Too complex to put here.  It all uses T-SQL run in a pass-thru query.

What do you want to do?

SQL Server Management Studio (SSMS) has import wizards that will import all the fields for you including putting autonumber into an Identity column, but you MUST have SysAdmin rights, or SS won't let you set that switch.

It took me several days to build my app from scratch - all the research to get all the pieces together and working as a utility app.

Hi Mark,

The column ID which is showing new on the image is something just for SQL, although SNV_ID is unique on the source table (Access, originally Caspio), but some experts here on EE suggested I create that on SQL so I listen...

As you can see from the append query OP, I'm not trying to insert anything into that ID auto increment column, leaving it for SQL to handle.

Check the design of your sql server table and see if the field property "Identity Specification" is set to "Yes".  

Yes, it is.

SQL Server Management Studio (SSMS) has import wizards

This is something we need to provide users an auto way to accomplish, as users are constantly downloading data from a web app, which comes as an Access file (there are other formats avail, however had other issues with those formats, so preferred using Access), and we need these data to get into our SQL table.

FYI- All these described is currently functioning with our Access BE, but we are trying now to upgrade our Access DB to SQL server BE.

I've got a utility app I built in Access to move Access table data to SQL Server.  There are 4 setting you have to set to be able to use the "OPENDATASOURCE()" function in SS to copy Access table records straight into SQL Server

Perhaps this is something that may help...would you share that with us? (no need for the auto number insert option for now).

Too complex to put here.

We can start here, and then continue in another thread...



Thanks,
Ben

Also, as far as your criteria is concerned, check the values in V_Visit_Note_Export.Status.  The criteria <>"draft" does NOT include nulls.  To include nulls, use NZ(V_Visit_Note_Export.Status,"")<>"draft", which replaces null with an empty string.

Also, check V_Visit_Note_Export.Deleted to make SURE each record is either True or False (-1 or 0).

The "why" doesn't matter at this point.  You have a work-around.  Please use it.

You have a work-around.  Please use it.

Not sure which work around are you referring to, please clarify...(as mentioned, the make table query is not working).

@Mark,
When I try single record it works, tried more than once, see attached.
So there is no way I can pinpoint which record is the culprit.

I can get you the create table script of the SQL (target table), however the definition of the Access table is only view-able thru an image (do you have other option?)

P.S. There is nothing wrong with the logic of the SQL statement, as it currently works perfectly fine.

Thanks,
Ben
Untitled.png

If you have some records that are appending, it sounds like issues with the data in some records.  If that's the case, then it is really hard to tell and troubleshoot without the actual records.  I'm sure someone who knows what to look for could probably find the problem quickly if they had access to the data and the tables.

If a record in a set that you are appending DOES NOT make it into the SS table, does it make it if you append it separately?  It could be a problem where records append until hitting a problem record, then failing.  The answer is going to be in the details....

Here's an idea:  put an "ORDER BY" clause in your query so you will know what order the records are being appended, and see what records are making it into the SS table and which are not (out of a small recordset that you try to append).

See if you are hitting a certain record and then append fails, or if only certain records are failing while others append successfully.  Does the append fail for ALL records after a particular record fails to append, or do others following it append while that particular record does not... that will help identify the issue.

Tried already with the order by, but didnt got any further, as it keeps adding some records and not necessary those on top of the list.
when I tried adding manually those currently on top of the list, it works.
At this point, looks like when the attached message pops up, then no (or almost no) records are being added.
I will try next to make sure data types are defined the same in both tables.
Will keep you posted.
Thanks,
Ben
Untitled.png

Ben:  Just curious by what you mean by "adding manually".  Do you mean copy/paste the record from the Access table to the SQL Server table?  That works while query append does not for certain records?

If copy/paste "fixes" the record data so SQL Server will accept it, but appending the record with a query does not, then you've got a situation where someone is going to have to look over your should at what's happening.  I've seen situations where copy/paste does "fix" the data, and it was always a situation with funky ASCII characters in the data.

Just curious by what you mean by "adding manually".

Sorry for misclarification, I meant when changing the query to look for one particular record only, as shown on attached.
Thanks,
Ben
Untitled.png

Interesting... the record will append when appended by itself, but not when in a group of records... weird....

The problem was with one field's data type.
In Access the field was short text while in SQL its DateTime
If I omit that field, it works.
Thanks to all participants!

AHA!  Table structure matching is one of the FIRST things you check, even before using it.  By the time you get to having to ask for help, everyone else thinks you've already "done the obvious", so we don't think to risk insulting the author and asking them to "back-track" and check things like this.

Glad you got it working.... now go forth and sin no more.... ;-)