SQL Server tables linked into Access - advice

[BigJimSlade]

Hi, Big Jim here:

I have done some searching and found lots of interesting little tidbits of information on this topic, but I thought I would ask here and see if there was something major I was missing...

I have sql 7.0 tables \ views linked into Access 97. They are linked over a network from Tampa to Seattle. For me to retrieve 500 records with 200 fields apiece, it takes about 45 seconds.

I created a TimeStamp in the SQL table (wonder if I have to do anything else with it), but I am not sure what else I can do to speed things up. Any thoughts? Perhaps I could cache the links?



(here is how i link the tables in)

tbl1.Connect = "ODBC;DATABASE=SOQR;UID=myid;PWD=mypswd;driver={SQL Server};server=myserver;"
CurrentDb.TableDefs.Append tbl1

(also, here are some examples)

When Seattle asks Tampa for the same data in a linked Access table 3 times, it takes 50 seconds, 4 seconds, and 4 seconds.

When Seattle asks Tampa for the same data in a linked SQL Server table 3 times, it takes 34 seconds, 34 seconds, 34 seconds.

Why did the SQL table not speed up as the Access table did? Am I just rambling now?

Thanks in advance!

Big Jim

 

[WayneRyan]

Big Jim,

This is not really an answer...

Access, when it makes a recordset, will buffer the
information. That's why when you re-sort the info
using the A-Z & Z-A buttons, it's quick.

I'm just getting introduced to SQL Server, but I
don't think that when you request a "recordset",
or query, or whatever that it does any buffering
at all.

In short, when requesting data from Access, you
may have some latency, but for the same data later
you will see it quickly.

When using SQL server, you have to minimize the
amount of data that you transfer over the connection.
Use "narrow" recordsets and restrict the amount of
info that you transfer over the net.

Wayne

 

[BigJimSlade]

Thanks, Wayne!

Big Jim totally agrees about limiting the amount of data that comes over from SQL Server. I found that moving one record at a time takes less than a second. Perhaps I can set my form to a query that returns only one record at a time and then rerun the query with different parameters (when the user wants to move onto another record).

Thanks again for your response!

Big Jim

 

[WayneRyan]

Big Jim,

You can look at having "unbound" controls; combos or listboxes
that are parameters for a query. In their AfterUpdate event you
can just do a Me.Requery.

At all costs, don't base a form on an entire table, or a query
that does so.

Wayne

p.s. I'm gonna need some help with SQL Server soon, stay in
touch!