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.
1 there is no limit to the size of the datbase (hard disk)
2 less network traffic
3 less crashers
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.