Access Crashes Filtering a Linked SQL Table on a date field

I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.

This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.

Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.

I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.

Has anyone run across this before?

Thanks!

-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?

The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.

There are many many other alternatives that would work better and be much much faster.

About trace flag 8588

Hi Guys,
I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
while the session is enlisted in a distributed transaction that has an active
savepoint. I setup two sql server 2000 in two windows 2003 server, both
install sql server sp1. I created linked server and setup MCDTC on both
server. When I run application that include distributed transaction, I got
error above. I re-check MCDTC, permission and so on, but I still got that
issue. Could some one help me to fix it? Thanks in advance.For more information about using trace flags and startup parameters, see the
"DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
Online.
To use trace flag 8599, obtain the latest service pack for SQL Server 2000:
Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Once you update the service pack your problem will get fixed. Please update
the same and lemme know if it works...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Iter" wrote:
> Hi Guys,
> I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
> while the session is enlisted in a distributed transaction that has an active
> savepoint. I setup two sql server 2000 in two windows 2003 server, both
> install sql server sp1. I created linked server and setup MCDTC on both
> server. When I run application that include distributed transaction, I got
> error above. I re-check MCDTC, permission and so on, but I still got that
> issue. Could some one help me to fix it? Thanks in advance.
>|||Thank you very much. But for my case, the server is windows 2003 and have
already installed service pack 1. Is there something else that I need to
install? Thanks.
"Sreejith G" wrote:
> For more information about using trace flags and startup parameters, see the
> "DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
> Online.
> To use trace flag 8599, obtain the latest service pack for SQL Server 2000:
> Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
> Once you update the service pack your problem will get fixed. Please update
> the same and lemme know if it works...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Iter" wrote:
> > Hi Guys,
> > I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
> > while the session is enlisted in a distributed transaction that has an active
> > savepoint. I setup two sql server 2000 in two windows 2003 server, both
> > install sql server sp1. I created linked server and setup MCDTC on both
> > server. When I run application that include distributed transaction, I got
> > error above. I re-check MCDTC, permission and so on, but I still got that
> > issue. Could some one help me to fix it? Thanks in advance.
> >

About trace flag 8588

Hi Guys,
I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
while the session is enlisted in a distributed transaction that has an activ
e
savepoint. I setup two sql server 2000 in two windows 2003 server, both
install sql server sp1. I created linked server and setup MCDTC on both
server. When I run application that include distributed transaction, I got
error above. I re-check MCDTC, permission and so on, but I still got that
issue. Could some one help me to fix it? Thanks in advance.For more information about using trace flags and startup parameters, see the
"DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
Online.
To use trace flag 8599, obtain the latest service pack for SQL Server 2000:
Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Once you update the service pack your problem will get fixed. Please update
the same and lemme know if it works...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Iter" wrote:

> Hi Guys,
> I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remot
e
> while the session is enlisted in a distributed transaction that has an act
ive
> savepoint. I setup two sql server 2000 in two windows 2003 server, both
> install sql server sp1. I created linked server and setup MCDTC on both
> server. When I run application that include distributed transaction, I got
> error above. I re-check MCDTC, permission and so on, but I still got that
> issue. Could some one help me to fix it? Thanks in advance.
>|||Thank you very much. But for my case, the server is windows 2003 and have
already installed service pack 1. Is there something else that I need to
install? Thanks.
"Sreejith G" wrote:
[vbcol=seagreen]
> For more information about using trace flags and startup parameters, see t
he
> "DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
> Online.
> To use trace flag 8599, obtain the latest service pack for SQL Server 2000
:
> Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
> Once you update the service pack your problem will get fixed. Please updat
e
> the same and lemme know if it works...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Iter" wrote:
>

About Replicating Graphs and Trees

I won't even get into the recursion and trees stuff since at 4:40AM just
might fry my brain.
The distributed issue really boils down to collisions. Collisions really
just boil down to data ownership. If you have a set which can be openly
modified by anyone at any time, you are going to have a complete mess no
matter what you try to do. The other side is that a data set is owned by
one and only one person which means others may be able to view it, but can
not modify it. This case means that you have no issues with synching since
you can't have collisions with data. For the case in between the two
extremes, you are back to the mess. Don't have a solution since even with
the conflict resolution available with merge, it doesn't solve your problem
but just shifts the burden to someone else.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Still musing on all this -- the other way that I have been thinking about it
is -- to heck with the Graph part -- just work on keeping a list of
AdjacentLists in sycn, and after the synch is complete rebuild the Paths
table from it...from there on use the Paths.
But -- this would mean rebuilding the Paths on both the Server and on the
Client.
The client, I don't care -- that's his time, his cross to carry -- but on
the server, my findings have been that Celko's nested set theories were
clocking in at about 1000 records per second on a server. So a 10,000
record database would take 10 seconds about...
which would lock up the main path table for ten seconds for all users.
Make this 500 users -- we are talking about 5000 seconds -- so about 100
minutes a day. Yikes.
I have never checked to see to see if Celko's updates can be done with temp
tables and then dropping the main one and replacing it -- which would
decrease the amount of time offline -- but maybe make it impossible to allow
two users to synch at the same time...then again, all solutions probably do.
PS: I must admit, I do give Celko's techniques a hard time...and it's really
not his fault. More mine. But I have so much trouble understanding his stuff
because he wrote the book...well, for smarties...and I have to admit that
after reading it, I found out -- categorically -- that I am not one...no
matter how much I want to understand and use his stuff.
But...
If I were using MaterializedPaths, I would only have to rebuild/update the
fresh nodes...? Significantly less time.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:erwAQqGIEHA.2016@.TK2MSFTNGP10.phx.gbl...
> I won't even get into the recursion and trees stuff since at 4:40AM just
> might fry my brain.
> The distributed issue really boils down to collisions. Collisions really
> just boil down to data ownership. If you have a set which can be openly
> modified by anyone at any time, you are going to have a complete mess no
> matter what you try to do. The other side is that a data set is owned by
> one and only one person which means others may be able to view it, but can
> not modify it. This case means that you have no issues with synching
since
> you can't have collisions with data. For the case in between the two
> extremes, you are back to the mess. Don't have a solution since even with
> the conflict resolution available with merge, it doesn't solve your
problem
> but just shifts the burden to someone else.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

About distributed trans

I have two Server. One is Local, another is Remote. Both Installed SQL Server 2000 at Server 2000.
I start MSDTC on both the local and the remote. The Remote SQL Server start MTS.

When I run the code forward, the error message '7391' will occur

begin distributed tran
select * from rmtdb.pubs.dbo.employee --rmtdb is a linked server
go

How to verify the OLE DB provider specified supports distributed transactions?
How can I do for this error message?
How can I reconfig the Local Server and the Remote?Make sure you have these settings before SP or code :

SET ANSI_DEFAULTS ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON