Linked SQL Server tables in Access

[Guest]

Hi,

I have about 50 linked Acess tables that connect to SQL Server, that are
linked to access forms. Each form is linked to a query, which is based on
many other queries.
The problem is that it takes about 30-60 seconds for the results to display.
Sometimes, it says that the ODBC link was lost. The tables are between
200,000 and 12 mil. records. I know access is not designed for large tables,
but is there any way I can correct this? Thanks,

Michael

 

[Jeff Boyce]

Michael

Are your forms bound to queries that return ALL the records? That would
take a long time with tables that large. If they are, consider a different
approach:

* create an unbound combo box in the header of the form
* based that combo box on a list of records (minimum data needed to ID
correct one)
* modify your "form" query to use the combo box as a selection criterion
* add Me.Requery to the AfterUpdate event of the header combo box
This results in a single record being "loaded" (and uses a smaller faster
query to load the combo box). Actually, you could modify how that combo box
works to only start looking for records after the first 2, 3, ... n
characters were entered, making the combo box much faster.

Are the underlying SQL-Server tables well-indexed? Are the fields used in
your query for selection, sorting and joining all indexed? Adding an
appropriate index can speed up queries remarkably. (You'll need to remember
to re-link to the tables in SQL-Server if you add indices.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thanks for the reply. Yes, my forms are bound to queries that return all
records, but the user will always search for a specific contract #. So, they
should get one or two results. The tables are indexed, but the fields I
search on and some of the join fields are not indexed. There is nothing I
can do, as the tables are read only. Also, the queries are based on 10-16
different tables. I think this is what's causing the ODBC link to time out,
or the search to run 1-2 mins. I have a text box that I use for the search,
since having a combo box that displayed all of the millions of search
criteria would take too long for the user to go through.

Michael

 

[Jeff Boyce]

I understand that your view of the tables is read-only.

Perhaps you could persuade the db admin to index those fields, as it would
mean you'd NOT be tying up his/her database for so long when you run your
queries.

Another approach might be to have the db admin create "views" for you to
use, pre-joining those tables. You'd still want to suggest indexing...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MH]

What the OP really needs is a perameterised stored procedure which is called
using a pass-through query. The network traffic will be reduced and the
server will process the SQL much more efficiently than the current setup.

MH