ODBC Timeout

[cspowell24 via AccessMonster.com]

Our office uses Access 2000 as a front end to a SQL
server 2000 database.
Each client has it's own copy of the access .mdb file on
their computer.

It has been up and running for about a year and half but
recently have been getting sporadic ODBC timeout errors.

The syntax of the error is:
ODBC -- update on a linked table 'tableName' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

It only seems to happen in one particular Form when a
user adds/updates a record. The table underneath is a
linked table which has probably 15,000 records This issue
only happens about once every couple of days and is very
sporadic.

I've researched this and seen several postings where people
are having the exact same problem, but I have yet to find a clear
resolution. Anyone, anyone?

 

[Pieter Wijnen]

probably due to MSSQL locking issues
ie somebody i running a report/update against the table & the ODBC timeout
period is exceded before the data is freed, wouldn't happen to a Oracle Db
Though <g>

Pieter

 

[Pieter Wijnen]

probably due to MSSQL locking issues
ie somebody i running a report/update against the table & the ODBC timeout
period is exceded before the data is freed, wouldn't happen to a Oracle Db
Though <g>

Pieter

Our office uses Access 2000 as a front end to a SQL
server 2000 database.
Each client has it's own copy of the access .mdb file on
their computer.

It has been up and running for about a year and half but
recently have been getting sporadic ODBC timeout errors.

The syntax of the error is:
ODBC -- update on a linked table 'tableName' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

It only seems to happen in one particular Form when a
user adds/updates a record. The table underneath is a
linked table which has probably 15,000 records This issue
only happens about once every couple of days and is very
sporadic.

I've researched this and seen several postings where people
are having the exact same problem, but I have yet to find a clear
resolution. Anyone, anyone?

--

 

[Beowulf_Cam]

How is the link from Access to SQL being achieved? There are a few
ways:

- Using ODBC links to the SQL Server from Access (this is the
slowest). The table appears as linked and processing of your queries is
performed by the workstation.

- Accessing the SQL server directly using ADO (this is fairly quick),
which passes the SQL string directly to the server (using SQL syntax).
This also allows you to use views and stored procedures. This doesn't
require the creation of ODBC connections on the workstation.

- Using Access as a Project file which gives it an ADP extension
instead of MDB. (fastest). Allows a direct view into the server for
tables, views and procedures. Also uses the ADO architecture.

Since I started using ADO exclusively for SQL server connections to
Access, although I may experience slowness on occasion I haven't had it
drop a connection yet. ADO is also the preferred method by Microsoft.
15,000 records isn't a lot for SQL server, but it could cause problems