Trouble appending a table

[Gryphoune]

I have a table of almost 6K records that I have exported to Excel where we have made a bunch of updates to the data. I have tried appending the table using External Data from Excel with the spreadsheet being some of the fields to all of the fields but I keep getting a validation rule error message. Since I exported the data from Access shouldn't the fields in Excel be formatted the same?
I can't attach the database as it's current size is 1.5GB. Here are a few screenshots of my queries and table design.

This is one of the append queries



Table design for table to be updated



Any assistance would be greatly appreciated as this would be an enormous amount of time to update each record individually.

Thanks,



Export query to Excel for the data to updated



I tried importing the Excel file to a new table and then appending to the existing table and still get the error message.

[ranman256]

this some times happens when you import from excel. XL will guess at the format, if it sees text in the 1st few lines it assumes its text when it should be numbers. or vise versa.

1. you can try importing all fields, fail, then remove 1 field at a time until it works, to find the culprit.
2. what I do is, on some fields that SHOULD be text , I run a macro in xl to scan down the column and convert it to hard text with a single quote. '123

Sub Cvt2Txt()
 While ActiveCell.Value <> ""
     If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
     ActiveCell.Offset(1, 0).Select  'next row
 Wend
End Sub

[Gryphoune]

I ran the macro on all the text fields in the Excel file and then attempted another append and am still receiving the validation rule message. I imported the file to a new table and ran an append query to the Audit Notes table with the fields that need to be updated only to get the error again.

[John_G]

Take a look at the design of the table you are appending to. Do any of the fields in that table have Required = Yes? If any do, and they are not in the field list of the Insert... statement, that is the problem.

Try setting all the fields to Required = No in the Test Update table and see what happens.

[Gryphoune]

They were all set to No already and also in the tblAuditNotes.

[John_G]

Is an INSERT really what you want to do here? Or do you want to update existing records with updated data? Your original statement, with the join, suggests you want to use UPDATE, not INSERT.

Are there any fields in the table with a Validation Rule?

[Gryphoune]

I would like to update the Audit Notes table with corrected data in the Excel file. None of the fields in either table have validation rules.

[Gryphoune]

I finally got an update query to run with no error messages but it didn't actually update the data in the table. Here is the query SQL:

UPDATE [Test Update] INNER JOIN [AUDIT NOTES] ON ([Test Update].Res_Type = [AUDIT NOTES].Res_Type) AND ([Test Update].[ERROR CATEGORY] = [AUDIT NOTES].[ERROR CATEGORY]) AND ([Test Update].NOTE = [AUDIT NOTES].NOTE) AND ([Test Update].[Source code Desc] = [AUDIT NOTES].[Source code Desc]) AND ([Test Update].DeptResponsible = [AUDIT NOTES].DeptResponsible) AND ([Test Update].RESOLUTION = [AUDIT NOTES].RESOLUTION) AND ([Test Update].[LOGGED BY] = [AUDIT NOTES].[LOGGED BY]) AND ([Test Update].RESERVATION_NUM = [AUDIT NOTES].RESERVATION_NUM) SET [AUDIT NOTES].RESOLUTION = [Test Update].[RESOLUTION], [AUDIT NOTES].[LOGGED BY] = [Test Update].[LOGGED BY], [AUDIT NOTES].[NOTE] = [Test Update].[NOTE], [AUDIT NOTES].DeptResponsible = [Test Update].[DeptResponsible], [AUDIT NOTES].[Source code Desc] = [Test Update].[Source code Desc], [AUDIT NOTES].Res_Type = [Test Update].[Res_Type], [AUDIT NOTES].[ERROR CATEGORY] = [Test Update].[ERROR CATEGORY];

[ssanfu]

Just to be sure I understand:

You have a table "AUDIT NOTES" that you exported to Excel and made changes. You imported the changed Excel data to a table "Test Update". Now you want to update the records in "AUDIT NOTES" with the changed records in "Test Update".

Change your update query to a select query and execute it. I would bet 0 records would be returned.
Be aware that "Note" is a reserved word in Access and shouldn't be used for object names. Since you have a field named "Note", it must be delimited with brackets.

In the update query you have linked each field in "AUDIT NOTES" with the matching field in "Test Update". Since the data in "Test Update" is different than "AUDIT NOTES", no records should be returned.

When you exported the fields from "AUDIT NOTES" to Excel, did you include the PK field?
When you imported the changed data, did you include the PK field in the import and NOT let Access create a PK field?

If you did, I would try something like

UPDATE [Test Update] INNER JOIN [AUDIT NOTES] ON 
    ([Test Update].ID = [AUDIT NOTES].ID) 
SET 
[AUDIT NOTES].RESOLUTION = [Test Update].[RESOLUTION], 
[AUDIT NOTES].[LOGGED BY] = [Test Update].[LOGGED BY], 
[AUDIT NOTES].[NOTE] = [Test Update].[NOTE], 
[AUDIT NOTES].DeptResponsible = [Test Update].[DeptResponsible], 
[AUDIT NOTES].[Source code Desc] = [Test Update].[Source code Desc], 
[AUDIT NOTES].Res_Type = [Test Update].[Res_Type], 
[AUDIT NOTES].[ERROR CATEGORY] = [Test Update].[ERROR CATEGORY];

Linking the two tables on the PK field will ensure the correct record gets updated with the correct changed record.


My $0.02 .........

[Gryphoune]

I finally got it figured out.