Access 2007->SQL Server2005 "connection was forcibly closed",GNE 1
Christian Coppes
with an Access 2007 application, I have a very big problem connecting an SQL
Server 2005.
The scenario:
- nearly 200 clients with Windows XP Professional (in an Active Directory
Domain)
- clients uses WAN, LAN and WLAN, different locations
- one SQL Server 2005 EE, uses actually 36 databases, the Access 2007
application uses 2 of them (one for testing, one for production)
- around 50 tables in the database the application uses
- changing number of clients using the SQL Server with this frontend at the
same time
- the clients are using Access 2007 runtime and a ACCDR (from ACCDE)
version of the frontend, I'm using a full version of Access 2007
The problem:
From time to time Access shows an error message while some of the tables
were left open:
[Microsoft][SQL Native Client]TCP Provider: An existing connection was
forcibly closed by the remote host.
(#10054)[Microsoft][SQL Native Client]Communication link failure (#10054)
It seems that the problem occurs more often if more clients are using the
same table at the same time.
I searched the Internet up and down for the problem and found some
solutions. One of them was, that there could be an issue with tables which
using Memo fields (any SQL Server type which is longer than 255 characters is
converted to "Memo" in Access). The most problematic table used 5 "ntext"
fields. Following this knowledge base article
http://support.microsoft.com/?kbid=942861
I programmed a solution to cut all fields in single 255 character strings
and save them separately into an extra table. This is working and so the
mentioned table doesn't have any ntext field anymore, only an int field which
targets to the MemoField table (consisting of an ID and a nvarchar(255)
column).
Unfortunately the solution didn't solve the problem.
The application uses two different methods to access the tables on the
server. First the tables are linked with a DSN less connection using the
following connection string:
ODBC;DRIVER=SQL Native
Client;SERVER=FullQualifiedServername\Instance;UID=Username;PWD=Password;APP=2007 Microsoft Office system;DATABASE=DatabaseName;
The second method is inside the VBA application where ADO is being used to
access queries and tasks like INSERT, UPDATE and so on. This is be done with
the following connection string:
strConnectionStr = "Provider=SQLNCLI;" & _
"Server=" & cActualDBServer & ";" & _
"Database=" & cActualDatabase & ";" & _
"UID=" & cActualDBUser & ";" & _
"PWD=" & cActualDBPassword & ";"
where the constants for the right parameters are set elsewhere in the
application with the same contents as in the other connection string.
As I'm only database owner on the SQL Server I unfortunately cannot run any
tests there, I have no access to the SQL Profiler, Logs, Activity Monitor or
Performance Monitor so I cannot test anything there.
As a test alternative I installed an SQL Server Express 2005 in a Windows XP
virtual machine on my computer which has the same configuration as the
standard clients. I copied anything of the normal SQL Server to this smaller
version and relinked a test version of the application to this. Anything else
is the same here.
I tried to run a Performance Monitor Log here and started 5 clients to use
this database. Then I opened any table without starting the application ( the
VBA part) only using the Navigation pane of Access, with all of the 5 clients.
What I saw on the Performance Monitor is that each opened table creates a
new connection on the SQL Server (I'm not sure if this is part of the
connection pooling or if these connections are separated connections). So if
I open 30 tables with the Navigation pane, I got 30 connections on the SQL
server. Every new client opens again 30 connections if I open 30 tables
there. Every client uses the same user and password using SQL authentication
and the same connection string so I would expect that they uses connection
pooling but as mentioned, I'm not sure and I don't know how to test that.
I saw other effects:
- opening a table in the Navigation pane works very fast, opening a view
which uses the same table on the SQL server has a big delay in execution (but
this is maybe an effect of having all on the same computer). The VMWare
player is accessed through network with an own IP, as the big SQL Server.
When I open a view as recordsource with a form it is normally faster than
using a linked table as recordsource.
- if I let the tables opened in all clients and simply wait, the
connections in the Performance Monitor are closed after around a minute
(which is what I expected as I read that this is the normal behaviour of SQL
Server to close not used connections after a minute). They are not reopened
if I go to one of the opened tables and refresh them.
- if I reach around 96 connections, every further try to open another table
runs with more delay. Views are very slow.
- if I come to more than 100 connections (which is no border, it opened 115
connections without any error) views cannot be opened anymore, after waiting
Access shows an ODBC timeout error. Looking in the SQL Server Express logfile
it shows the error, too:
Error: 8645, Severity: 17, State: 1. A time out occurred while waiting for
memory resources to execute the query. Rerun the query.
I guess this is simply too much for one computer running VMWare, SQL Server
Express and 5 clients opening 30 tables each...:)
But: It is not the error I got with the "big" SQL Server.
My guess was, after the thing with the Memo fields was not solving the
problem, that the problem has something to do with the connection - that all
users connects with the same credentials. The above test shows that it
normally couldn't have anything to do with the number of connections to the
server. If the standard "Max Pool size" is 100 it would mean that the
application would have crashed trying to use more than that. 115 connections
shows, that it worked with the simple Express Edition. The EE Server has 36
databases with an unknown number of users working with it. As both server
versions can use a maximum of 32767 connections (I checked the setting on
both servers) I don't believe that our application will get the EE server
above this number of connections. In my test I opened 30 of 50 tables at the
same time - but if the application is started normally no user uses more than
5 of them at the same time. With 200 users I don't believe that the number of
connections will go over 1000 connections at the same time.
The server will get a priority handling on all the switches in near future
to make it faster. But I don't think this will solve the problem.
Other facts:
- the problem most often occurs with the above mentioned table and nearly
no other table. It is used by around 20 users at the same time
- the complete database was on a SQL Server 2000 EE before with the same
settings without any problems
- the connection string used the older OLE driver before, for testing
purposes I now use the SQL Native client on all clients (installed this
driver on all workstations) - the problem is the same.
- the problem occurs if I only open the table and let it be opened with the
Navigation pane of Access while other users are working with the application.
If I'm alone (or only a few clients are online) the problem doesn't occur
that much or disappears completely until more users are online again (the
problem occurs in a form which uses this table in a running application, too).
- as there are a lot of other users/databases on the same server I don't
think that the server has capacity problems with network bandwidth or other
such things.
- as the problem only occurs with the Access ODBC connection (recordsource
of a form uses the linked tables in any case, a view is a linked table in
Access and linked tables can only be linked with ODBC driver) I guess that
the problem has something to do with ODBC (the error I wrote above is an ODBC
error). If I use SQL Management Studio (2005/2008 Express) I never got any
error accessing any table or view. As far as I see no error occured accessing
the database through ADO commands with the other connection string.
Thanks for reading this very long article until here...:)
If anyone has any idea what could be the reason for this annoying error it
would be great if we could discuss it.
Cheers,
Christian
Mary Chipman [MSFT]
you would use it if the tables were local Jet/ACE tables running on
the client machine or a LAN. Once you start round tripping over the
network and holding tables/connections open, you cut down drastically
on the scalability of your application and limit the number of
concurrent users you can support. Check out this whitepaper
Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx
A couple of quick notes -- don't use ADO recordsets for modifying
data. Guaranteed slowest way of doing anything. Use pass-through
queries for fetching read-only data -- holds no locks on the server
(great for reports). Cache static data in local tables for combo and
listboxes. Basically you want to tread as lightly on the server as
possible -- imagine that you are creating a Web application, not an
Access application, and you'll be on the right track. If you go for
read-only data access and use stored procedures to post updates, an
Access client can be used to support an infinite number of concurrent
users. Not that you'd necessarily want to go to that much work (see
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446 for the gory details), but it's
possible.
--Mary
Erland Sommarskog
> The problem:
> From time to time Access shows an error message while some of the tables
> were left open:
> [Microsoft][SQL Native Client]TCP Provider: An existing connection was
> forcibly closed by the remote host.
> (#10054)[Microsoft][SQL Native Client]Communication link failure (#10054)
It is always a good idea to look at the SQL Server error to see it
there are any messages there related to the error message in Access.
This could be due to some error on the SQL Server side. It may also be
someone using the KILL command...
> I open 30 tables with the Navigation pane, I got 30 connections on the
> SQL server. Every new client opens again 30 connections if I open 30
> tables there. Every client uses the same user and password using SQL
> authentication and the same connection string so I would expect that
> they uses connection pooling but as mentioned, I'm not sure and I don't
> know how to test that.
I don't know how that works in Access, but the connection has to be
closed, or else it cannot be returned to the pool.
> (which is what I expected as I read that this is the normal behaviour of
> SQL Server to close not used connections after a minute).
Just a small correction here: it's the client API that closes the
connection, not SQL Server.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Christian Coppes
thanks for the informations, I already read this document, it's very helpful.
The number of connections couldn't be the issue with this as the same
database with the same number of users worked without any problem on a
Enterprise version of SQL Server 2000. Today the admins told me that the new
one (SQL Server 2005) is a cluster of servers so they should have no problems
with network problems. Especially the problem occurs in LAN, not in WAN (as
fas as I know) so the bandwidth should not be the problem.
After reading a lot today I think the highest chance is that it has indeed
something to do with the network but I cannot proof that, I've no access to
router logs.
I did a lot of further tests today after I got more rights on the server.
1. I tried to use Performance Monitor, it shows that the number of
connections to this server (including all 36 databases) is around 200
connections so this should not be a problem. I tried to check the TCP
"Connection Reset" and "Connection Failure" (and so on) counters, they are
showing around 586,000 connections active and 402 connections established,
7553 connection failures and around 801,000 connection resets. I'm not sure
if this says something about the quality of the network packets.
Moreover I tried User errors/sec, which rarely shows one or two errors (not
knowing where they come from so the value is not very helpful) but it seems
that it has nothing to do with the time when the error in Access appears.
And I tried Batches/sec, which shows average 140 running batches, sometimes
jumping over 4000. So it doesn't look like the cluster is too heavily used.
2. I tried to use the SQL Server Profiler to check what happens if I open a
table and wait until the error occurs. The only thing the Profiler says is
"Audit Logout" in the moment the error occurs in Access. So it seems that the
connection is terminated elsewhere and SQL server releases the connection
because it sees that the connection is not used anymore (my guess). No error
message (or maybe I have not the right object in the trace to show me such an
error).
3. After I got access to the Activity monitor I looked for the connection
which had the error (filtered on my machine) and after the error occurs the
monitor shows "ASYNC_NETWORK_IO" as wait type and status "Suspended". If I
doubleclick on "command" to see what was last executed it shows me a select
to a completely different table I never touched in this client since starting
it...strange... (for testing purpose I changed this one client to another SQL
server user login to be sure that it is not mixed with other running clients).
If I open the table after waiting a while (a few minutes) the message in the
activity monitor is the same even if there is no error in Access.
I asked to get a SQL Server log file to make further investigations because
all this doesn't seem to give me any result for the error.
Yes, you're right, there are a lot better methods of accessing an SQL server
but unfortunately I have no choice because the application was not made by
me. It started as a simple Access database and was made by someone who is not
a database programmer, I solved a lot of issues with this and already
recommended to start over again completely with a new client, but it seems
that the application will not run long enough anymore (should be exchanged
with other software in a few years) so there's no time for rearrange anything.
Thanks for your posting,
Christian
Christian Coppes
thanks for your reply.
I don't believe that someone uses a Kill command..:)
That would mean, every 5 minutes for any of the running clients someone
would kick out them...
I got further access rights to the server and made some further tests
(please see the response I wrote to Mary for details). I don't have access to
the SQL Server log, but I asked an admin to copy me one today, I hope this
will shed more light on the problems...
As I wrote to Mary the SQL Profiler shows a simple "Audit Logout" in the
moment when the error occurs.
I don't know if it is the ODBC driver which closes the connection (closes it
because it looses IP connection maybe). I guess it must be an error directly
from the ODBC driver because I cannot catch this error with the Access error
object (that would be of great help, I could simply reestablish a connection
if the error occurs).
Cheers,
Christian
Erland Sommarskog
> thanks for your reply.
> I don't believe that someone uses a Kill command..:)
> That would mean, every 5 minutes for any of the running clients someone
> would kick out them...
You know, some admins are EVIL. :-)
OK, to be serious it is not likely that that is the cause, but you
never know. Anyway:
> As I wrote to Mary the SQL Profiler shows a simple "Audit Logout" in the
> moment when the error occurs.
That smells like a network problem, as you discuss in your reply to Mary.
It's a bit funny, though, that it is correlated with many connections
and many open tables. But maybe some switch or router gets overloaded.
Or maybe you need many connections to use that faulty switch in the
dusty corner of the rack.
> I got further access rights to the server and made some further tests
> (please see the response I wrote to Mary for details). I don't have
> access to the SQL Server log, but I asked an admin to copy me one today,
> I hope this will shed more light on the problems...
xp_readerrorlog may help, even if you don't have access to the disk.
> I don't know if it is the ODBC driver which closes the connection
> (closes it because it looses IP connection maybe). I guess it must be an
> error directly from the ODBC driver because I cannot catch this error
> with the Access error object (that would be of great help, I could
> simply reestablish a connection if the error occurs).
A way to test what happens is to simply pull the network cable from
a client, and see what this results in. If you get the same error
message, I would be quite sure that it is a network problem.
Now, you only have the challenge to convice the network admins...
Christian Coppes
OK, but I don't think we have any "bastard operators from hell" here...if
you know "him"...:-)
But maybe the "faulty switch in the dusty corner of the rack" is the
problem...:-))
What I saw when I started the profiler and the activity monitor first
without filtering the output to my database is that this application creates
more workload than all the other 34 databases on the server together. So I
think the statement of the admin "all other databases on the server doesn't
have such problems" not only has something to do with their used clients
(whatever they are but surely not Access) but with the fact that they don't
use much resources on the server. The Access application uses a lot of
connections and so maybe the network must be tested against this new network
load.
The most complicated thing is to get someone who is willing and able to do
that...
Hope I get at least the SQL Server log today to look if there is something
useful inside. I will write more if I know more about this.
Cheers,
Christian
Christian Coppes
I followed your recommendation and testet to cut off the network connection
in a running client. The result is that Access only shows "ODBC--call failed
Communication link failure (#0)". Next is that Access crashes completely
("Microsoft Office Access has encountered a problem..."). So this is not the
same behaviour as with the reported error. So I guess that this is not a lost
connection to the server, it means what it said: The connection was forcibly
closed by the host. Some service, whatever it is, stops the connection and
gives this error back to the ODBC driver. Access is going on working but
shows the "#Name?" in all fields. And it can go on working after a while. On
the other side if the network is cutted off Access is not able to work
anymore (an annoying error because this happens by simply setting a laptop to
sleep mode with closing the laptop and for this problem I don't have a
solution but that's another story...).
So my guess is that it has something to do with any form of too many network
packets which lets some network device throttle the network IO down.
Difficult to find that out in a wide network with many network components...
Cheers,
Christian
Mary Chipman [MSFT]
for a few more years until you can switch to a more appropriate
client. It's not an uncommon situation that applications started by
amateurs can grow to be unmanageable, but sometimes they can be
cleaned up by eliminating extra layers and simplifying data access by
substituting set-based operations (T-SQL, local queries, pass-through
queries) for client code. Take a hard look at the ADO code -- are you
leaking resources by not closing connections cleanly? As was pointed
out elsewhere, connections need to be returned to the pool to be
reused. Access was never intended to work out of the box with SQL
Server, although it often ends up in that space.
--Mary
On Wed, 19 Aug 2009 08:49:02 -0700, Christian Coppes
Erland Sommarskog
> I followed your recommendation and testet to cut off the network
> connection in a running client. The result is that Access only shows
> "ODBC--call failed Communication link failure (#0)". Next is that Access
> crashes completely ("Microsoft Office Access has encountered a
> problem..."). So this is not the same behaviour as with the reported
> error. So I guess that this is not a lost connection to the server, it
> means what it said: The connection was forcibly closed by the host. Some
> service, whatever it is, stops the connection and gives this error back
> to the ODBC driver.
Of course the difference is that when you pull the cable all connections
go down. Then again, if there is a faulty network thing somewhere, this
is exactly what would happen.
I've think I've seen this error when I have been running a query-stress
tool which hammers the server with request, and I think has been when
I have had the client and SQL Server on a different machines. I've then
only had a few of them.
Something occurred to me, and I went on a Google search the error
message. The culprit may be in Windows. What is the SQL Server machine
running? Windows 2003 has a protection against something known as a
"SYN flooding attack", and maybe your Access application is taken for
one. (Now, is that a sign that you need to redesign or what? :-)
Look here, and see if you can get the admin to add this to registry
on the SQL Server box:
http://technet.microsoft.com/en-us/library/cc781167(WS.10).aspx
Christian Coppes
"Erland Sommarskog" wrote:
Thanks for the hint, I already read about this and wrote this to the admin
but have no reply until now. I'll ask him again.
What I did today: I downloaded "TCPView" from Sysinternals. Here's the link:
http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx
I tested the application again using this tool and opened all linked tables
one by one as fast is I could click. The result look like this for SQL Server
2005:
MSACCESS.EXE:3264 TCP MyComputer:1342 SQLServer2005:2594 ESTABLISHED
[System Process]:0 TCP MyComputer:1347 SQLServer2005:2594 TIME_WAIT
(changed the full qualified names in the list).
The most processes are opened like the first line, some are opened as
"System Process" with "TIME_WAIT". After a certain timeout (which I can
extend by using the following Registry hack:
http://www.pctools.com/guides/registry/detail/878) the "TIME_WAIT" processes
will be stopped and removed. If they all have been removed, the MSACCESS
processes begins to being removed. Result is, after this happens the first
time the error occurs in Access. It doesn't stop here, they are being removed
until nearly 10 processes left, after this it takes longer until the next is
being removed.
Result is in all cases that every time a "MSACCESS" process is being removed
one error comes up in Access.
I tried this with OLEDB (normal Windows SQL Server driver) and SQL Native
Client, the error occurs in both cases so it's not a special driver problem.
Then I tried to use the older SQL Server 2000 which was used before for the
same application. Nearly everything is the same here, same tables,
structures, access rights and so on. I tested the same as above with this
one, it looks like this in TCPView (only two samples, as in the example above
you must think of about 25 entries in this way):
MSACCESS.EXE:3744 TCP MyComputer:1407 SQLServer2000:ms-sql-s ESTABLISHED
[System Process]:0 TCP MyComputer:1408 SQLServer2000:ms-sql-s TIME_WAIT
The behaviour is the same as in the above case: After opening some of the
tables with doubleclick, the first tasks were listet like the first line
above, all following as "TIME_WAIT" like the second line.
And as above after a special timeout the "TIME_WAIT" processes were removed
one by one. After another timeout, the MSACCESS processes were removed one by
one until around 10 left - same as above. But the difference here: No error
is shown, all opened tables are accessible as before.
I checked the configuration with SSMS and the only difference I could find
is that SQL Server 2000 has a Max Worker Thread value of "255" and SQL Server
2005 a value of "0". Following the books online guide I saw that this is the
value of the maximum number of threads which SQL server works on at the same
time, independent how much connections are made to the server. And that it
could be better to set a special value instead of "0" which means the number
is handled dynamically internal. I don't know if this is the reason, I wrote
it to the admin now.
Next I saw is the Configuration Manager of SQL Server tools, there are the
protocols described. Here is the possibility to set values for "Keep alive"
and "Keep Alive internal", maybe these values can help adjust the problem? I
wrote the admin to compare these values.
And I saw comparing the trace of TCPView above that SQL Server 2005 always
connects to port "2594" and SQL Server 2000 to port "ms-sql-s". Does this
mean that 2005 is using TCP and 2000 is using named pipes? Can that make a
difference and can the order of the used protocols in Configuration Manager
be a problem? As there are 34 other databases on the server it should not
create a problem to all the other clients...:-)
Yesterday I made a trace with Wireshark to test the network traffic between
both computers, but I couldn't find anything what could be helpful. I'm not
so deep in analyzing traffic to say if there's something problematic in there.
I could see that there is a regular "TCP Keep-Alive" traffic as expected.
From time to time a "TCP ZerWindowProbe" occurs and a lot of acknowledge
packets on the way to the server, I guess these are all the network
components on the way to the server.
I also switched off all other network tasks on my computer, Symantec
Altiris, Antivirus and Windows Firewall, didn't change the problem.
And I was in contact with someone of the network hardware support who helped
me testing the hardware environment on the way to the server to check if
there are any problems but there was no error in the time when my Access
error occured. So I don't think that it is a network hardware problem, I
guess it is a SQL Server configuration or TCP configuration problem on the
server.
It's OK that you and Mary want to show me the advantages of "real" clients -
but I don't think it's a solution...:-)
Fact is, Microsoft has made Access as a possible SQL Server client and the
ODBC- method of accessing SQL Server is not only made for Access, it is made
for all client applications which want to get access to any sort of database
server. This is working since years in many versions of Access and other
software, and the SQL Server 2000 in my case prooves that it is a possible
and good solution to create stable applications - this server never had such
problems using the same client. And I don't believe that SQL Server 2005
(which is on the market since four years now) is a step backward...
Moreover in me search for this problem I found a very high number of
programmers who tried to find a solution for this problem using ADO.NET
applications or Java, even the same problem when accessing MySQL server with
Java, ASP, IIS, whatever. So this is not really a problem of the used
programming language, client or server, it is a communication problem between
them and needs exact investigation to find the special reason - in all of the
found cases. The number of possible solutions I found (but unfortunately
don't helped in my case) shows that I'm not alone with this. The
documentation about this problem is very poor from Microsoft, they say
something like "yes, it means it is a network problem", and some independent
knowledge base articles (like the one you showed above) which are only
special solutions in special cases.
Yes it is impossible to show all possible solutions for all cases as there
are too many possibilities but a LITTLE more investigation tips would be very
helpful..
And my last test: I created an Access project connection to the 2005
database. Access projects doesn't use ODBC to connect, they work directly
with the SQL Server without any use of the Jet DB machine Access normally
uses.
If I make the same test as above, opening all tables I could find on the
server (a lot more than used in the application) to which I have access to,
the result in TCPView is that only three connections are made, one I guess is
always for Access internal use as this happens with ODBC, too.
I can open and close them as fast as I can, I can see that one of the three
processes is "TIME_WAIT", too, it ends after the timeout like before but then
only these two connections are there and works stable. No problem.
I know that ADP is not preferred by Microsoft - but I don't know why because
in my opinion it is the very best, stable and fast method to access a server
- because it uses the server directly and no sensefree try to translate Jet
SQL into "real" SQL with complete other data types and programming methods.
The only problem I found until know is the fact that in every form the
records are delimited to 1000 records. This can be adjusted but you must
change it in any form and what if you need more than the limit you once
configured? That makes no real sense for me. That's the only thing Jet can do
better by using dynasets which only retrieves as many records as being
displayed on the screen.
But to come back to the problem: No, unfortunately I cannot change the
application to an ADP version, because it would take more time to do that
than the expected lifetime of the application is. In the time the application
has from now on the programming time must be used to make it better wherever
possible, no time is given to change it into a real database application.
The following product is planned to be a SAP application which has nothing
to do with this server or application. So there will be no real "follow-up".
That brings me back to my problem, I must find a solution for this in the
way it uses the server and SQL Server 2000 shows that it is possible so it
must be possible for SQL Server 2005 - my opinion.
Thanks for any help and patience...:-)
Christian
Christian Coppes
that's what my job is with this application: Make it better, change it to a
database application. Before I began to work with it it didn't use any ADO
code, everything was made with very Access like methods like "DLookup", DAO,
saved queries and so on. All tables were linked with a System DSN, I changed
it to use DSN less method. I moved every table I was able to move to the SQL
Server to make it multi user accessable and a lot more such things. As it was
an Access database in former times this was a lot of work which took me
months.
I think there will be a lot of errors and problems in the code including not
closed connections - but all of these errors are in the version which used
the SQL Server 2000 (see my reply to Erland about this) and everything worked
here.
As I reported: The problem occurs and is reproducable with only Access is
open and only some tables will be opened by using doubleclick on the table in
the Navigation pane of Access - that means, no part of the application is
responsible for the General Network Error, it is anywhere in the
communication between ODBC (OLEDB and Native Client, both the same) and the
SQL Server 2005. After all my tests I wrote in the answer to Erland I'm sure
know that this is a problem with some configuration.
Your last statement simply is not true: Access was made for direct access to
most databases but especially to SQL Server since at least Access 97. Since
then Access supports the creation of Access projects which directly uses the
SQL Server without any use of the Jet engine. And Access is a very powerful
client for programming easy, fast and good applications. Dot Net is the first
library which is capable of being better but it's not so easy and fast to
create an application with this. There's a lot more work needed and more work
needs more time so in many cases Access as a well-known application is the
better solution to get a middle-sized database up and working than any other
client. This means of course good planning and good knowledge of database
technology but this is the basis in all other cases, too. You can create
shitty programs in every language, no problem...:-)
Cheers,
Mary Chipman [MSFT]
being used out of the box with SQLS -- I actually wrote the book on
how to do it successfully -- http://www.amazon.com/dp/0672319446 :-)
What I meant was that opening entire linked tables has consequences
that opening a table in Jet doesn't have -- network bandwidth, server
load, potential locking/blocking/deadlock issues, so the
out-of-the-box behavior of basing forms and reports by specifying a
table name or a "select * from" query causes problems with SQLS that
do not exist when Jet/ACE is used as the data store. Access caches
data on the client computer when used with Jet, which is not possible
with server data--locks are held on the server in most cases. You can
cache static data in local Jet tables for using with combo/listboxes
and subforms, and you will get the best performance and have fewest
problems if you fetch data for browsing read-only by means of a
pass-through query, and fetch only single records for editing (use
query-by-form techniques to filter data).
It's a drag that you can't get a Profiler trace going so that you can
see for yourself what's going on at the server. I know what a drag it
can be to try to fix something when you don't have all the information
you need.
--Mary
On Fri, 21 Aug 2009 06:21:01 -0700, Christian Coppes
Christian Coppes
ah OK...If I would have known that I wouldn't have wrote so much about
it...crashing through open doors...:-)))
In the meantime I got access to the Profiler, the Activity Monitor and
Performance Monitor of Windows so I could test a lot more. I saw what you
mean, it's very much overhead Access produces to open a simple select. I will
do my best to change this whereever I can.
Amazon didn't tell much about the contents of your book. If it is not about
"How do I move my mouse to open a form?" (which are often theme in books
saying it is for professionals) then maybe I can learn from it and find some
good tips? In most cases things that were good for older versions of
Access/SQL Server are good for new versions in the same way, I often buy
older books if they are good.
And to come back to my problem: I think with help of the SQL Server admins
it should be possible to find the reason for the ODBC problem. It would be of
help if someone has experience in the settings of SQL Server (the admin
maintainable settings) or TCP settings maybe from Windows. I have not so deep
knowledge about the transport protocols and all their settings. Unfortunately
the admins seem not to be specialists in SQL Server nor very deep in Windows
so I must find the problem lastly on my own.
Erland Sommarskog
> And I saw comparing the trace of TCPView above that SQL Server 2005 always
> connects to port "2594" and SQL Server 2000 to port "ms-sql-s". Does this
> mean that 2005 is using TCP and 2000 is using named pipes?
No, I think ms-sql-s means that SQL 2000 is using port 1433, which is
the default port for the default instance. That is, ms-sql-s is a
registered name for port 1433. In the same vein, I guess that TCPVIEW
does not present port 25 as 25, but as "smtp". (I don't have TCPVIEW
here, but I would not be surprised if there is an option to change
this.)
Are SQL 2000 and SQL 2005 running on the same Windows box, or on
different ones? If they are on different, there could be Windows
differences. If they are on the same box, it's getting a little more
intriguing.
In any case, since SQL 2005 runs on port 2594, this indicates that it's
a named instance. This means that there is one more player in the
game, the SQL Server Browser service. To be able to connect the name
instance, the client first connects to the Browser Service on UDP port
1434, to get the port number for the instance.
If you change the connection string to read server,2594 rather than
server\instance, you will not make the connection on port 1434. Not that
I see that it has anything to do with it, but it is worth trying.
William Vaughn (MVP)
your problem--at least I expect it is.
http://betav.com/blog/billva/2008/11/solution-forcibly-closed-sql-s.html
hth
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C6EF0234...@127.0.0.1...
Christian Coppes
"Erland Sommarskog" wrote:
> Christian Coppes (Christi...@discussions.microsoft.com) writes:
> > And I saw comparing the trace of TCPView above that SQL Server 2005 always
> > connects to port "2594" and SQL Server 2000 to port "ms-sql-s". Does this
> > mean that 2005 is using TCP and 2000 is using named pipes?
>
> No, I think ms-sql-s means that SQL 2000 is using port 1433, which is
> the default port for the default instance. That is, ms-sql-s is a
> registered name for port 1433. In the same vein, I guess that TCPVIEW
> does not present port 25 as 25, but as "smtp". (I don't have TCPVIEW
> here, but I would not be surprised if there is an option to change
> this.)
>
> Are SQL 2000 and SQL 2005 running on the same Windows box, or on
> different ones? If they are on different, there could be Windows
> differences. If they are on the same box, it's getting a little more
> intriguing.
>
> In any case, since SQL 2005 runs on port 2594, this indicates that it's
> a named instance. This means that there is one more player in the
> game, the SQL Server Browser service. To be able to connect the name
> instance, the client first connects to the Browser Service on UDP port
> 1434, to get the port number for the instance.
>
> If you change the connection string to read server,2594 rather than
> server\instance, you will not make the connection on port 1434. Not that
> I see that it has anything to do with it, but it is worth trying.
Yes, you are right: The SQL Server 2000 runs without a named instance, the
SQL Server 2005 has one. I think it makes sense that this "ms-sql-s" is a
replacement of TCPView for the standard port, I don't know. But good to know
that it is possible to access the SQL Server directly without using the
browser service, I didn't knew that this is possible. I will test that
tomorrow when I've access to these servers again, thanks for the idea.
And as far as I know they are running on different Windows systems, moreover
SQL 2005 runs on a cluster and with VMWare, I think SQL 2000 does not but I
must ask for this.
Thanks, I will do further tries..:-)
Christian
Christian Coppes
thanks for your hint, I already found your blog post when researching for
the problem and checked this on both servers, unfortunately the maximum
connections setting is 0 in both cases so I cannot reach anything with
flowers and candy...:-)
Moreover I tested this in another environment with Access 2003 and SQL 2005
Server with AdventureWorks: If I cut the maximum number of connections to for
example 20 connections and try to open more than 20 tables with Access the
SQL Server simply blocks any further try to open another table until I close
one. So this cannot be the reason for the cutting of the connection. What
happens here is that I CAN open all the tables of the database, no problem.
As written before to Erland the tool TCPView from Sysinternals shows that all
connections are made, some with "TCP_WAIT". But I can see the contents of all
opened tables in Access. Then by simply waiting some minutes I can see with
this tool that the connections are cutted one by one, first the "TCP_WAIT"
and then the "ESTABLISHED" ones. And if this happens, the error occurs on
Access. So there must be some software component which cuts the connections
actively. This doesn't happen with the SQL Server 2000 but with SQL Server
2005. If it is not any setting on the SQL Server it must be in Windows - or,
what I cannot exclude at the moment, with some VMWare driver or cluster
problem because as far as I know the SQL Server 2005 is running on VMWare ESX
server with a Windows cluster.
Cheers,
Christian
Erland Sommarskog
> Yes, you are right: The SQL Server 2000 runs without a named instance,
> the SQL Server 2005 has one. I think it makes sense that this "ms-sql-s"
> is a replacement of TCPView for the standard port, I don't know. But
> good to know that it is possible to access the SQL Server directly
> without using the browser service, I didn't knew that this is possible.
> I will test that tomorrow when I've access to these servers again,
> thanks for the idea.
Since you say that the connections are made, and then broken, I doubt
that it matters. But it's always worth trying.
> And as far as I know they are running on different Windows systems,
> moreover SQL 2005 runs on a cluster and with VMWare, I think SQL 2000
> does not but I must ask for this.
OK, so there may be difference between the Windows setup. And now you
mention VMware, means that VMWare or the host OS also could be the culprit.
The plot thickens...
(That is, I have no clue of what is going on.)
Christian Coppes
"Erland Sommarskog" wrote:
> Christian Coppes (Christi...@discussions.microsoft.com) writes:
> > Yes, you are right: The SQL Server 2000 runs without a named instance,
> > the SQL Server 2005 has one. I think it makes sense that this "ms-sql-s"
> > is a replacement of TCPView for the standard port, I don't know. But
> > good to know that it is possible to access the SQL Server directly
> > without using the browser service, I didn't knew that this is possible.
> > I will test that tomorrow when I've access to these servers again,
> > thanks for the idea.
>
> Since you say that the connections are made, and then broken, I doubt
> that it matters. But it's always worth trying.
>
> > And as far as I know they are running on different Windows systems,
> > moreover SQL 2005 runs on a cluster and with VMWare, I think SQL 2000
> > does not but I must ask for this.
>
> OK, so there may be difference between the Windows setup. And now you
> mention VMware, means that VMWare or the host OS also could be the culprit.
> The plot thickens...
>
> (That is, I have no clue of what is going on.)
I tried to access the server directly with the port number but I can't
create a linked table with that setting. The error is:
SQL Server Error: 53
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [53]
Connection failed:
SQL State: '08001'
SQL Server Error: 53
[Microsoft][SQL Native Client]An error has occured while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections.
Connection failed:
SQL State:'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I guess that's a security setting to make sure that nobody tries to use the
port setting without going through the SQL Browser.
I will do further tests today.
Cheers,
Christian
Mary Chipman [MSFT]
much as you can, get the low-hanging fruit insofar as best practices
are concerned (the things already talked about in this thread). The
book is still in print because we wrote a lot of original code for
creating a lightweight front end that is still valid. Even though the
versions of Access and SQLS covered are old, the underlying
architecture hasn't changed as far as ODBC and data access is
concerned (ignore the chanpter on ADPs--that material is mostly
useless). Feel free to contact me if you have any specific questions
that are not of general interest to the newsgroups -- mary.chipman at
microsoft.com.
--Mary
On Fri, 21 Aug 2009 14:09:01 -0700, Christian Coppes
Christian Coppes
"Christian Coppes" wrote:
Today I went to the "holy halls" of the server admins...:-)
So what I can say now is:
1. it is not running on VMWare, but on a Windows cluster.
2. the admin added the Microsoft recommended setting regarding
SynAttackProtect, this setting was not in the registry (from link:
http://technet.microsoft.com/de-de/library/ms189083(SQL.90).aspx)
3. he changed a setting regarding TCP Chimney but I don't know which one and
he was in hurry. But he changed it by himself so I think he saw that there is
a wrong setting.
4. he changed the maximum worker thread from 0 to a higher value higher than
255. (The machine has 8 processors.)
As the SynAttackProtect setting wasn't in the registry I guess it is a good
possibility that this was the problem. He will restart the server this night
and then we will see what happens tomorrow.
Cheers,
Christian
Christian Coppes
this is now my plan "B". As I wrote in my answer to Erland some settings on
the server could be done and maybe these were the problems. The admin will
restart the server tonight and I'll see the result tomorrow.
In the meantime I began to create an Access project and tried to change the
forms to the direct use of SQL Server - a lot of work to do, but maybe a
possible plan "B" so that I can change at least the forms which causes the
most problems and the people in these departments can get a "light" version
with only these forms working with ADP.
And thanks, maybe I come back to your offer to write you later.
William Vaughn (MVP)
is playing a role here?
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
"Christian Coppes" <Christi...@discussions.microsoft.com> wrote in
message news:75447830-75B0-485D...@microsoft.com...
Erland Sommarskog
> I tried to access the server directly with the port number but I can't
> create a linked table with that setting. The error is:
> I guess that's a security setting to make sure that nobody tries to use
> the port setting without going through the SQL Browser.
was not correct.
But it is true, that if the server is configured to use dynamic ports,
the port could shift if the server is restarted.
Anyway, I don't believe that bypassing Browser would help.
Let's see tomorrow if the other actions of your admin had any effect.
Christian Coppes
"Erland Sommarskog" wrote:
> Christian Coppes (Christi...@discussions.microsoft.com) writes:
> > I tried to access the server directly with the port number but I can't
> > create a linked table with that setting. The error is:
> >...
> >
> > I guess that's a security setting to make sure that nobody tries to use
> > the port setting without going through the SQL Browser.
>
> No, there is no such setting. I would guess that your connection string
> was not correct.
>
> But it is true, that if the server is configured to use dynamic ports,
> the port could shift if the server is restarted.
>
> Anyway, I don't believe that bypassing Browser would help.
>
> Let's see tomorrow if the other actions of your admin had any effect.
I used this as server string: "SERVER=Servername:2594", maybe I'd better
used "\2594"?
But anyway, good news: The problem seems to be gone. Here are the settings
which were made at the server:
TCP Settings:
In Registry Key "HKLM\System\CurrentControlSet\Services\Tcpip\Parameters"
EnableRSS DWORD 0
EnableSecurityFilters DWORD 0
EnableTCPA DWORD 0
EnableTCPChimney DWORD 0
SynAttackProtect DWORD 0
And the "Maximum worker threads" setting was set to 288 instead of 0 (8 CPU
server, can be different on other machines having more or less CPUs, see
here:[url="http://msdn.microsoft.com/en-us/library/ms187024.aspx"]Maximum
Worker Threads Option[/url]).
After setting these values on the server and the server's Windows all
problems were gone (rebooting of Windows is necessary)!
As all the settings were made at the same time I'm not really sure what was
the problem. I guess it was the SynAttackProtect setting, but maybe these
settings help someone who has the same problems.
Thanks for all hints and tips to all of you. Now I can go on to do what's my
job here: Making the database better...:-)
Cheers,
Christian
Erland Sommarskog
> I used this as server string: "SERVER=Servername:2594", maybe I'd better
> used "\2594"?
Neither. "Servername,2594" is the sytnax.
> But anyway, good news: The problem seems to be gone. Here are the settings
> which were made at the server:
>
> TCP Settings:
> In Registry Key "HKLM\System\CurrentControlSet\Services\Tcpip\Parameters"
> EnableRSS DWORD 0
> EnableSecurityFilters DWORD 0
> EnableTCPA DWORD 0
> EnableTCPChimney DWORD 0
> SynAttackProtect DWORD 0
>
> And the "Maximum worker threads" setting was set to 288 instead of 0 (8
> CPU server, can be different on other machines having more or less CPUs,
> see
> here:[url="http://msdn.microsoft.com/en-us/library/ms187024.aspx"]Maximum
> Worker Threads Option[/url]).
>
> After setting these values on the server and the server's Windows all
> problems were gone (rebooting of Windows is necessary)!
Great to hear! You seem to have put in an immense lot of work to
track this down. I am glad if my moderately initiated postings have
been helptul.
> As all the settings were made at the same time I'm not really sure what
> was the problem. I guess it was the SynAttackProtect setting, but maybe
> these settings help someone who has the same problems.
Of course with lot of time on your hands, you could change them back one
by one. But I guess that you had enough of that now!
Christian Coppes
"Erland Sommarskog" wrote:
> Christian Coppes (Christi...@discussions.microsoft.com) writes:
> > I used this as server string: "SERVER=Servername:2594", maybe I'd better
> > used "\2594"?
>
> Neither. "Servername,2594" is the sytnax.
Ah OK...good to know, thanks!
> > But anyway, good news: The problem seems to be gone. Here are the settings
> > which were made at the server:
> >
> > TCP Settings:
> > In Registry Key "HKLM\System\CurrentControlSet\Services\Tcpip\Parameters"
> > EnableRSS DWORD 0
> > EnableSecurityFilters DWORD 0
> > EnableTCPA DWORD 0
> > EnableTCPChimney DWORD 0
> > SynAttackProtect DWORD 0
> >
> > And the "Maximum worker threads" setting was set to 288 instead of 0 (8
> > CPU server, can be different on other machines having more or less CPUs,
> > see
> > here:[url="http://msdn.microsoft.com/en-us/library/ms187024.aspx"]Maximum
> > Worker Threads Option[/url]).
> >
> > After setting these values on the server and the server's Windows all
> > problems were gone (rebooting of Windows is necessary)!
>
> Great to hear! You seem to have put in an immense lot of work to
> track this down. I am glad if my moderately initiated postings have
> been helptul.
Yes, thank you for a helping hand. I was researching for the solution with
testing and programming for 4 weeks now....
> > As all the settings were made at the same time I'm not really sure what
> > was the problem. I guess it was the SynAttackProtect setting, but maybe
> > these settings help someone who has the same problems.
>
> Of course with lot of time on your hands, you could change them back one
> by one. But I guess that you had enough of that now!
If this would be my server and I had the admin rights I would do this, but
it's a production server (you remember, 34 more databases on the same
server...) and alone in our database there are around 200 users which would
not be amused if I would return to further crashes...:-)
Thanks for your support,
Christian
Mary Chipman [MSFT]
it fixes the immediate problems, and you are planning to rewrite the
front-end using a different client anyway, perhaps that will suffice.
For some problems there are no good answers, only less bad ones.
--Mary
On Mon, 24 Aug 2009 06:50:01 -0700, Christian Coppes
Christian Coppes
as I wrote to Erland, the problem is fixed with some settings on the SQL
Server. But nevertheless I will change the frontend to ADP (already begung
with this). Yes, I know that MS is not recommending projects, but..if I would
always do what MS recommends...:-)
First they recommended DAO, then ADO, and now DAO again. First they
implemented VBA in all wizwards, now it's Macros, and in the coming version
Macros will become a debugger and so on...
Fact is: ADP is native SQL, I don't need to handle Jet SQL and SQL Server's
SQL in different situations, it's faster, it's better, no strange stored
procedures to get a simple SELECT and never again such silly ODBC problems.
A lot of forms I could successfully convert to ADO, they are faster now and
I couldn't find any real problem. Some little things are not the same as
before which I could do with DAO, but with some little workarounds I get the
same result in any case until now.
I have not the option to convert this to another kind of frontend, because
Access is what they want to have and I'm not able to write the same things I
can do with Access easily and fast with any other language. As the
application will not have a long lifetime it would make no sense to start
over again from scratch in another language.
Mary Chipman [MSFT]
APIs. DAO is intended to work with Jet data objects and ADO is a
general purpose API designed to work with many different data sources.
You can use either from VBA. Macros were originally intended for UI
commands, although their capabilities have been expanded in recent
versions because they are useful for sandboxing functionality that
would otherwise represent potential security vulnerabilities if done
in VBA. ADPs contain only Access objects such as forms, reports and
code and connect to a single SQL Server database, so there is no such
thing as native ADP SQL, you are working directly with T-SQL on the
back end. Its simplicity is its weakness -- all users must be directly
connected to the database, which is sometimes not acceptable for
performance/security reasons if the application needs to scale, and
you cannot connect to more than one database at a time. I sympathize
with what appears to have been the big mess you inherited, but that is
due to the flaws in the way the original application was created, the
ignorance of the original developer, or some combination of factors
more related to the misunderstanding and misuse of technology rather
than to defects in the technologies themselves. I know it's difficult
to know what to believe when one group at Microsoft promotes one point
of view, and another group promotes another, but that sadly is the
world we live in.
If you run into issues with the ADP solution, there is a very active
newsgroup filled with Access MVPs who can help if you have any further
questions at microsoft.public.access.adp.sqlserver. Good luck!
--Mary
Christian Coppes
it's not a question of which API is used for which database engine or
purpose. You can access SQL Server with DAO/Jet/ODBC or with ADO/ADP. Both is
possible, both has advantages and disadvantages. But the clearly
recommendation of Microsoft is: Don't use ADP, use ACCDB and DAO/ODBC. Why
else is the creation of Access project made so complicated and hidden that no
newcomer would find it without exactly knowing where it is or with a hint
from someone who knows it or by experimenting with the "new database" dialog?
And Macros: I don't see any sense in Macros - it would be possible to create
a sandbox for VBA, too, like you can see with VBScript in HTML: If you create
a HTA application the Internet Explorer allows full access to the system, in
HTML it doesn't. It would be possible to restrict access to the system in the
same way with VBA in Access. The real sense of Macros are to let people use
Access for quick and dirty creating of simple databases like for home use.
The only sense in my eyes is to get people to Access (and with this, better
selling of the product) who otherwise wouldn't use Access. I hate Macros and
kill them whereever I can find them in applications I'm responsible for.
That's no programming. My opinion.
Yes, the tables listed in an ADP can only be connected from one server and
best with SQL Server - no question. But it would be a good idea from
Microsoft to expand this capability and let Access connect to multiple
database servers with ADP natively.
There are two possibilities to work around this: Using linked tables on the
SQL Server (which doesn't work with a lot existing database providers) or
opening an ADO recordset in a form and set the form's recordset to the ADO
recordset. So in both cases you get connections to more than one database
server even of different types - no problem. And here would be the great
chance for Access to be an allround client system if this would work
natively, without such tricks. Where's the problem? It's not a real problem
to support more than one connection string in the options of Access and to
list all tables/queries from more than one server by giving them a server
prefix in the object list of Access. That's no really hard programming...:-)
But Microsoft invests a lot of work in creating a Macro debugger...
Yes, more servers would mean that you couldn't mix queries over more than
one server, because the SQL command would be sent to the individual server -
but in many cases there's no need to do that. And if, you can create a
temporary table on one of the servers which gets the contents of a query of
the other server and then it can be mixed together. But as there is no native
suppport for this, all must be done using slow VBA code. THIS would be a real
innovation for Access - copy the result of a table from one natively
supported database server to a second (third,...) connected server as a
temporary table in one ADO command. Or: Using the Jet engine (which is not
bad!) to make a local mix of data from two (or more) servers inside an ADP.
Or what's with this: Use connectionless forms for access to a database
server like it is possible in any web application. Yes, I can do that with
Access, but it's a really hard work to do with VBA. It would be a great
improvement if a form could do a refresh only if the user clicks a button or
using a kind of submit. Actually when I close my laptop while I have a
connection open my application will crash if I open the laptop again (or: if
a bad WLAN loses packets, or if I disconnect and reconnect the LAN cable and
so on). This doesn't happen with SQL Server Management Studio so why not
implement this kind of access into Access? I think a lot of people waiting
for such features and are angry to see that Microsoft is proud to present a
Macro debugger as "improvement" while it is a step back into the wrong
direction...
Your argument of a direct connection is the same with ODBC: If I close my
connection by closing the laptop the connection is lost and cannot be
restored. The same as in ADP. Security is the same in ODBC/ADP: You must
supply the server credentials in ODBC/connection string in ADP or use Windows
authentication which means a lot of login users in the SQL Server management
(not any enterprise wants this kind of security). So there's no difference in
security (it's bad in both cases, it would be of big help if Access would
have integrated a local method of encryption for such data so that it
wouldn't be possible to extract usernames/passwords with a simple hex
editor). It's not acceptable to let the user enter database security logon
data every time the client application starts... (and would be of less
security to let the user get the logon settings which normally allows a wider
access than his own login inside the application allows).
What I'm missing at Microsoft is a simple feedback method, one for each
application like it is normal in most OpenSource projects. Blogs, newsgroups,
support hotlines - this is not the same. It would be of great help for
Microsoft, too, to get direct feedback from any user of their application and
get new ideas and things the users really want.
Thanks for the hint to the ADP newsgroup, I've all Microsoft newsgroups
regarding Access and SQL Server inside my newsreader...:-)
If it's better we can go on discussing this in the ADP newsgroup (if you
want).
Mary Chipman [MSFT]
always loathed :)
>What I'm missing at Microsoft is a simple feedback method, one for each
>application like it is normal in most OpenSource projects. Blogs, newsgroups,
>support hotlines - this is not the same. It would be of great help for
>Microsoft, too, to get direct feedback from any user of their application and
>get new ideas and things the users really want.
I've been fighting for resources for Access-SQL developers for years,
back from when I was an MVP and since joining msft, but sadly one's
influence as a Microsoft employee is much diminished. Here's your
feedback mechanism -- go to
http://connect.microsoft.com/SQLServer/Feedback to register, create
some bugs, and get your friends to vote on them. They actually do pay
attention to Connect items internally, and if enough customers *vote*
on an issue, it will get some traction. Otherwise, don't hold your
breath
One problem with getting anything done in terms of resources for
Access-SQL developers is that Access and SQL Server are produced by
different divisions within Microsoft. Originally ADPs were a
collaboration between the teams, which was grandually abandoned after
the first version (long story). The Access team in recent years has
focused on their core constituency, who are *not* SQL Server
developers, but more end users/power users, whose needs are more
aligned with Sharepoint, which is also aligned with the needs of IT
departments for security, manageability, etc (which is why you see
resources poured into macros and the like). And the SQL Server team
has never considered the millions of installed Access databases and
millions of Access developers and as an important constituency worth
paying attention to. There are indications that things *might* be
changing, but it's important that you customers make your voices heard
if change is to happen.
--Mary
On Tue, 1 Sep 2009 03:05:03 -0700, Christian Coppes
<Christi...@discussions.microsoft.com> wrote: