# Solved: Import data into existing access table



## snorkytheweasel (May 3, 2006)

I created an access 2003 db & populated a table using 'get external data' from excel 2003. Now I need to import more records from excel to that same table. I tried the same process, but it insisted on creating a new table. Repeat 2x, get 2 more new tables. Even though when using 'get external data' there is an option to import 'into an existing table,' the option is greyed out.

I need to get the 'new' excel data into the already-populated table.

Speaking in human terms (not Microsoftese), how to I either

merge the data from newly-created,newly-populated, and unwanted table 2 into the existing table 1
or

import from excel into the existing table

I realize that I could export from access back into excel, merge the data there, and import into a new table, but surely there's a better way. Or not, since we're talking about one of the Big Flopper's biggest flops (Access).

Of course, the best solution would be to import it into an SQL db, but that's not an option in this case (office politics).

[note to self: CHILL]


----------



## OBP (Mar 8, 2005)

You may consider Access a Flop, but there are still plenty of posters on this and other Forums getting plenty of useful mileage out of it.
The biggest problem with Access is that users can't be bothered to learn how to get the most out of it as it is much more complicated than Excel.
I never import directly in to a Table, I always Import in to a temporary table and then use a simple Append Query to Add the data to the Correct table(s) and then run an Update query to update any changed records. Once the data is in the Main Table I then run a deleteQuery on the temorary Table, or delete it altogether. 
This can all be done at the Click of a Command button.


----------



## snorkytheweasel (May 3, 2006)

I appreciate your help, but.....

"A simple Append query" -- tried it. I joined fields in both tables, but got hung up on "criteria."

I went back to RTFM. Such as it is.

The help system gives no clue about what are valid criteria. Since I wanted all records, I tried *. Bad idea, but no help as to what I did wrong. Everything I tried and everything I looked up in the help (ha-ha-ha) system assumed that I know all about access terminology, that all of the icons were visible (which they aren't, but I figured out that one), all of the icons' names are obvious (which they are not) -- that I was inside the head of the help (ha-ha-ha) article writer.

An hour-and -a half later I have nothing accomplished.

If this were SQL I'd say

```
INSERT into main (lname,fname,m,title,descrip,bldg,room,phone)
SELECT lname,fname,m,title,descrip,bldg,room,phone
FROM main;
```
In less than a second the data would be merged and I'd be ready to go do some real work.

I know that somewhere there is a capability for creating SQL queries, but the help (ha-ha-ha) system never says anything clearly: 

Click 'a' (and makes sure thatI can tell what 'a' means)
Choose 'b' if .... or choose 'c' if... and makes it clead what are b,c, and the if conditions
in the large box, enter your SQL statement; for help, see 'nnnn'


----------



## OBP (Mar 8, 2005)

Open a New Select query in design view and switch to SQL view using Main Menu>View>SQL
The help is nearly good when you have been using Access for a few years 

If you post a zipped copy of the database with the 2 tables and the form in it I should be able to create the query for you in about 5 minutes. But I have to go soon as the wife will be kicking me off the computer.


----------



## snorkytheweasel (May 3, 2006)

Thanks.

There used to be a guy working here - he used Access a lot. He's gone now (note: he was acquitted due to impaired sanity).

I rummaged through his old books, and found a book about Access.

The book didn't directly address the question, but by following clues, I figured out a way to do what I need:


create query in design view.
select "Append query" from main menu
list table 2 fields horizontally in the query from 
define table 1 field to which eacn table 2 field is to be appended
get religion
click run
Woo-hoo! It worked!

No doubt there's a better way, and once I recover from this episode, I'll follow your advice.


----------



## OBP (Mar 8, 2005)

If it works it is good, well done and try to persevere, between Queries which are mainly SQL based and VBA Access can be a very powerful tool.


----------

