Linked table from another db; make table query

[Sharon]

Sorry for the duplicate subject, my hand slipped!!!

I have two databases.
Database A - I did a make table query (as the table did not show up when I
selected File - Get External Data and selected the database).

Database B - I selected File - Get External Data and selected the table from
Database A. In Database B, I also did a query make table because I didn't
want all the fields in the table from Database A.

My problem - I went into Database A and entered a new test record to see if
it showed up in Database B. It didn't. I created a Relationship between the
linked table of Database A and a table in Database B.

Relationship
Database A - the field "DocketNumber", lnktblfromDatabaseA
Database B - the field "DocketNumber" in tblReference.

Can anyone give me some direction? Thanks in advance.

 

[John Vinson]

Sorry for the duplicate subject, my hand slipped!!!

I have two databases.
Database A - I did a make table query (as the table did not show up when I
selected File - Get External Data and selected the database).

So you did a make-table from... what source of data? What table didn't
"show up"? What database were you in when you tried Get External Data,
and what did you link to?

Database B - I selected File - Get External Data and selected the table from
Database A. In Database B, I also did a query make table because I didn't
want all the fields in the table from Database A.

Ok... so you created a new table.

My problem - I went into Database A and entered a new test record to see if
it showed up in Database B. It didn't.

Why would it?

If you had a document, made a Xerox copy of it, and then edited the
original document, you wouldn't expect the edits to show up on the
copy. Same principle!

I created a Relationship between the
linked table of Database A and a table in Database B.

Relationship
Database A - the field "DocketNumber", lnktblfromDatabaseA
Database B - the field "DocketNumber" in tblReference.

You can't make an *enforced* relationship between tables in separate
databases... and creating a relationship will not (and should not, and
is not expected to) insert or edit records in a related table when you
insert or edit records in the main table.

A Relationship *PREVENTS* you from making changes that would create
"orphan" records. That's *all* that it does.

Could you explain what the two databases are for, and what - in a
real-life business sense - you're trying to accomplish?

John W. Vinson[MVP]

 

[John Vinson]

Database A is a database created by an outside company for a patent law
office docketing program. This database contains all of the information with
regard to the patents we handle. Database B is a database created by me to
manage references, U.S. patents and foreign patents for information
disclosure purposes. I have the database set up exactly the way that I want
it to in regard to the actual references, etc.

Now, I want to link (or whatever is the best way) Database B with Database
A.

Example:
Reference 1 is a reference that has been disclosed in the following patent
prosecutions:
USPTO23425
USPTO25398
USPTO29689
USPTO38692

Reference 2 is a reference that has been disclosed in the following patent
prosecutions:
USPTO95439
USPTO95432
USPTO95432


Each reference can be disclosed in multiple patent applications and because
our patents all pertain to cancer research, this is quite common and one
reference can be used in the 50 files. If you disclose a reference in one
file, you have to be sure that it is also disclosed in all other patent files
pertaining to that particular subject matter so it is important to keep track
of which references were disclosed in each patent file.

I created a make table query in Database A (as the tables created by the
outside company's tables are not accessible to me so I created my own
separate table). In Database B, I selected Get External Data/Link and linked
it to the make table created above. I then created a continuous subform with
a combo box populated with each file number, i.e., USPTO23425. So, I can
select each file number where this reference has already been disclosed.

Anyway, I thought "linking" meant that when something was changed or added
in the original data (Database A), this change would also be made in the
external file (Database B).

Does this make more sense?

Not really. You're talking about business rules in a business with
which I am not familiar, rather than talking about database structure.
And you're using jargon that I don't understand - the phrases "the
tables created by the outside company's tables" for one (tables can't
create tables, they just sit there containing data) and "linked to the
make table" - you can link to a Table but not to a make-table query
(maybe that's what you meant?)

A "linked table" is a *pointer* to a table in another database,
another .mdb file on the same server or at least the same stable LAN.

If you create a Form (not a combo box) based on the linked table, then
yes, anything you update in the linked table will be updated - there
IS no local table, just a link.

But if you run a MakeTable query, that creates a new, independent,
unlinked table in your local database; any updates made to it are made
locally, and do not affect the linked table.


John W. Vinson[MVP]