Key Violation

[loki1049]

Using Access 2007.

I am using append to move query data from one database to another.

Database1 has a lone table (TABLE_A) with one data field Key_ID set up with unique keys and a couple fields filled with records.

I am sending the query results to the table (TABLE_B1) in the Database2. Ths table has a set structure of fields, but is empty, i.e. no records. It is linked to another table (TABLE_B2) by its primary key, which is set to autonumber. When I try and append my Query results from Database1 to Database2 I keep getting a key violation and don't know why??

Anyone willing to give an answer? When I send all of the fields but the Key field of my original Table_A, it works and loads the foreign tables key with the autonumbers you would expect.

 

[HiTechCoach]

Sounds like you are not setting the correct foreign key value from TABLE_B2 into TABLE_B1.

If TABLE_B1 is a child (many side) of TABLE_B2 (1 side) in Database2, then you need to create a TABLE_B2 record using a a recordset to get the primary key. This value will be used in the append query relate to the records in TABLE_B1 to TABLE_B2 in Database2.

That make sense?

TIP: Please use your real table names. It was really difficult to keep it straight with such generic names.

Hope I got the names is correct!

 

[loki1049]

EDIT: @HiTechCoach

Sorry, but the names are no better ha. The first table I load is the 1-side, then I load the many-side table. I'm pretty sure I have that part figured out already... but below you can see it works if I open and close Access for some stupid reason.

/EDIT



Ok, well I kinda figured this out, but still would like a way around from my method.

I started by doing 1 append query to the first table in the database2, and then doing a second query to a related table in database2. The only way I get it to work is if I close and reopen Access before running each sucessive append query..

I have to update 55 tables in as short of time as possibe, so if anyone knows a solution please let me know.

 

[HiTechCoach]

The only solution I know that works is to use VBA code, recordsets and a append query. This is what I was trying to describe in my first post.

 

[gemma-the-husky]

well there must be a sequence of inserts that wouldnt interfere with relational integrity

just fill the tables in that order.

have another table, with the tables included in the order in which you need to process them

 

[loki1049]

I can only load it one way.. there are two tables, one is a base and the other one depends on that base. I load the base table fine, then I try and load the next one and I can't unless I close access and open it again. Then when I load the second table it works.

I am populating a new online database via link tables with records from an older version of the database. There are plenty of tables to fill, but these are the two I am starting with because they are for the most part isolated.

 

[loki1049]

For anyone reading this and having the same problem, you can solve this issue by simply running your first append query, and then closing access, open access again, and run the next append query. Don't ask me why this works but it does.

The key violations that access was giving me were not in fact actual key violations, just access not working. I loaded a table for instance called "Employers" first, and then tried to load another table called "Employees" each of which was associated with a specific "Employer" and so long as all the data was correct, and I was loading the "Employer" table before the "Employee" table, I would always get key violations.. for all the records I tried to load. But if you open and close access between queries it works.

In my case I was using Access 2007, pulling data from a Sybase database using access queries, and then loading that data into an MS SQL Server database.

Regards,
-Jordan

 

[HiTechCoach]

For anyone reading this and having the same problem, you can solve this issue by simply running your first append query, and then closing access, open access again, and run the next append query. Don't ask me why this works but it does.

The key violations that access was giving me were not in fact actual key violations, just access not working. I loaded a table for instance called "Employers" first, and then tried to load another table called "Employees" each of which was associated with a specific "Employer" and so long as all the data was correct, and I was loading the "Employer" table before the "Employee" table, I would always get key violations.. for all the records I tried to load. But if you open and close access between queries it works.

In my case I was using Access 2007, pulling data from a Sybase database using access queries, and then loading that data into an MS SQL Server database.

Regards,
-Jordan

Jordan,

Glad to here you are making progress.

TIP: It really helps if you will have explained in more detail what you are doing, like the back ends were not JET/ACE databases. It is a general assumption that you are working only with Access/ET/ACE databases in this forum unless you specify things are different. I would have expected to see this post in the SQL Server forum because that is the database you are working with. Access is only the front end.

The reason that closing Access between queries works is probably due to timing issues. It appears that you are trying to append to a related table with the second query before the first query have completed.


FYI: There are ways to make this work without having to close Access between append queries.

When appending data into a database (Jet/ACE/SQL Servers) with RI enforced, I find it a lot of times it is better to use VBA code with recordsets to add the data in the same order that would be done as if a user was entering the data.