 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / December 2005Persistent links to SQL tables
To all, I have an Access mdb application with a SQL backend. The app maintains links to about 50 SQL tables. How much of a load on the SQL DB, are the linked tables in Access? I have created a couple of VBA functions that easily create and destroy links to the SQL tables as needed, but if the load of the linked tables in the FE is not that bad on the BE, I don't want to go back through the app and make the changes to reduce the persistent, linked tables. Thanks in advance for the help. Bill T. Hi, You should ask this question in the m.p.a.odbclientsvr newsgroup; as this one is about ADP and not about ODBC linked tables. However, I can tell you that in my opinion, the load on the BE is nothing: until you use them for making some kind of query to the BE, linked tables are only a set of connection properties locally stored in the mdb file and the SQL-Server see nothing of them.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > To all, > [quoted text clipped - 12 lines] > > Bill T. Sylvain, Thanks for the info. Bill T. > Hi, > [quoted text clipped - 22 lines] > > > > Bill T. It depends on how the links are used. The link info is cached in Access, so unless someone opens the base table that the link points to, nothing is happening on the server. You can put a Profiler trace on your application and examine the traffic. Also, consider downloading static data to local Jet tables and repopulating them periodically. For example, a Jet table can be used as the source for the "product type" combo box (where product type rarely or never changes), thus incurring no round trips to the server to fill it. --Mary >To all, > [quoted text clipped - 11 lines] > >Bill T. Mary, Thanks for the info. Pardon my ignorance, but how would I go about activating a Profiler trace? As for caching SQL data to local tables, I am currently maintaining about a dozen local tables. This in one of the things I like about the Access/ SQL combination. I have created the persistent links mainly as a convenience, to avoid having to link the tables on the fly. I didn't want to continue doing so if I was creating unnecessary load on the SQL backend. Thanks again. Bill T. > It depends on how the links are used. The link info is cached in > Access, so unless someone opens the base table that the link points [quoted text clipped - 22 lines] > > > >Bill T. SQL Profiler is one of the tools that ships with SQL Server, and allows you to examine the calls going back and forth between the server and your Access application. If you don't have a full version of SQL Server with Profiler, check out the Developer edition -- it's $49, and has a full set of tools. The license agreement prohibits its use as a production server or in a production enviornment, but it's great for developing the app. Linking the tables all at once when your app starts isn't going to create a load on the server. The load comes when you write bad queries or use pessimistic locking, etc. It sounds like you're doing the right thing caching data in local Jet tables. HTH, --Mary >Mary, > [quoted text clipped - 40 lines] >> > >> >Bill T. re-linking and refreshing and doing all those things are just a major headache. ADP you dont have to deal with any of the crap-- it's just simple keep all your data on one server and it's about 100 times easier than mdb bullshit Friend, joel-ange sitbon has invited you to join GreenZap and get $50 WebCash to spend online. Sign up for a FREE GreenZap account and get $50 to spend at hundreds of the world's premier merchants, many of whom are offering incredible upfront discounts. Click on the link below to go to GreenZap and signup! All thanks to joel-ange sitbon. It's Zappening in the GreenZap Storez. http://www.greenzap.com/joel1962 If you do not want to receive these emails in the future click the link below: http://www.greenzap.com/optout_invite.asp > SQL Profiler is one of the tools that ships with SQL Server, and > allows you to examine the calls going back and forth between the [quoted text clipped - 61 lines] >>> > >>> >Bill T. god mdb is crap i mean-- should you really have to do all this elaborate sh.t? wake up guys; ADP is a much simpler implementation |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.