Using Access as a front end

[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.

 

[Catadmin]

Unfortunately, in my experience, using Access as a front end to SQL has always been a less than desirable solution because it does bog SQL down considerably. And I know of no way to use persistant connections from Access to SQL.

Sorry, but I think you're stuck as far as connections go.

However, you might want to check into your WAN connections to see if there's anything you can do to clear up / speed up network traffic. For instance, if you're using dial-up to get across your WAN, try changing to a 56K or T1 line. Heck, even Broadband might be better.

You might also check the PCs that are using Access.

Verify the number of ODBCs and Network protocols you have installed are as minimal as possible. Those also cause slowdown problems (on the Access side at least). User DSNs are preferable to System DSNs which are preferable to File DSNs (I believe that's the order of least slow ODBC to most slow ODBC).

Check ALL PCs (not just the ones that use the Access front end) for any network related programs such as StockTicker, WeatherBug, BorgChat, anything minor program in the system tray that "keeps current" via an internet connection. That chews up your bandwidth and will also cause Access to SQL problems. More often than not, I've found the bogging issues of SQL are related to network bandwidth being used by non-business related software running in the back ground of the users' PCs.

I hope that helps you out a bit. Sorry I couldn't give you the magic answer.

Catadmin


Catadmin - MCDBA, MCSA
"The only stupid question is the one you *didn't* ask.

 

[CylonLove4Life]

I know that ODBC supports connection pooling. Couldn't I use this to keep the connection alive?

 

[Catadmin]

It's not so much the ODBCs that are doing this as I think that was the way Access was designed. You can try the ODBC connection pooling, but ISTR that the developers at a former workplace of mine couldn't get that to work for them.

Catadmin


Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.