Importing data from text file to append to table

[KingKen]

I am trying to export some values from a table in my database to a text file then have these values imported and apprnded to another copy of the same database table the same table in another location

When I tried to Import the values I got an error stating that X record have been lost due to a key violation. I suspected that I was trying to put values to a auto number field and hence the error occured.

I then created a query that exported all other fields but the primayrkey autonumber field. To this action i also get an error stating that I cannot record changes because the value entered violates settings defined for the table or set.

I am working in access 2007

I do hope that there is a way to import what I Exported and append directly to a table as I want to be able to execuit the emport process with just one mouse click.

This post is connect to the project as describe in my previous post at Automating Data export and import in Access

[nico5038]

Just make sure that the sending department ID is part of your key and make sure that for every specified index this field is present to prevent dupes.

Nic;o)

[NeoPa]

Do you have any fields set in the destination table with any restrictions set? Cannot be Null for instance.

These should be at least as loosely set as the originating table. Ideally loosen them up fully and add back in any restrictions you want until it either works or you see which restriction is causing the problem.

[KingKen]

I want the data to be imported into the table with all of its structure intact. this is not a one off import it has to be done every month from about twenty different databases. i am thinking about creating a temporary table at the importation of the data then run an append query to add the records to the table.

This method just might work but it brings a lot of over heads. I have to some how remove the table after I finish appending the data to the main table... I am still thinking about how best to go about this.

Is this the right approach? would it work? is there a lighter idea floating around someware out there...? I love to heare from you please help!

[KingKen]

Not sure that I understand what Nico 5038 is saying. The ID field in the table is of type AutoNumber. I dont want to send it to the other table as I would have to allow duplicates in the primary key (I think this is what the first error gave). so I send the the file without the primary key field and hence the second error. I think NeoPa have good Idea of the proplem however I want the table to remain as is and still accept the imported data. Please dont stop sharing your ideas, there are helping me to think outside the box and I am better off with your help.
thenks a lot

[nico5038]

My proposal is to store the original (autonumber) ID and the code for the department "together" as the final key. This will allow you to trace the records back to the origin (department) and identify that record the next time by the (autonumber) ID.
The autonumber ID needs to be stored in a "normal" numeric field and will have duplicates as the different departments can have the same autonumber ID.

Getting the idea ?

Nic;o)

[TheSmileyCoder]

I think Nico Makes a very valid point there.

I then created a query that exported all other fields but the primayrkey autonumber field. To this action i also get an error stating that I cannot record changes because the value entered violates settings defined for the table or set.

Your getting an error when your trying to do the export, or when your importing the result of that query?

Exactly what errors are you getting? Error number please, and complete Error msg

What types of data do your table contain, besides the primary key.

[NeoPa]

I want the data to be imported into the table with all of its structure intact. this is not a one off import it has to be done every month from about twenty different databases. i am thinking about creating a temporary table at the importation of the data then run an append query to add the records to the table.

You have already hit on the idea I was thinking of moving on to. The suggestion to loosen up the restrictions was simply to identify where the problem was though. It wasn't intended as a long-term strategy.

The idea of an intermediary import table is a good one. It is probably better to create it once, then empty it before and after (belt & braces approach) use each time. This is the way i'd be inclined to go. That way, any errors in the data can be picked up after importing into the import table, and reported on within Access.

[KingKen]

Ok I have tried my new idea and still have problems appending the records. The attachment shows a picture of the error that I have been receiving. When I remove all the primary keys (there are three of them and they are set to "indexed(Al low duplicates)'.

I am thinking that the three keys together form the primary key and though they are set to allow duplication individually they do not allow duplication of the same combination.

Is this so?

[TheSmileyCoder]

A primary Key cannot contain duplicates, as far as I know. So yes, its quite likely that some combination of of keys sets a primary key.

I am not familiar with Access 2007, and if I recalll correctly, thats what you were working in. There should however still be a button in hte ribbon called "indexes". In 2003 its icon is a few lines of text on the left and some lightning on the right. Click it, and you can see an overview of the indexes in the table, and also which are set as primary key (or if a combination is set as primary key)

[NeoPa]

There seems to be some misunderstandin g here Ken. There can be only one Primary Key. Multiple indices, but only one of them can be primary. Indices may also be unique or allow duplicates. A Primary Key must be unique. Perhaps if you post (in text form please) a list of your indices for us to peruse we could help further.

You mention also you are continuing with your new idea. Considering the comments inbetween, about which you say nothing, it's unclear what you mean by this. Conversations (communication) is best managed by responding one way or another to all points raised. Without this it's very easy to get lost and misunderstand. This is to no-one's benefit.

[nico5038]

I am trying to export some values from a table in my database to a text file then have these values imported and apprnded to another copy of the same database table the same table in another location

I think the duplicates trouble comes from the multiple databases holding the same table (and autonumber). Combining these in a "master" won't work when there's no additional unique making field. Thus my post that the best way is to create on the "master" table a multiple field unique index consisting of the original autonumber PK combined with the Department field. Thus there's always a reference to the origin in the departments database table and the "master" table will be unique.

Creating a multiple field unique key is done by selecting both fields in the table and than pressing the key button.

Nic;o)