where are tables

  • Thread starter Thread starter JIM WHITAKER
  • Start date Start date
J

JIM WHITAKER

Generally speaking, in this newsgroup, are the sql tables in same building
on a server, or are the tables in another city. I'm trying to figure out if
some of the techniques I've learned from this newsgroup, and using a small
network at work, will also work on a remote (meaning long distance away)
database. I hope when this newsgroup refers to a remote database/connection
it's not in the same building. This is still local and easier to deal with.
If one of you wanted to connect to an sql server database using ms access as
frontend, but the server was 500 miles away, how would you do it? Weather
host or company server? What are those secret details to doing such as
this?
 
The common definitions (in a SQL Server context, anyway) are that "local" is
on the same machine as you're using, and "remote" is a different machine.
In point of fact, distance isn't very relevant, it's more a question of how
good the connection is at that distance. I can, for example, connect to
work over VPN from my Mom's place 3000 miles away with no noticeable speed
difference than when I'm connecting from home over VPN. (For the most part,
I think this is due to the fact that home-base DSL & cable speeds here in
Canada are about the 3rd or 4th fastest in the world.)

But on to your point, in terms of using SQL Server over a long-distance
connection, the tips to keep in mind are to reduce round-trips as much as
possible (do as much as possible with one read/write/command), and only
retrieve/send the data that you need. Don't use "SELECT * FROM MyHugeTable"
then filter it on the client side, filter it on the server side if possible
with "SELECT OnlyThisField FROM MyHugeTable WHERE SomeOtherField = 1".

One of the biggest hints I've found is that wherever possible, for any kind
of bulk update, don't let Access do it, just send one huge command string
all at once, or use ADO recordsets in bulk update mode. Access waits for
round-trip confirmations after every update, where a single command string
or bulk update sends all the changes all at once, then waits for a single
okay. Not always something you'll want, but if you have a reasonable
likelihood of an error-free update, it's a good method to use.



Rob
 
Thanks for the 2 great replys. I will look into the VPN aspect. And I
realize to only query whats needed, IE., Selecting only customers names
starting with "dav".
 
what you need to do is this:

a) use ACCESS DATA PROJECTS instead of mdb-linked
b) it is much more efficient than MDB against linked tables
c) not as difficult to implement as MDB.. in other words, you dont have
to deal with Sql passthroughs.. you dont have to deal with a whole ton
of other crap like linked tables, refresh tables, testing tables,
performance--

it's just a lot simpler to use a ADP
 
Back
Top