Vista, ODBC and SQL Server 2005

[Charax]

[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

 

[Paul Shapiro]

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.

 

[Rick Byham, \(MSFT\)]

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

 

[Charax]

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

 

[Rick Byham, \(MSFT\)]

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

 

[Charax]

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

 

[Rick Byham, \(MSFT\)]

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=npAVILION;UID=myusername;DATABASE=mydatabase;Trusted_Connection=Yes"
Again, that may improve your performance when connecting locally. It's worth
a try.

 

[Charax]

Many thanks, Rick. I'll try these connection string options and report the
results.

Cheers,

Charax

 

[DAVID]

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)

 

[Charax]

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

 

[Charax]

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

 

[Tony Toews [MVP]]

Thanks for asking this question. I've blogged it and updated the
Access Performance FAQ.

Tony
--
Tony 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/