Should I Link or Import Tables?...

[kdm3]

Me again, my head's sore from banging it against a wall!

I wanted to ask for opinion regarding the pros/cons of linking tables.
More specifically I am writing an application to do some pretty scary stuff, and need to 'get' some data from another application - but then need to add more fields and info.

I originally considered importing the necessary fields, and then adding the extra info required as necessary, but am now erring towards linking to the relevant tables, and then creating a 1:1 relationship between the linked tables and tables containing the extra info I need.
I'm kinda concerned about the updating of tables, keeping the records up-tp-date and accurate, so linking would be better, but I'm just a bit concerned it may be really slow if I have a linked table, which is then related to a table within the application.

I'm kinda new to this so some advice would be much appreciated...

Thanks in advance,

kdm3

 

[kdm3]

Further...

In answer,

From Sage, basically details regarding orders, customers, suppliers etc...

Needs updating whenever a new order/customer/supplier etc. is added, so would need updating tres often! I had intended adding new records periodically using a timer!

Didn't get that one!?

The data is required mostly while the order is still active, to track production/purchasing etc. but will then be archived after order is completed.

Any suggestions bearing this in mind?

 

[Pat Hartman]

My First choice since the new data is 1-1, is to add the new columns to the existing Sage table.

My Second choice is to add a new table to the Sage database. This allows you to create a relationship and establish RI.

My Third choice is to add a new local table. There are at least two problems with this method. The first is that you CANNOT establish RI between tables in two different databases. For me, this is usually a deal breaker. Relationships without RI are a recipe for trouble. The second problem is that when you join a Jet table to an ODBC table, unless you are very careful, Jet may decide that it needs to download the entire ODBC table and execute the join locally. If the ODBC table is large, this may cause too much network traffic.

My Fourth choice is to import the ODBC table on a regular basis. The problem with this is twofold. First, this technique prevents the establishment of RI between the two local tables because you won't be able to delete the parent records without deleting the related child records. Of course, you could remove RI, do the delete, import the new data, and put RI back - The second problem with this is that it causes substantial db bloat. Jet doesn't recover space from deleted data/objects until the db is compacted.

So that leaves us with my Fifth choice which is to import the table, create the additional local table and establish RI. Then run an update query against the local Sage table. This requires joining the local Sage table to the linked ODBC Sage table and using a special update/append query to update existing data and append new data. The problem here is that the query doesn't recognize when records are deleted from the linked table and so they never get deleted from the local copy.