Append data from external database

[JoeA2006]

I need to import data from a live table in one database to a table in another
database. The data I am importing is not indexed. I would like to be able to
import this data into my table that is indexed. My table is indenticle
structure except for the indexes.
I only need to refresh my table intermittently, so I delete all the records
from my table and append all the records including the most current records
from the live table to my table. I am using a link to the live table. When I
attempted the append I received an error "invalid argument." Is there a way
to handle this without having to copy the table into my database and adding
the indexes everytime? There is a lot of data in the table so it takes quite
a while.

 

[Dale Fye]

Joe,

Can you post your SQL, so we can see what it looks like? How did you create
the "working" table? Did you copy the structure? Is the "live" database
also Access, or is it SQL Server or some other database?

Have you considered only appending the new records that are not already in
your indexed table? If the old records cannot change in the "live" database,
then all you really need to do is append the new records (assuming that you
have a combination of one or more fields that define a primary key for the
table and that your "live" data table is linked to your "working" database).

The way I would handle this is to create an unmatched query and append the
unmatched records to your working table.

1. Add the "live" table to the query grid
2. Add the "working" table to the grid
3. Join them on the PK fields
4. Modify the properties of the joins to include all records from the
"live" table and only those that match from the "working" table.
5. Select all of the fields from the "live" table and add them to the grid
6. Drag one of the PK fields from the "working" table to the grid. Uncheck
the checkbox and set the Criteria = Null
7. run your query (this should give you all the "new" records)
8. Change the query to an append query and select your "working" table as
the destination.
9. save the query.

HTH
Dale