Post reply

ODBC - insert on a linked table ''TableName'' failed

  • March 8, 2005 at 8:29 am

    #66975

    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?

  • March 8, 2005 at 8:49 am

    #545889

    Can you provide me with your connect statement?

     

    Marty

  • March 8, 2005 at 10:19 am

    #545929

    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

  • March 8, 2005 at 1:59 pm

    #546004

    I can send you an Access Db with all the info you need to connect.

  • March 9, 2005 at 12:35 am

    #546047

    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.

  • March 9, 2005 at 12:50 am

    #546050

    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.

  • March 9, 2005 at 7:04 am

    #546138

    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.

  • March 9, 2005 at 7:16 am

    #546147

    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.

  • March 9, 2005 at 7:53 am

    #546154

    Alicea,

    try to create a timestamp field in that table. I used this and it worked.

    Good luck!

  • February 6, 2008 at 4:47 pm

    #775817

    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

  • February 12, 2008 at 6:31 am

    #777304

    Hi Frank,

    After much testing I ended up doing two things that as of today seem to solve the problem:

    1. I have to big fields that I use as memo fields for my users to write anything they have to write about the document. The two fields are varchar and I had to reduce the length that I originally assigned them from 8000 to 3800. Some of my users where not happy about it but most of them didn't need that much space.

    2. The main table was used on a form with a List Box to browse the records to edit any of them. When the form opened, the List Box was populated with all the records that everyone entered since they begin using the application. They could either do a search by keyword or number, or they could just use they side bar to move the List Box down until they found the record they wanted. I had to change that so when the user opened that form the List Box loaded only the top 1% of all the records entered but they can still do the search by keyword or number of any record not displayed on the List Box.

    So far I have not had that error again 🙂

    I hope that helps 😀

  • February 12, 2008 at 8:10 am

    #777358

    For Amarilis Alicea-Reyes

    Thank-you very much. After much research, your solutions seem to make the most sense.

    I had read another blog and it stated that I might have to do some maintenance on the database like rebuilding indexes.

    I will try these actually today.

    I'll keep you informed and thanks again.]

    Frank

  • February 13, 2008 at 5:38 pm

    #778158

    For Amarilis Alicea-Reyes

    Thank-you very much.

    Just to let you know that I went with your suggestion and manipulated the notes sections of my work order table.

    I went from datatype ntext to varchar(8000) and it worked!

    I still can't believe it took me quite a while to find anyone with the same problem, but I am eternally greatful for your solution.

    Thanks a Million!!!!!!!

    Frank

  • February 14, 2008 at 1:25 pm

    #778566

    Hi Frank 😀

    I'm really, really happy to see that it work out for you as well.

    Good Luck with the rest of the project 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply