Results 1 to 8 of 8
  1. #1
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67

    Cannot record your changes because a value you entered violates the settings defined for this table


    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.

    Any thoughts on what is going wrong? I would really like to do this from within dbParent due to the volume of data tables and repetitive nature of this task (it happens almost monthly). The resulting dbChild is about 750 MB so I am within the 2 Gb limit but possibly hitting during the processing? dbParent is tiny at about 15 Mb.

    Thank you in advance,
    Peter

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are describing HOW you have set things up to accomplish something (the WHAT). But we don't know WHAT you are trying to do in simple, plain English. 74 columns is a lot for an Access table--but it seems to be some sort of temporary/holding area from which to extract specific data for appending to normalized tables. I say "seems" because it is just a guess at this time.

    An analysis of your "business" and your proposed database design may provide more insight on the issue.

    You might get focused responses if you provide more info and context (even a few examples).

    Why multiple database--just curious.

    The error message you provided
    "You cannot record your changes because a value you entered violates the settings defined for this table or list."
    would have to be reviewed along with your table design and associated properties.

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    The 74 columns are mentioned to stress that the CSV is coming from a standardized Oracle db and the data types are valid. I am working with a transactional database that is relatively large (by Access standards). Out of this database are "small" extracts which are passed to a third party for in-depth analysis. The small extract happens to be 74 columns and 1.5 million records (plus other reference tables but those aren't an issue).
    There are roughly 20 fields that are always populated and 50 that explain why the data in the core 20 are the way they are. My responsibility is to verify that the CSV data extract is 100% accurate in every field in every record. This involves making sure that my Oracle db queries perfectly match my CSV queries. Some transactions have unusual values - but these are explained by data in the other fields. I have macros, queries, and sub-routines that check the data extracts for these exceptions and help me explain the transaction result. I have automated the process to the point that it runs in the background and populates a Log Table showing which I then review later for discrepancies. In some very rare cases the result is truly unexpected and not easily explainable. By having the data in indexed Access tables it makes it much easier for me to further analyze the results - all within the constraint of the CSV extract.
    It is my responsibility to:
    a) find unusual and unexplainable transactions before the 3rd party does
    b) explain how these results happened
    c) if it is problem in the transaction calculation consider ways to solve it

    And this data check process happens roughly once a month. The first step, which is the most rushed, is to pass the CSV file to the 3rd party. This almost always happens before I can complete my thorough analysis for unusual events.
    If I run my analysis against the CSV tables, they will complete but are very slow. This is why I want to import the data into Access databases so I can index various fields.
    I really should be using some other tool but Access is what I have, and also what I know best. I like my front end, my summary tables, my VBA scripts are readable (to me), and at the end of this process I have very quick access to all of the data that was sent to the 3rd party (the CSV files) going back a couple years. (If I can automate the load I will go back in time 5 years.) This is a core benefit as they often come back to us months later to explain results from a prior CSV extract.
    I have tried other methods but run into problems with those approaches as well. (See my post on XML import scripts going sideways when the columns get auto-ordered and append into the wrong fields; I really don't know XML so I'm not putting too much effort into resolving that problem - instead I'm trying to automate the CSV link-append process; it is slower than the XML import but still faster than me doing it myself.)

    There isn't a database design per se; the whole exercise is a data validation exercise to answer the question "Is this CSV extract a perfect extract of the data in the transactional database?"

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Who designed the extract from Oracle? Were there options?


    If it is a straight dump from a table or a view in Oracle, my basic questions are:
    -why would anyone think the extract is not "correct"
    -why deal with Access, why not dump directly from Oracle to csv

    It's been several years since I worked with Oracle. If I needed data from Oracle to use with Access or process it with other data, we used ODBC links. However, there were times where we needed a few Oracle tables from production for offline/local processing and analysis--for that we used an Oracle routine (forget if it was sqlPlus or similar) --quicker to get large volumes of data.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried to import the CSV file as a local table in dbParent and base your append on that?
    Cheers,
    Vlad

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Peter M View Post
    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.

  7. #7
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    (I think) I have finally resolved my problem so here is an update in case anyone else (myself included) needs to know how to do this.

    The goal is to automate the regular import of large CSV files into a structured Access db with indexed fields to expedite data validation. To avoid the 2 Gb limit, each data file is stored as its own database. A Front End that contains queries and scripts for importing and validating the data is linked to each Access Database. (Using this approach has enabled me to link ~50 Gb to a single front end.)
    When importing the data manually and directly to the database there are no problems; but this is tedious and not automated as each time requires either copying and editing XML scripts or setting the field importspecs, etc. (Plus there are a few other tweaks like combining some small files into single files; a host of reasons for why I want to automate the process.)
    Unfortunately when importing via links (importing the text file to the linked Access Database file) the system error "3349 - You cannot record your changes because a value you entered violates the settings defined for this table or list" posts. Despite the error message, the issue seems to be the data volume. I have the data structured described above but summarized here:


    A DestinationDatabase with a DEST_TABLE that has all the appropriate fields indexed.
    A FrontEndDatabase with
    - A link to the text file (CSV_LINK), made with an XML script
    - A link to the DestinationDatabase DEST_TABLE, as it is an access db it does not need any specifications
    - An Append Query that Appends CSV_LINK to DEST_TABLE


    If I run this query, an error posts that implies I'm violating unique index constraints, datatype constraints, or some variation of that. I think the real issue is one of datasize (but I do not know what the data size limit is).
    To get around it, I add a filter that limits the size of the append query. In my case I have a month's worth of data and can filter by date.
    Append CSV_LINK to DEST_TABLE where [filterDate is first week of month]
    Append CSV_LINK to DEST_TABLE where [filterDate is second week of month]
    Append CSV_LINK to DEST_TABLE where [filterDate is third week of month]
    Append CSV_LINK to DEST_TABLE where [filterDate is fourth and fifth weeks of month]

    Now when the error pops, it actually is due to a key violation that needs further investigation!

  8. #8
    RogerD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2014
    Location
    Ky
    Posts
    27
    I am receiving the same error "You cannot record your changes because a value you entered violates the settings defined in this table or list". I am importing an Excel fie into a table in the db. We have used this same table, excel file for years with no issue. The only thing I can see that has changed is the Month/Year is now out to Mar-21. the file has 6769 rows and 6 columns. I have reduced the rows from 6769 to 3237, reformatted the data to be sure it is correct, but nothing is working.

    I figured it out so this will be for someone else who has the same problem. I added 4 rows of data. The 4 rows of data had numbers as text. When I converted the text to numbers, it resolved my issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-16-2017, 09:09 AM
  2. Resetting visiblity settings on new record
    By mrt1069 in forum Forms
    Replies: 3
    Last Post: 06-21-2016, 08:10 AM
  3. Replies: 3
    Last Post: 02-27-2014, 11:05 AM
  4. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  5. Replies: 6
    Last Post: 05-14-2013, 09:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums