By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 2,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

Importing data from text file to append to table

P: 68
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
Jan 25 '10 #1
Share this Question
Share on Google+
11 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 25 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 26 '10 #3

P: 68
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!
Jan 26 '10 #4

P: 68
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
Jan 26 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 26 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 26 '10 #7

NeoPa
Expert Mod 15k+
P: 31,494
@KingKen
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.
Jan 27 '10 #8

P: 68
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(Allow 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?
Attached Images
File Type: jpg Error.jpg (13.4 KB, 249 views)
Jan 28 '10 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
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)
Jan 28 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
There seems to be some misunderstanding 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.
Jan 29 '10 #11

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 30 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.