Linking Tables (hindsight's always 20/20)

[GIraffe]

I feel immensely stupid and I hope you can help me fix my problem. I have 2
databases using linked tables. Now that I look back (hindsight’s always
20/20), I should have seen this problem before I started … but for some
reason I just thought Access would “know†which database I was entering data
from and keep my data straight (sigh). So here’s my problem:

DB1
tPubs
ID [relationship, 1-to-many w/ tLocation.tPubsID]
Title
Author

tLocation
Autonumber
tPubsID
Room
Space
Number

In DB1, I have tLocation as a subform to tPubs.

DB2
tFiles
FileName
Year

tLocation (linked table from DB1)

In DB2, I have tLocation as a subform to tFiles.

[You probably see my problem before I even explain it.] Here’s what
happened, I entered a record in tPubs, I’ll call it ID1, chose my location
info. When I entered a new record in tFiles (also ID1) and chose my location
info, I overwrote my location information for my ID1 in my tPubs DB.

Originally, I thought linking tables would make my life easier. Now, I can
see one of the potential problems for linking tables and am thinking this may
not be such a good idea.

I’d appreciate your thoughts and advice on how I can fix this.

 

[Klatuu]

Linking tables is Always a good idea. You just need to understand how it
works.
All you forms, queries, modules, macros, reports, and pages belong in on
mdb. The is the actual application. It should not nave tables in it, as a
rule. There are exceptions, like if you want to keep some user specific info.
Each user should have their own copy of the application (front end) on thier
computer.
Each user's copy of the front end should be linked to the back end. The
back end is the mdb that contains ONLY tables and relationships. It is also
a good idea to make a habit of usin UNC paths rather than drive maps. You
can never be sure all users have the same drives mapped to the folders.
Drive maps are like G:\MyData\MyApp_be.mdb
UNC paths are like
\\ServerName\MyData\MyApp_be.mdb

Once you get that straigt, the rest will fall in place.

 

[GIraffe]

Thank you ... I'll give this a try and see how it goes.

Linking tables is Always a good idea. You just need to understand how it
works.
All you forms, queries, modules, macros, reports, and pages belong in on
mdb. The is the actual application. It should not nave tables in it, as a
rule. There are exceptions, like if you want to keep some user specific info.
Each user should have their own copy of the application (front end) on thier
computer.
Each user's copy of the front end should be linked to the back end. The
back end is the mdb that contains ONLY tables and relationships. It is also
a good idea to make a habit of usin UNC paths rather than drive maps. You
can never be sure all users have the same drives mapped to the folders.
Drive maps are like G:\MyData\MyApp_be.mdb
UNC paths are like
\\ServerName\MyData\MyApp_be.mdb

Once you get that straigt, the rest will fall in place.
--
Dave Hargis, Microsoft Access MVP

I feel immensely stupid and I hope you can help me fix my problem. I have 2
databases using linked tables. Now that I look back (hindsight’s always
20/20), I should have seen this problem before I started … but for some
reason I just thought Access would “know†which database I was entering data
from and keep my data straight (sigh). So here’s my problem:

DB1
tPubs
ID [relationship, 1-to-many w/ tLocation.tPubsID]
Title
Author

tLocation
Autonumber
tPubsID
Room
Space
Number

In DB1, I have tLocation as a subform to tPubs.

DB2
tFiles
FileName
Year

tLocation (linked table from DB1)

In DB2, I have tLocation as a subform to tFiles.

[You probably see my problem before I even explain it.] Here’s what
happened, I entered a record in tPubs, I’ll call it ID1, chose my location
info. When I entered a new record in tFiles (also ID1) and chose my location
info, I overwrote my location information for my ID1 in my tPubs DB.

Originally, I thought linking tables would make my life easier. Now, I can
see one of the potential problems for linking tables and am thinking this may
not be such a good idea.

I’d appreciate your thoughts and advice on how I can fix this.