Tables: Appending Data, Link or Not to Link, That's My Question...

[MJ]

I am sure that someone has asked this at one time or another, but haven't
found it out here yet.

I have several inter-related databases, some of them have linked tables
while others have queries appending to tables which they are not linked to.

What is a good rule of thumb for when it makes better sense to link to a
table rather than simply appending to a table? What are the PROs and CONs?

Thank you in advance for your informed inputs.

 

[Steve]

Linking to a table refers to having a backend database of tables only and
linking to the tables from the frontend on each user's computer. All
databases ought to be split excpt where you can say with 0ne-hundred percent
certainty that the database will always be a single user database. Linking
to tables and appending to tables are two separate distinct issues and one
can not be substitued for the other.

Steve
(e-mail address removed)

 

[Fred]

Linking and appending are for completely different purposes, and so there is
no basis for a general "either/or" type question.

Perhaps if you could give the specifics of something that you want to do, we
could tell you which to use for that purpose.

 

[MJ]

Ok gentlemen, perhaps I did not word my question correctly.

1) [Steve] I understand FE/BE splitting, so need to beat it into ground.
Also, understand difference from a linked table vs appending to a table.
2) [Fred] The question was general in nature... wahat are the pros & cons

Specifics: I have DB1 that serves as the source for info to be appended to
a table in DB2. It currently does this using the URL of DB2, vice linking
the table in DB2. It works.

What I am looking for other experienced Access users, are there any
advantages or disadvantages to linking the table from DB2 to DB1 and simply
appending the info to the now "local" table (linked)?

* I see them as 2 ways of doing the same thing, wondering if one is
better than
* the other, or if the are real tradeoffs that should be considered in
doing it one
* way over the other.

I hope this clarifies my questions. Once again, thank you for your response
and taking time out to help another.

 

[John W. Vinson]

I am sure that someone has asked this at one time or another, but haven't
found it out here yet.

I have several inter-related databases, some of them have linked tables
while others have queries appending to tables which they are not linked to.

What is a good rule of thumb for when it makes better sense to link to a
table rather than simply appending to a table? What are the PROs and CONs?

Thank you in advance for your informed inputs.

If by appending you mean that you're copying data from one database into new
records in a table in another database, just be aware that you now have *the
same* data stored in two different places. This is called Redundancy, and it's
generally considered a bad thing to do!

Suppose you append some data from BigJoe.mdb into Mylocal.mdb. The guy in
charge of BigJoe realizes that some of this data is incorrect, and goes in and
corrects the errors.

You now have two copies of the data. They're different. One of them is wrong
(yours, as it happens, but how can you tell?).

Not only are you wasting space but you now have wrong data in your database,
and no way to reliably detect that it is wrong.

The only time I'd use an append is when you intentionally want a point-in-time
snapshot of the current data in BigJoe, realizing that it may be out of date
at any later time.

 

[MJ]

John,

You make a valid point, were this updatable data I would be concerned about
duplication, but it exactly as you said a "snapshot" in time results
reporting, which is broken out and distrributed to users for worklists.

Thank you for your observations, please keep them coming, it is a learning
process.

 

[david]

The main historical reason for using non-linked
databases was when using a VB (or C) front-end.

Using a VB front end, you aren't working with the
link wizard, nor with the database window. And
without those, a table link is just a way of hiding
the location of the target database, and making life
more difficult for the developer/supporter.

The main advantage of using a non-linked database
is when using multiple dynamically-linked databases.
If you are dynamically linking multiple databases,
the link doesn't really help you, because it stores
only one link path. It's just one more thing to set
up, tear down, and keep track of.

Table links do store extra information about the
target table (so do the less common query links I
think) This probably makes a query (like your
append queries) faster to set up, and marginally
faster to execute.

Computers are so fast now, that you probably
can't even notice that difference.

It also means that sometimes the information
becomes wrong and out of date if your BE
changes.

But in Access, (unlike VB or C), having the linked
tables makes it easier to write queries, and easier
to do ad-hoc testing in the GUI.

So I find that even when I am doing dynamically
linked tables, I tend to use table links, just for
the extra ease when developing.

(david)