Access client with SQL 2000 back end stops responding

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

I have the following:

1. An Access 2000 MDB file.
2. The MDB file has all forms, queries, and code.
3. The 30+ tables are all linked tables to two back ends.
a. One read only Pervasive P.SQL 2000i SP4 database
b. One MS SQL 2000 (believe sp2a) database which users read and write
to.
4. Windows XP and 2000 clients all with latest Windows Updates

Once every couple of months for several hours at a time no one is able to
save records to the MS SQL database. The Access database just sits there
and eventually times out when trying to save a record. I have restarted the
computers that I see accessing the records to see if I can break any locked
records but that did not work. A couple of times I have even restarted the
MS SQL server and that did not help either. Eventually within a couple of
hours the problem just stopped.

What can I look for to see what is causing this odd problem?
 
Well, if you are searching a place to look for solutions, the first thing to
do would be to post to a newsgroup about ODBC or SQL-Server such as
m.p.a.odbclientsrv or m.p.sqlserver.server. This newsgroup is mainly about
ADP and a little about SQL-Server and has nothing to do with linked tables.

The second thing to do would be to look at any connection or locking problem
on the SQL-Server. For example, are you able to open a second MDB file and
connect to the SQL-Server; are you able to refresh the links, can you open a
connection with Enterprise Manager or Query Analyser; what are the result of
sp_lock; what kind of authentification are you using; did you try with a
SQL-Server logon instead of Windows authentification; are you using Named
Pipes and if yes, can you connect using TCP/IP instead; etc., etc., etc.
 
for the record; linked tables against SQL Server has _ALWAYS_ been
problematic.

I would reccomend dumping pervasive; maybe using DTS to copy the data
into a real sql server database

if you used Access Data Projects; it is easy to point to multiple
databases on the same server; it is quite easy to refer to these using
the 3-part naming convention

DATABASE.OWNER/SCHEMA.OBJECT
 
are you sure that you're not just seeing a simple performance problem?

how fast / busy is the SQL Server machine??

have you tried thigns like the 'index tuning wizard' to evaluate
whether you're indexing correctly??

-Aaron
 
Back
Top