SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Amarilis Alicea-Reyes
Amarilis Alicea-Reyes
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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?
Martin D. Cymerman
Martin D. Cymerman
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 17

Can you provide me with your connect statement?

Marty


Amarilis Alicea-Reyes
Amarilis Alicea-Reyes
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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


Martin D. Cymerman
Martin D. Cymerman
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 17
I can send you an Access Db with all the info you need to connect.
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 1248
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.
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 1248
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.
MTSmith
MTSmith
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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.





Amarilis Alicea-Reyes
Amarilis Alicea-Reyes
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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.


Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 1248
Alicea,
try to create a timestamp field in that table. I used this and it worked.

Good luck!
Frank Srebot
Frank Srebot
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search