Need Help

[Diane]

OK, I have no more hairs, left, so its time to ask for help.

Seeminly a very simple task, is escaping me.

Here is what I have:

An Excel Table
An Access Data base with a table with the same fields as Excel table

What I want:

To have entries entered on the Excel table, be able to be auto updated to
the corresponding table in the Acess table. I can create a macro in access
to add the records, but every time it runs, it dupes the existing records
from the Excel table, onto the access table. How can I run the macro, or
whatever other way to accomplish this, have ONLY the new or changed records
from the Excel file, update to the Access TAble???

I hvae to believe this is possible, yet, its just not working the way I want
it to.

TIA
Diane

 

[Douglas J. Steele]

Can you link to the Excel database, rather than importing each time? In that
way, Access will use whatever's in Excel as its data.

File | Get External Data | Link Table..., rather than File | Get External
Data | Import

Another alternative is to import into a temporary table each time, then run
a query to determine what's different.

 

[Diane]

So, you are saying if I do:

File | Get External Data | Link Table...,

It will only pull new, or changed data?

I'll have to give it a try..

Thanks
Diane

 

[Diane]

OK tried that, but it overwrites the entire table.

Is there a way to check for changed records, only, and to be able to append
those, or add new without overwriting the entire table?

Thanks again
Diane

 

[Rolls]

No, if you link to the Excel table you get all values in the Excel table.
If you want to update existing Access table values and then append new Excel
records to the Access table you'll need two or more steps to fully agree
your Access table to the Excel transactions.

1) Find existing Excel rows containing updated values and update the Access
table.
2) Find new Excel rows and append to the Access table.

This assumes that it's ok if the Access table contains rows that don't exist
at all in the Excel table, and you want to leave them unchanged.

 

[Douglas J. Steele]

Actually, no, what I was suggesting is that you link to Excel so that you
don't need to make a separate copy of the data in Access.

When you create the linked table in Access, there should be no need for you
to continually update anymore: whenever you open Access, your linked table
will always be identical to what's in Excel.