Slow Connection to Linked Tables

[tom in houston]

Hi:

I'm having trouble with linked tables.

Basic Setup - Access2010 connecting to a shared SQL SERVER 2005 (provided via databasemart). Access FE has linked tables to SQL Server tables. DSN-less connection using the following string:

strODBC = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUserName & ";PWD=" & strPassword


Initial routine is to drop the link and recreate it for the 6 or so tables. Most of the tables are pretty small - few doz records. Two tables are large, largest being 500K records. Next a dialog box comes up with one combo box on it with a link to a small "facility table" to allow the user to select the facility to work work (i.e., which subset of the records). Once done, the main form comes up with 3 or 4 records loaded including a subform that may have a few dozen records.

On my development box, the whole process is quite speedy - basically what you would expect in launching access on a good speced machine. However on the client's box performance is a real drag. Maybe 20 seconds to get to the dialog box form and just as long again to open the main form. They have a brand new computer, with all current updates running a fresh install of Access2010.

When the client's box was in my office connected to my network , performance was as good as on my development box. On their network - sucks...

Additional data point - if I open a complex query in SQL SERVER Management Studio and run it, the query actually returns results faster on the client box than on mine! I looked at rated upload and download speeds from our respective ISPs, and the client actually has a faster connection.

Finally, if I drop the linked tables and just run reports via pass thru queries (and set the facility in code), response speed is fine. It seems therefore its something to do with linked tables.

Any suggestions on what is going on? Or how to fix without getting rid of linked tables? If I have to get rid of liked tables, I've identified two alternatives for the forms - assigning an ADO recordset as the forms recordset or using pass thru queries as the forms recordset. ADO won't work since the subform is a continuous form. I'd like to avoid pass thru's since they don't give editable data.

Thanks

Tom

 

[AccessMSSQL]

What OS is the SQL Server 2005 install running on? and what is on client machine? I've had problems with Access 2010 on a brand new Windows 7 64 bit machine connecting to SQL Server running on a Windows 2003 Server Box. My client had to downgrade to win 7 32 bit. I never figured out what the issue was but their IT disabled firewall settings to improve performance a little bit but not much.

 

[tom in houston]

OS for SQL: Microsoft Windows Server 2008 R2
OS for Access Box: both are win7 64 bit

Can you clarify if you were running 32 or 64 bit in your cited example... In other words if I have this problem, should I expect it would affect all 64 bit client OSs, or only some.

Thanks!

 

[AccessMSSQL]

We actually were running 32 bit Access 2010 and win 7 64 bit. We had them upgrade to Access 2010 64 bit and they still had the performance issues. What solved the problem is they downgraded to Win 7 32 bit and Access 2010 32 bit - but they are still running in Windows 2003 server environment. So not exactly your same configuration. How many machines are having the slow issues? I just thought of another customer I had performance issues with - they were running on win server 2008 r2 with sql server 2008 and win 7 64 bit. I just looked at my notes from the performance issues. Here is what I did to solve:troubleshoot DNS issues on server. Turn off firewall blocking server connections. Re-configure two machines to point to server IP and disable TCP/IP v6.0 on machines.***** I think this solved their issue - so you might want to make sure the machines are configured properly to connect to the domain and not obtain IP address automatically. Also disable TCP/IP v6.0 on clients.

 

[tom in houston]

I am an absolute newb on configuring connections, so be gentle...

However, I found MS Mr. Fit It for disabling IPV6 (kb 929852) and followed it. No improvement. Since the SQL Server is hosted by a 3rd party, I don't think I can accomplish the rest of your suggestion.

Any other ideas?

 

[AccessMSSQL]

Oh...sorry, did not realize the SQL Server was hosted by 3rd party. Thought the SQL Server was onsite at your customer? Is it through a hosting company or something?

 

[tom in houston]

sorry - I wan't real clear in my initial post... 3rd party hosting service. Tried contacting them - they tried to be helpful, but the guy I spoke to had zero experience with Access and basically couldn't help me at all.

Additional info - dropped all linked tables and shifted over to pass thru queries for the initial form to pick the subset of data and the first form to display data just to see what would happen. Performance did speed up, but still not acceptable and not nearly as fast as what I get with linked forms on my development box.

This is killing me... its got to be something with how they connect to the internet or how they get to the 3rd party hosting service. I'm about to sign up with another host service just to see if that makes any difference!

 

[AccessMSSQL]

Why don't you just install SQL Express on the customers own server and move the database there? OR is there a web front-end also hosted with the same provider?

 

[tom in houston]

(very) small business - no server, just a couple desktops. part of the allure of moving to a hosted service was to let somebody else deal with all the IT issues. So much for that plan!

 

[Lightwave]

Tom

I've had similar problems before. I have ended up changing forms to be based on simpler queries with less tables as I have found that queries with many linked tables can be a problem.

You also want to ensure that you don't have any look up fields directly in the query. If there are FKIDs in there keep the FKIDs and translate them on the form rather than in the query (if you understand what I am saying)

 

[Lightwave]

And here's some further suggestions on speeding things up

http://http://www.accessmvp.com/TWickerath/articles/multiuser.htm

 

[tom in houston]

Lightwave - will look at the website when I get back to my computer - thanks for the suggestion