How do I update a table from another MDB ( of same design)

[GOPALAKRISHNAN]

I want to update records in DeliveryTicket table in Sales.MDB from another
database SalesNew.MDB which is the copy of Sales.MDB but having more latest
records. Can anyone let me know how to do that?

 

[Evi]

Go to File, Import and import the table that contains the new data

It will be given the same name as your original table but will have a 1 on
the end.
Set up an Append Query (having done this once, you can use the same append.
Append the data from the imported table to the archive one. Delete the
appended records from your original table.

A couple of things to note. You can't append to an Autonumber primary key
field but if you decide to have a Number field in your archive table and
append an Autonuimber field to that, you may not be able to have it as a
primary key field because if you Compact your New Sales mdb after deleting
the data, the Autonumber will reset and you will have duplicate values. One
way of preventing this, would be to enter a record into new
sales -anything - compact the new sales database, and then deleting your
dummy record.
You will need some way of ensuring that you don't append the same data more
than once.
Evi

 

[John Spencer]

Evi,

As far as I know, compacting a table that has ANY records in it will not
affect the autonumber. Compacting a table that has NO records will reset the
autonumber to zero.

Is your experience different than this?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Klatuu]

I find your description confusing.
Which mdb has the table you want to update?
From which mdb do you want to do the update?
Are you updating existing records or adding new records or both?

In any case, you can link to multiple mdb files from your application mdb,
so really, it should just a matter of creating a link to the table you want
to update and running a query to perform the update.

 

[Evi]

I agree John, but I was concerned that the writer wanted to empty his New
Record table when he archived it. if he is just taking out the oldest data,
then the problem would not exist.
Evi

 

[GOPALAKRISHNAN]

Hi Klatuu,
1. Sales.mdb which has a table 'Delivery Ticket' table is the one I want to
update.
I want to add records only from SalesNew.Mdb to Sales.Mdb

2. I want the new records in SaleNew.mdb to be added in Sales.mdb
( Fox example , I want to add records having ticket date >= 01-Apr-2008
and
<= 30-Apr-2008.)

I hope it is clear now.

Gopal

 

[GOPALAKRISHNAN]

Hi Evi,
I have successfully Imported the table, made an append quary which is
done successfully.
Yes. I will take care that I don't append the same record more than once by
puting condition/cretieria ..etc. in the query before running the append
query.

With thanks

Gopal

 

[Klatuu]

Then as I said, link to the table or tables in NewSales.mdb you want to get
the records from and create an append query to copy the new records to the
table or tables in Sales.mdb