 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Vista, ODBC and SQL Server 2005
[Discouraged by the lack of any response on microsoft.public.access.odbcclientsvr, I'm now cross-posting to microsoft.public.sqlserverconnect and microsoft.public.access. If the message does not explain the problem adequately or is sent to the wrong group, please let me know what else is needed.... Thanks.] At home, I had totally unacceptable performance with an Access 2007 database which was connected to an SQL Server 2005 database via ODBC on the same laptop computer. But when linking to the database from Access 2003 on other computers on the wireless home network, it linked to the db on the laptop and worked great. Faced with leaving on an extended trip, I removed Access 2007 from the HP Pavilion laptop and installed Access 2003. I still couldn't make the ODBC link reliably and resigned myself to not having access to the db will on my trip. Now that I'm on the trip with my laptop and only intermittently connect to the Internet by dial-up. I've discovered by chance that: (1) if I restart Vista and do not connect to the Internet, the Access 2003 front end will properly ODBC link to the SQL Server 2005 back end on the laptop and run very fast so long as I do not connect to the Internet. (2) if I restart Vista and connect by dial-up to the Internet BEFORE starting Access, the slow linking to SQL Server and subsequent abominable performance occurs, and Access remains unusable even after disconnecting from the Internet. (3) if I restart Vista and connect by dial-up to the Internet AFTER starting Access, performance is OK for varying amounts of time before performance degrades and Access becomes unusable. So -- is it a network protocol issue of some sort? What changes when an Internet connection is made that will inhibit ODBC links to the SQL Server back end? [At home, the laptop was always connected to my wireless network before Access was started. I now realize that some networking issue may have been causing the persistent bad performance since I did all troubleshooting while connected to the wireless network.] UAC is turned off and Access is run as administrator. Turning off the firewall and virus protection are of no help. OS is Vista Home Premium. Thanks for any insight to this problem. Charax It sounds like Access could be using a different means of connecting to SQL Server in those cases. Maybe when the network is disabled it uses shared memory or local pipes, which should be faster, and when the network is enabled it's using TCP/IP? I'm afraid I can't help with how to diagnose the issue, but maybe looking into this area would help. > [Discouraged by the lack of any response on > microsoft.public.access.odbcclientsvr, I'm now cross-posting to [quoted text clipped - 44 lines] > UAC is turned off and Access is run as administrator. Turning off the > firewall and virus protection are of no help. OS is Vista Home Premium. In Vista, the protocol options are available when I click the Client Configuration button in the ODBC Data Source Administrator wizard. You should have a choice of either TCP or named pipes. Create a pass-through query in Access (I'm using 2007) with the following query that returns the protocol of the connection: SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID; Now with the pass-through query above, you at least know which protocol is being used. I received a TCP connection initially. I don't know if that's the default, or whether it was because I configured something earlier. At any rate, while you are connecting locally, you will probably get better performance if you choose named pipes and avoid the TCP stack. If you connect locally using named pipes and the SQL Server Native Client, I think you'll get a shared memory connection (a local named pipe) which should be the fastest connection.  SignatureRick Byham (MSFT) This posting is provided "AS IS" with no warranties, and confers no rights. > It sounds like Access could be using a different means of connecting to > SQL Server in those cases. Maybe when the network is disabled it uses [quoted text clipped - 54 lines] >> UAC is turned off and Access is run as administrator. Turning off the >> firewall and virus protection are of no help. OS is Vista Home Premium. Thanks, Rick. I hate to be dense, but I cannot find the ODBC Data Source Administrator in Vista Home Premium. At the control panel (regular view not Classic) there is an Additional Options view, but no Data Sources in it. How else can I navigate to it? I looked all through Control Panel for ODBC... or Data Sources... and couldn't find it. Must be there somewhere! I ran the pass-thru query and get TCP as the net_transport. Charax > In Vista, the protocol options are available when I click the Client > Configuration button in the ODBC Data Source Administrator wizard. You [quoted text clipped - 73 lines] >>> UAC is turned off and Access is run as administrator. Turning off the >>> firewall and virus protection are of no help. OS is Vista Home Premium. To find the ODBC Data Source Administrator: 1. In Vista, on the Start menu, click Control Panel. 2. In regular (not classic) view, click Administrative Tools. 3. Click Data Sources (ODBC), and then in User Account Control click Continue. Your existing data source name (DSN) is probably either a User DSN available only to you, a System DSN available to all users on your computer, or a File DSN, stored in a file that could be moved to another computer. Either find the DSN of interest and click Configure, or click Add to create a new one. As I play around with this more this morning, I realize that the Client Configuration option, which lets you select a protocol, is only available if you select SQL Server as your driver. But that's an older version of the driver (SQL Server 6 and SQL Server 7.0). That's the driver Access 2007 picked for me. To connect to SQL Server 2005, you should select SQL Native Client as your driver. In that case you won't be presented with the protocol option. You must use the SQL Server Configuration Manager to configure your protocol choices. I suspect you are currently using the old driver (called just SQL Server) and it has selected TCP/IP as your protocol. Your best connection would be to use the SQL Native Client driver. That will give you a named pipes (actually a local pipe using shared memory) by default when connecting to the local SQL Server. Rick Byham (MSFT) This posting is provided "AS IS" with no warranties, and confers no rights. > Thanks, Rick. I hate to be dense, but I cannot find the ODBC Data Source > Administrator in Vista Home Premium. At the control panel (regular view [quoted text clipped - 86 lines] >>>> UAC is turned off and Access is run as administrator. Turning off the >>>> firewall and virus protection are of no help. OS is Vista Home Premium. I'm about to chuck Vista out the Window. Admin Tools does not appear in the Control Panel, but I found it by going to Control Panel > Programs> Administrative Tools > Data Sources (ODBC) Once there, it is clear that I had no DSN data source for SQL Server established, so my original connection string is DSN-less. The string I have been successfully using for some years now looks like this: "ODBC;Driver={SQL Server};Server=PAVILION;UID=myusername;DATABASE=mydatabase;Trusted_Connection=Yes" If I change to a DSN, I'll have to rewrite all my ODBC linking code which will then not be usable for connecting to web-served databases when I get back to my home network. I appreciate the DSN approach as an interim solution for SQL Server local use on my traveling laptop, but isn't there a way I can continue to use DSN-less connections on Vista? I guess I'm restating the problem to "how does one get DSN-less connections to work reliably on Vista?" Cheers, Charax > To find the ODBC Data Source Administrator: > 1. In Vista, on the Start menu, click Control Panel. [quoted text clipped - 120 lines] >>>>> firewall and virus protection are of no help. OS is Vista Home >>>>> Premium. For the best connection string for a DSN-less connection to SQL Server 2005, switch providers to the SQL Native Client with the following: "ODBC;Driver={SQL Native Client};Server=PAVILION;DATABASE=mydatabase;Trusted_Connection=Yes" That should get you a shared memory connection through the newest provider. The SQL Native Client will be smart enough to use shared memory locally and TCP for a remote connection, without any hints. I also removed the UID=myusername since you are using a trusted connection. This is working for me, from Access 2007. If you want to keep using the older SQL Server driver, for a DSN-less connection, try connecting with named pipes by adding np: before the server name, as in: "ODBC;Driver={SQL Server};Server=np:PAVILION;UID=myusername;DATABASE=mydatabase;Trusted_Connection=Yes" Again, that may improve your performance when connecting locally. It's worth a try. SignatureRick Byham (MSFT) This posting is provided "AS IS" with no warranties, and confers no rights. Many thanks, Rick. I'll try these connection string options and report the results. Cheers, Charax > For the best connection string for a DSN-less connection to SQL Server > 2005, switch providers to the SQL Native Client with the following: [quoted text clipped - 13 lines] > Again, that may improve your performance when connecting locally. It's > worth a try. For TCP/IP, include the option (old) NETWORK=DBMSSOCN or (not so old) NETWORK=dpnetlib for named pipes, NETWORK=DBNMPNTW These words are actually the name of windows DLL's: the NETWORK option tells the SQL Server driver which network library to load. Other drivers (or new SQL Server drivers) might have the same feature, but use different words and libraries. (david) > For the best connection string for a DSN-less connection to SQL Server > 2005, switch providers to the SQL Native Client with the following: [quoted text clipped - 14 lines] > Again, that may improve your performance when connecting locally. It's > worth a try. Thanks, David. I'll play with these suggestions when I return home to my development environment. In the meantime, Rick's suggestion to use "SQL Native Client" for the driver has got my Access 2003 linked to SQL Server 2005 on Vista, so I'm back in business with my laptop. Cheers, Charax > For TCP/IP, include the option (old) > NETWORK=DBMSSOCN [quoted text clipped - 30 lines] >> Again, that may improve your performance when connecting locally. It's >> worth a try. Bingo! Success (I think).... I put Vista into the "bad" configuration (online connection to a dial-up ISP) and confirmed the bad performance with Access 2003 FE to SQL Server 2005 BE, using Driver={SQL Server} in the connect string. I fixed the startup code in Access to instead use your suggestion of Driver={SQL Native Client} to link to SQL Server, then shut Access down. When I restarted Access there was a small but noticeable decrease in time required to drop/reestablish ODBC links, and the performance of the ODBC linked tables behind a complex Access form with about 50,000 records is now excellent. Many thanks for the solution. I don't know if there are other implications to switching to the SQL Native Client driver, but for the time being my db is working as well as it did on Win XP. The real test will occur when I return home and test out the connections to the local and web SQL servers that I can't test for now, using Access 2007 (which I removed before my trip). Thanks again for the right answer to a nagging issue. Cheers, Charax > For the best connection string for a DSN-less connection to SQL Server > 2005, switch providers to the SQL Native Client with the following: [quoted text clipped - 13 lines] > Again, that may improve your performance when connecting locally. It's > worth a try. Thanks for asking this question. I've blogged it and updated the Access Performance FAQ. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > UAC is turned off and Access is run as administrator. Turning off > the firewall and virus protection are of no help. OS is Vista Home > Premium. You need something that monitors outgoing connection requests, I think. My software firewall will notify me and I can log connections specific to a particular application. I know that Windows firewall can't do either of those things, but maybe there's some free or trial software that someone could suggest that would do that. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.