Originally Posted by
Peter M
I have two databases (actually a lot more, but for simplicity) dbParent and dbChild.
Inside dbChild is an empty table (tbMale) with 74 columns that match the columns in my CSV file.
In dbParent the CSV and tbMale are linked.
When I try to append data from the CSV file (filtered for Males) I am getting the error message "You cannot record your changes because a value you entered violates the settings defined for this table or list." There are no data validation errors. If I open dbChild and directly append the data using the exact same SQL it works. There may be data errors in the data but there are zero data-type errors and no validation constraints on tbMale.
I was confused on how you filtered the CSV file - then I realized you created a link to the CSV file and ran a filtered append query to get the data into the table "tbMale".
My approach was different. I had to import 3 different CSV files that ranged from 0 records to 36,000.
I had buttons on different forms but the method was the same for the 3 different files.
I'll describe the process for one tables/CSV file.
The tables/CSV file pair had the same field order.
I used the FSO file picker code to select the CSV file to import.
I checked the CSV header (the first row) to a VBA constant to ensure it was the correct CSV file.
If the headers matched, I used the command "DoCmd.TransferText" to import the CSV file data to a table, using an ImportSpec I created.
The syntax for the import code is
Code:
expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
Mine looked like
Code:
DoCmd.TransferText acImportDelim, "EmpImportSpecification", "tblAddsEdits", strFileName, True
The import is very qiuck with 36,000 records.
If you wanted only males, you then could run a delete query on tbMale" to delete all records except males.
Something like
Code:
DELETE tblMale.*
FROM tblMale
WHERE tblMale.gender <> "Male";
------------------------------------------------------------
Troubleshooting your import/append
I would make a copy of the 3 files: CSV, dbParent and dbChild (for safety).
Using the copies:
Make another copy of the CSV file. Edit the file and delete all but 10 records.
Create a link to the new (smaller) CSV file and try to append the records to the table "tbMale".
If that works with no errors, create another CSV copy, but delete all but 100 records.
Append the 100 records to see if you get the error message.