adding a second table to expand the first one?

[Jerome]

Hi,

I've got the following situation:

I've got a main database on the server with several tables where the
ordinary user only has reas-only rights. But he can link to them from a
local DB he creates so he can use the data from the main database to
display it.

The thing is he needs to locally add several more columns for each
record. How would we now do that? How would we link those two tables
together so that when there is a new record added in the linked table,
it will be shown in the local table as well so that the user can add the
additional (local) information for that record as well? It would also be
a one-to-one, not one-to-many, relationship.

Any help is greatly appreciated,

Jerome

 

[Jeff Boyce]

Jerome

I may be reading too much into your description ... if so, please
disregard...

If someone tries to extend a normalized database by "adding a few columns"
to a table design, I immediately suspect that they are working with a
spreadsheet mentality. I'm occasionally wrong in this inference, but right
way more often.

While adding a few more columns may provide some sense of immediate
gratification, this approach can cause no end of issues long after the warm
fuzzy wears off. If there's a need for additional data related to the
current system, consider re-evaluating the normalization and adding the
attributes into the back-end table(s) where appropriate.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Spencer]

There is no way that you can automatically create a new record in the local
table when a record is added in the linked table. However, you can create a
query that will identify when there are records in the linked table that are
not in the local table.

You should be able to use the Unmatched query wizard to do that. Once you
have this query, you can use it as the source for an append query and add
new records to the local table.

Do you have the local table designed? If so, can you share the structure of
it and tell us which field(s) identify the corresponding records in the
linked table?

Generic SQL to add the record(s) would look something like:

Insert Into LocalTable (ForeignKeyToLinkedTable)
SELECT LinkedTable.PrimaryKey
FROM LinkedTable LEFT JOIN LocalTable
ON LinkedTable.PrimaryKey = LocalTable.ForeignKeyToLinkedTable
WHERE LocalTable.ForeignKeyToLinkedTable is null

You could then run this periodically to update the local table.

Then you would need a query to identify the records that didn't have the
additional information or had not been updated. Perhaps you have a updated
date field or a yes/no field that is marked when you add the extra
information.

 

[Michel Walsh]

Hi,


exactly. A one to one relationship is, in fact, a one to (zero or one)
relation and you can "edit" your two tables through a query. Jet will use
its autolookup feature, in this case, to help you to add record in the
second table, as required.


Hoping it may help,
Vanderghast, Access MVP