Access and SQL Server: Persistent?

[CylonLove4Life]

I'm working on an Access front end, connected to a SQL Server back end (as linked tables).

Over the LAN, it's fine, but over a WAN (ping <50ms), it's slow as molasses.

I ran the SQL Profiler, and it shows a separate Audit Login and Augit Logout for each query. It seems like Access is logging in and out of the SQL Server for each SELECT statement! It seems to me that this is the cause of the slowdown.

Am I correct? More importantly, what can I do to have it use persistent connections (or solve the problem some other way?)

All help *much* appreciated.

 

[MDTekUser]

One thing that I would recommend to increase your performance is to get rid of the linked tables and use an Access project instead. When you have linked tables you are adding another layer from the server to the client. Whereas in an Access project you are directly accessing the SQL Server. An Access project also keeps a persistent connection to the SQL Server database.

When you upsize to an access project, beware, because you'll likely end up having to re-write most of your Access queries as SQL Server stored procedures.

 

[CylonLove4Life]

Thanks for the tip.

I actually didn't develop this project - just brought in to speed it up. I see from the SQL Profiler that the main problem is that Access keeps on reconnecting - so I'd like to address that first.

 

[MDTekUser]

If you don't want to make any design changes then you could create a form in the background that is always connected to a backend table.

Part of the problem could be the locking file (.ldb).

This link below addresses this issue.

http://www.granite.ab.ca/access/performanceldblocking.htm

 

[CylonLove4Life]

I think .ldb's are only relevant to Jet BackEnd's - not SQL Server backends, no?

 

[MDTekUser]

You said you were using Access as a front end, so those front ends will each create their own locking files.

 

[jsteph]

Access should cache the connection. If there is an interruption in WAN traffic, it may disconnect, and require reconnection.

But with the default ODBC driver for SQL server and default Options in TOOLS-->OPTIONS-->ADVANCED
(refresh interval, etc), the connection should be maintained throughout the Access session.
--Jim