How to edit Northwind Database

[Greg]

Hello Anynone,

I have a business that requires that I keep track of my
inventory among other things. I have a copy of the
NorthWinds Access database that ships with Access 2002
XP. The design closely fits my business needs, at least
for the moment. I need to know how I can edit the
database and populate it with my company's information
(products, customers, purchases etc). I made a copy of
the database and tried to delete information on the
products table. I received a warning that it cannot be
deleted because of the relationship it has with other
tables. I tried another table and it said that the table
was locked. What is the easiest way to flush out every
information in these tables and begin to re-populate
them? I have a general sense of how access works.
However, at this time, I do not have the time or patience
to go re-aquaint myself with self-help books. I would
greatly appreciate any help from out there

Thank you
Greg

 

[DDM]

Greg, it wouldn't let you delete the records because they were related to
records in another table, and deleting those records would have created
"orphan" records in that other table. Click the Relationships button on the
Database toolbar to bring up the Relationships window and study how the
tables are related. Most of the tables are tied together in a one-to-many
relationship. Start with a table on the "many" side of the relationship
(that will be the one with the "infinity" symbol next to it) and try
deleting the records from it. You should be able to do this. Work your way
backward through the relationships, deleting records on the "many" side,
then those from the table on the "one" side. You should be able eventually
to clear out the entire database.

When that's done, you'll want to compact it. Tools > Database Utilities >
Compact and Repair. That will reset AutoNumber fields to 1. Then you can
take it from there.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Greg]

Thanks Mara. I will spend time tomorrow doing this.

Rgds,
Greg

 

[Marin Kostov]

Enforce Referential Integrity.

Here is the Help...

Cascading updates and deletes

For relationships in which referential integrity is enforced, you can
specify whether you want Microsoft Access to automatically cascade update
and cascade delete related records. If you set these options, delete and
update operations that would normally be prevented by referential integrity
rules are allowed. When you delete records or change primary key values in a
primary table, Microsoft Access makes necessary changes to related tables to
preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a
relationship, any time you change the primary key of a record in the primary
table, Microsoft Access automatically updates the primary key to the new
value in all related records. For example, if you change a customer's ID in
the Customers table, the CustomerID field in the Orders table is
automatically updated for every one of that customer's orders so that the
relationship isn't broken. Microsoft Access cascades updates without
displaying any message.

Note If the primary key in the primary table is an AutoNumber field,
setting the Cascade Update Related Fields check box will have no effect,
because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a
relationship, any time you delete records in the primary table, Microsoft
Access automatically deletes related records in the related table. For
example, if you delete a customer record from the Customers table, all the
customer's orders are automatically deleted from the Orders table (this
includes records in the Order Details table related to the Orders records).
When you delete records from a form or datasheet with the Cascade Delete
Related Records check box selected, Microsoft Access warns you that related
records may also be deleted. However, when you delete records using a delete
query, Microsoft Access automatically deletes the records in related tables
without displaying a warning.


How to do that?
Open the relationships window.
Double-click the lines between the tables (the relationhips).
Tick the Cascade Delete check-box.
Now when you delete records in one table, Access aotomaticaly deletes the
records in the related tables.
Then Compact and Repair the database.