import data into database ms access

I thought I had this figured out but was mistaken.  

The DB2 table will be adding records regularly and I want to import those new records into my Access database every morning.  The problem is, when I run the update it creates a new table instead of appending them to the table that has already been imported. Is there a way to create a table that updates regularly?
mwolfe83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
<The problem is, when I run the update it creates a new table instead of appending them to the table that has already been imported.>

your codes is probably directing the import to a new table.

post the whole codes for your import process,  or better upload a copy of your db..
0
mwolfe83Author Commented:
Capricorn1, You sound as if you are very good at this.  

I am very green when it comes to Access and I have just been using the "external Data Import feature."  I click More, Odbc, then pick the top option.  If there is a way to see the code, I would love to know it.


Copy-of-David-French.accdb
0
Rey Obrero (Capricorn1)Commented:
can't look at this yet.. don't have A2007 at the moment
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mwolfe83Author Commented:
For you capricorn1, I will convert it :)
Copy-of-David-French.mdb
0
Rey Obrero (Capricorn1)Commented:
there is nothing in your db that tells me that the imported records be  placed in a new table,

when you import the records specify that you want to import the records in an existing table.

select the table you wished to appned the data to.
0
mwolfe83Author Commented:
it never gives me that option.

I go to file, get external data, import, select "ODBC database" under Files of type, Machine Data Source, DHSCLIN, (username and password), select the table to import, and click Okay.

If I do it more than once...it just copies a second database with a 1 at the end.

The only other external data option I have is to link the table.  Linking the table will allow the access database to write back to the original database. I really don't want to take that risk.  
0
Rey Obrero (Capricorn1)Commented:
importing a table is normally done the way you've said it..
if you are holding your record in say TableX, (and you said you don't want to linked tables).
Import the table, run an append query appending the records from imported table to tableX
delete imported table..
0
mwolfe83Author Commented:
I'm on board with that.

How do you think I could do that with and make it append only new records?
0
Rey Obrero (Capricorn1)Commented:
what is the name of the table you want to keep with old and appended with new records.
0
mwolfe83Author Commented:
Lets use the MPI table. I think that dccdb_MPI will be the import table and I will create a new table identical to it called cch_MPI that I will append to.
0
Rey Obrero (Capricorn1)Commented:
this will be your append query

insert into cch_MPI
select dccdb_MPI.*
from dccdb_MPI;


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mwolfe83Author Commented:
Thank you for all of your assistance.  That was so easy that I feel silly for asking.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.