Import data from Excel to Access

Nick123

Registered User.
Local time
Today, 05:28
Joined
May 11, 2007
Messages
11
I am facing an issue in importing data from excel sheet to MS Access table.

The issue is I am trying to import excel sheet that contains 100 records into access table. Both the excel sheet and access table have same field names. Now the issue is when i use the MS Access | File | Get External data | import .... it will import the data from excel sheet to ms access table. It shows all the 100 records at the beginning in Access table. MS Access table already contains 5000 records. What I need is that those 100 records must be appended into the access table.

So the only problem i am facing is the position of records I need the records at the end of the records in MS Access table not in the beginning....


Pls help.
 
What is the PRIMARY KEY in your table.
Access put in order the records depending a primary key.
 
I dont have any primary key in the table.
 
I'm going to quote The_Doc_Man from this post
http://www.access-programmers.co.uk/forums/showthread.php?t=125417
The_Doc_Man said:
ALL databases are based on set theory. In the theoretical case, when you do something to the members of the set, you do it to all of them - with no statement of ordering. That is, in theory, everything that happens to set members MIGHT AS WELL happen in parallel without regard to order because set theory doesn't include the word "ordered." It is an artifact of presentation.

A query includes the syntax for "ORDER BY" to assert a particular order. A table, which is a SET (hint, hint) of records, has no true order. If you open the table and click on one of the fields to sort it, you sort the PRESENTATION of the table ON YOUR SCREEN. It is like you created a virtual query that is the equivalent of SELECT * FROM MYTABLE ORDER BY {whatever was last clicked in the field list}; If you save that, you DON'T SAVE THE TABLE. You save the presentation information.

Never ever presume anything to do with order when working directly with tables. That order doesn't truly exist. (Particularly since you just admitted there was no prime key.)

OK, now, here's how I let you off the hook. There is no order to a table - with one exception. If you declare a proper prime key (i.e. unique) and immediately compact the database, all tables having prime keys are re-sorted into the natural order of their prime keys. HOWEVER... even one little insertion can break up this ordering again. Physically, the new record will probably just get tacked onto the logical "tail end" of the table regardless of the order.
 
nick

what bob's posting means is that the records are being added to the table (presumably!). The order in which you choose to display them is up to you. Therefore if you have an order that places the new records at the bottom, thats where you will see them - so maybe you need a field called timeinserted to store this information

where the keys become important is that your query/import MAY NOT insert all the records into the table if some already exist, and your keys prohobot duplicates

i generally count records before and after to check inserts because of this
 
The order in which you choose to display them is up to you. Therefore if you have an order that places the new records at the bottom, thats where you will see them -

Almost, but not exactly. I mean that records are not stored by Access in any particular order. Actually, even if you do have a date/time stamp, the records in the table itself are not necessarily stored by Access in that order. You can, if you have a field like that, go in and click the sort button and they will appear to be sorted in that order, but only because Access generates a query (behind the scenes) to display them for you. And, in reality the table itself still doesn't store them in any order; it's just a view of the table that's been created.

What Doc was saying is that you can designate a primary key and that will become the sort order for the view of the table, without a manual sort being done.
 

Users who are viewing this thread

Back
Top Bottom