Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ODBC - insert on a linked table ''TableName'' failed Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2005 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2008 6:17 AM
Points: 5, Visits: 9
I have a mail system with the front end in Microsoft Access XP and the back end is in Microsoft SQL Server 2000 in several offices in the building I work.

One of them has been giving me trouble for a couple of months. Some times when the user tries to add a record the system does not save it and sends this message:

ODBC--insert on a linked table 'dbo_tblCorrespondencia' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

And when the user tries to update an old record it sends this message:

ODBC--update on a linked table 'dbo_tblCorrespondencia' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

The only thing that the user can do is browse the records but not add or update.

The only temporary solution that the DBA has found is to delete the log file of the database and create a new one but this only works sometimes and for a few months and the problem returns.

I place a copy of the database in my computer to try to replicate the problem to find a solution and after a few tries I get the message but it does not seems to have a pattern and when I try to fix it like the DBA does, that does not work for me.

Do any of you know what could cause this and how to fix it?
Post #166248
Posted Tuesday, March 8, 2005 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2008 4:19 PM
Points: 110, Visits: 17

Can you provide me with your connect statement?

 

Marty

Post #166253
Posted Tuesday, March 8, 2005 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2008 6:17 AM
Points: 5, Visits: 9

Well, I use linked tables trough a DSN file and this is the information in the DSN File

[ODBC]
DRIVER=SQL Server
UID=Alicea_A
Trusted_Connection=Yes
DATABASE=MailsysOficinaSecretarioData
WSID=ALICEA-A
APP=Microsoft Office XP
SERVER=IDC-SQL

Post #166300
Posted Tuesday, March 8, 2005 1:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2008 4:19 PM
Points: 110, Visits: 17
I can send you an Access Db with all the info you need to connect.
Post #166388
Posted Wednesday, March 9, 2005 12:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:02 AM
Points: 1,012, Visits: 1,008
generate sql script for that table and archive it
also archive the access database and send them to
ciuli7ro@yahoo.com
I will try to rebuild them and see if I have the same problem.
Post #166460
Posted Wednesday, March 9, 2005 12:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:02 AM
Points: 1,012, Visits: 1,008
TRY THIS:
ensure the ODBC connection time-out is sufficiently long.

ODBC settings are managed through the system registry and must be configured when Access or any other clients using Jet are closed. The registry settings are available through the Microsoft Windows® Registry Editor (Regedit.exe) under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

The default value for this is 60, which means that if it takes longer than 60 seconds to transfer the data from an Access table to SQL Server. Set the value to 0 by double-clicking the name and selecting the Decimal option in the Base options. Change the setting to 0 and click OK.
Post #166463
Posted Wednesday, March 9, 2005 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2007 12:27 PM
Points: 25, Visits: 1

Sometimes I get that message in Access. In your SQL table, create an Auto ID column:

[nAutoID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL

This is like creating a column in an Access database using AutoNumber as a datatype. Remember, after making this change to your SQL table - you will have to delete it from the Access database and relink. You can't just do a refresh, that doesn't work if columns have been added or deleted from a SQL table.




Post #166565
Posted Wednesday, March 9, 2005 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2008 6:17 AM
Points: 5, Visits: 9

The Primary Key of that table is an Identity Column.

I have just discover that sometimes I can directly add a new record to the table and I don't get the message but when I try to add it using the form I get the error again.

Post #166575
Posted Wednesday, March 9, 2005 7:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:02 AM
Points: 1,012, Visits: 1,008
Alicea,
try to create a timestamp field in that table. I used this and it worked.

Good luck!
Post #166583
Posted Wednesday, February 6, 2008 4:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2008 6:22 AM
Points: 4, Visits: 13
Hi

I have been having the same problem as described in this thread. Have you ever come up with a definitive solution?

I have found out, in a database that has been working for years, that I am able to add and delete through access tables, but the odbc times out in a form. Also, there seems to be a pattern. The table structure is from a workorder table with about 45 fields with a parent customer list. There are about 2000 customers with about 25000 work orders. Its seems that those customers with about 145 workorders receive the timeout error.

I am still working on other solutions like putting in more indexes using Management Studio Express and I hope it will work.

Any Advice would be great

Thanks

Frank

fsrebot@hotmail.com
Post #452491
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse