Thread: pros and cons of linked tables
05-04-04, 20:18 #1Registered User
- Join Date
- May 2004
- Salt Lake City, UT USA
Unanswered: pros and cons of linked tables
I currently have a front-end db and a back-end db and have linked several tables between them. Sometimes the network seems slow and even goes down from time to time - even if only for seconds. If one of the users has a form open when this happens the link is sometimes severed and then the db has to be restarted. I have considered using a different method but am unsure of the consequences. So I present 2 scenarios:
1) A form uses a linked table as its recordSource. The various controls on the form are linked to the corresponding fields of the underlying table. All updates/edits, etc. are done automatically by Access. This is how I am currently set up.
2) A form with no recordSource (and therefore none of the controls have underlying fields). When the form opens, a snippet of VBA runs which connects (using ODBC) to the back-end db, gets the record I want, then closes the connection. The retrieved record would be used to "populate" the various controls with the appropriate field values.
I am wondering what the pros and cons are for each scenario. In scenario 1, is the connection "persistent" - meaning that it remains open as long as the form is open? If so wouldn't scenario 2 be more efficient because it only keeps the connection open long enough to perform the given task?
Any insights into this would be greatly appreciated.
05-04-04, 22:43 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
all pros with linking table
1 there is no limit to the size of the datbase (hard disk)
2 less network traffic
3 less crashers
4 ...hope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON