Persistent links to SQL tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
Sylvain,

Thanks for the info.

Bill T.



Sylvain Lafontaine said:
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.
Click to expand...
 
god mdb is crap

i mean-- should you really have to do all this elaborate shit?

wake up guys; ADP is a much simpler implementation
 
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
 
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
 
Back
Top