updateability of linked tables

[Guest]

i have a table that is linked from another a2k database (call it 'A') in my
a2k database ('B'). one of its columns (e.g "City") is used in a query which
is itself used as the data source for a combobox field ("Patient_City") in
the 'B' database, e.g.

SELECT [Registration].[City] FROM [Registration]

so, based upon what i know, if the value of 'City' should change in 'A', it
would be reflected in 'Registration' in 'B', but would the value for any
record having an entry of 'Patient_City' in 'B' dynamically update or would
it be dependent upon being informed that say there was a change in the
spelling of 'City' in 'A' , say from 'Rio De Janeiro' to 'Rio de Janaiero' or
whatever else you care to conjure up.

ideally every time my 'B' database user opened the form with 'Patient_City'
it would reflect the current values for all the 'City' names in
'Registration' and/or ran a report on the underlying table behind the form.

anyone?

 

[John Vinson]

i have a table that is linked from another a2k database (call it 'A') in my
a2k database ('B'). one of its columns (e.g "City") is used in a query which
is itself used as the data source for a combobox field ("Patient_City") in
the 'B' database, e.g.

SELECT [Registration].[City] FROM [Registration]

so, based upon what i know, if the value of 'City' should change in 'A', it
would be reflected in 'Registration' in 'B', but would the value for any
record having an entry of 'Patient_City' in 'B' dynamically update or would
it be dependent upon being informed that say there was a change in the
spelling of 'City' in 'A' , say from 'Rio De Janeiro' to 'Rio de Janaiero' or
whatever else you care to conjure up.

No, unfortunately. If you had a Relationship defined with Cascading
Updates set, then it would - but you cannot enforce relationships or
set Cascading Updates between tables in separate databases.

Is there any chance that you could store a CityID (perhaps a postal
code uniquely identifying the city) in your table, and just retrieve
the city name by linking to the A table?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

i am trying to understand what the limitations of a2k come from in this
situation.

wrt the pt you make in 2 sentence: what if i removed the SQL code and
replaced it with a "direct l/u" instead - what if each "City" name were in
fact already unique?

there is an extension though to this situation, there are subsequent fields
which appear in the same table as 'City' name which are properties of 'City'
and which are used in the 'B' database via this form, so for example, say
another property were 'Current_Population' and we knew that it had changed,
we would identify the 'City' in 'B', then run some SQL code behind another
combo box, e.g. 'CityPop' which would drill down to the 'City' name selected
by the c/b in the form on 'B' and then selected the corresponding
'Current_Population' in the 'A' table to populate the c/b in 'B' with.

i have a table that is linked from another a2k database (call it 'A') in my
a2k database ('B'). one of its columns (e.g "City") is used in a query which
is itself used as the data source for a combobox field ("Patient_City") in
the 'B' database, e.g.

SELECT [Registration].[City] FROM [Registration]

so, based upon what i know, if the value of 'City' should change in 'A', it
would be reflected in 'Registration' in 'B', but would the value for any
record having an entry of 'Patient_City' in 'B' dynamically update or would
it be dependent upon being informed that say there was a change in the
spelling of 'City' in 'A' , say from 'Rio De Janeiro' to 'Rio de Janaiero' or
whatever else you care to conjure up.

No, unfortunately. If you had a Relationship defined with Cascading
Updates set, then it would - but you cannot enforce relationships or
set Cascading Updates between tables in separate databases.

Is there any chance that you could store a CityID (perhaps a postal
code uniquely identifying the city) in your table, and just retrieve
the city name by linking to the A table?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

i am trying to understand what the limitations of a2k come from in this
situation.

Access CANNOT enforce RI between two separate databases: why?

Because you are attempting to establish a Rule in B.MDB enforcing
changes of values in A.MDB, and there is NO way that B.MDB can enforce
this rule if you (or someone else) opens A.MDB independently.

wrt the pt you make in 2 sentence: what if i removed the SQL code and
replaced it with a "direct l/u" instead - what if each "City" name were in
fact already unique?

I don't see how this affects the situation at all. If you mean a
"lookup field", avoid them like the plague (see
http://www.mvps.org/access/lookupfields.htm for a critique). Having a
"lookup table" (which you can use on a Form, though preferably not in
a table) is exactly what I'm suggestiong.

there is an extension though to this situation, there are subsequent fields
which appear in the same table as 'City' name which are properties of 'City'
and which are used in the 'B' database via this form, so for example, say
another property were 'Current_Population' and we knew that it had changed,
we would identify the 'City' in 'B', then run some SQL code behind another
combo box, e.g. 'CityPop' which would drill down to the 'City' name selected
by the c/b in the form on 'B' and then selected the corresponding
'Current_Population' in the 'A' table to populate the c/b in 'B' with.

You can still maintain queries linking the table in A with the table
in B to look up this information. You just can't store the data in two
different databases and have it reliably stay in synch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

whatever; btw: thanks for the bandwidth

Access CANNOT enforce RI between two separate databases: why?

Because you are attempting to establish a Rule in B.MDB enforcing
changes of values in A.MDB, and there is NO way that B.MDB can enforce
this rule if you (or someone else) opens A.MDB independently.


I don't see how this affects the situation at all. If you mean a
"lookup field", avoid them like the plague (see
http://www.mvps.org/access/lookupfields.htm for a critique). Having a
"lookup table" (which you can use on a Form, though preferably not in
a table) is exactly what I'm suggestiong.


You can still maintain queries linking the table in A with the table
in B to look up this information. You just can't store the data in two
different databases and have it reliably stay in synch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps