[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Which is more efficient - Append data to local table from Linked table or import the linked table

Posted on 2006-06-02
9
Medium Priority
?
220 Views
Last Modified: 2006-11-18
Which is more efficient - Append data to local table from Linked table or import the linked table?

I am currently using linked tables and running an delete the data from the local table then appending the records to the local table from the linked table.  Also which is less likely to break?

The reason for this is because I am creating a data warehouse for my client and they do not have the rights to the linked tables for querying.

So which is the most efficient - please note that some of the tables are quit large - I am planning on running this at night due to the activity on the server during the day.

Your opinion is greatly appreciated.

Karen
0
Comment
Question by:Karen Schaefer
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16820538
if u append data, then u are only adding a subset, this is assuming you perform a select on the other table

how do u define what data to append? do u have som criteria that determines what data to append?

If u only do a subset, then its probably better to do an append



Im still a little confused by your last question


Its also late for me and Im very tired, Im gonna call it a nite,
other experts will be on hand to sort u out

0
 

Author Comment

by:Karen Schaefer
ID: 16820550
The there is no criteria - I am delete all data from the table and refreshing (appending) it with the data from the linked table - which can be from Oracle, Excel or other Access databases.

Thanks to rockinroads for all his patience with me.

Good night.

Karen
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16820553
Rather than deleting all the records from the table it's a lot faster to merely drop the table outright.  The following query will do the trick...

DROP TABLE tblMyTable

Once the table has been disposed of (and you've relived access of having to think of all those records that need be deleteted since it will merely dump the whole thing).  You'll probably find it faster and easier to run a MAKE TABLE QUERY that inserts all the records from the linked table into the new table being created on the fly.

SELECT tblMyLinkedTable.f1, tblMyLinkedTable.f2, tblMyLinkedTable.f3 INTO tblMyLocalTable
FROM tblMyLinkedTable;

Probably not quite what you had in mind but definately faster.  Besides, eliminates a step that is probably taking up a measurablle amount of time (deleting all those records).  Dropping a table is something that will happen faster than you can blink.

Rick
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 44

Expert Comment

by:GRayL
ID: 16820595
One of the reasons for 'linking' is to provide a means for other's in another app to stay connected. A linked table will let you do that.  Changes made in an Excel spreadsheet 'linked' to an Access mdb will allow the Excel users to keep the Access user informed.  The Access user is allowed to refect his changes back to the Excel users provided it does not involved a 'calcuated' value in Excel.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16820614
Said another way:  If it's a one time update per some period of time, probably the Import is more appropriate.  If it is time sensitive and important that both apps have the most current info, stay with Linked - provided you can live with the linking rules.
0
 

Author Comment

by:Karen Schaefer
ID: 16820694
Yes I will be updating the data on a weekly basis and the process will be run overnight. - The linked tables will not be used as a source for any queries, just the updated local datawarehouse tables.  Due to the process be slow over the servers.

So to make sure I understand your suggestions - I should use the linked Oracle tables and Delete the records and then copy/Append the data from the linked tables into the local tables is the most efficient method for updating my data warehouse.

K
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 16820727
If you are saying once a week you update your table(s) and do not connect again (to Oracle) until next week, then I think you should be Importing, not Updating.
0
 

Author Comment

by:Karen Schaefer
ID: 16820765
Thanks for your suggestions.

Karen
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16821378
Thanks, hope I'm right!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question