Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008

    Unanswered: ODBC insert on a linked table failed

    Hi all,

    I have searched for this specific error, but haven't been able to find a solid solution.

    I have a Access 2003 front end application with linked tables to tables on SQL Server 2000. The front end uses a form that allows me to enter data directly to the table. The info I enter in the form is inserted for each column on the table. The app is used by 10-20 users.

    I get few intermittent errors, such as "ODBC--insert on a linked table 'LOTS' failed" or "Object invalid or no longer set" or "ODBC--connection failed" or "ODBC--Connection to server failed". More recently I have been getting the first error (insert on a linked table failed). I'm not even sure where to look to find out what is the problem. When I use the app on my system, I do not get any errors. The application works very smoothly, considering it was upsized to SQL Server 2000.

    The front end was actually a front end to an Access 2003 backend database (tables were linked to the tables in the backend MDB). I upsized the front end so that the tables were now linked to SQL Server 2003 and upsized the data from the tables in the backend MDB. The original Access 2003 application worked with no errors. But ever since the upsize, I get all the above stated errors.

    I appreciate your help in anyway. Thank you!

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    A couple of things...

    Offhand I'd say it sounds like an issue with the form and it's not updating the relational tables correctly but you'll have to test by having the user add data directly into the tables or SQL Server didn't establish the relationships between the table when upsizing (see below comments on relationships of the tables). I also think the ODBC DSN might be the problem considering that it works fine on one machine and not the other. Try re-creating the ODBC DSN on the specific problematic user's machine and deleting/re-linking the tables. Does this happen for 1 specific user or does it happen randomly for all other users while working fine on your machine? Do the other users have a good network connection as you do or have they also been experiencing other abritrarly network problems? Also, is it possible that the user's might be doing something you hadn't anticipated on the form which is causing the problem?

    There's a lot of possibilities here and you need to try and narrow down what might be the problem the best you can. Start by narrowing it down to 1 or more specific users if possible and see if you can tell if they are doing ANYTHING different or if there's anything different with their setup. You can even throw in a few msgbox lines of code in certain places in the front-end to see what values are being returned during data entry. This may help to make sure certain fields such as the primary key or other required fields are being updated as the user enters data. If the user gets the error, make sure you walk through EXACTLY what they did on your machine to see if you can reproduce. Hopefully you can reproduce somehow on your machine and find out it's a simple code fix somewhere in the form.

    Other things to check/try:
    1. Possibly add a timestamp field (timestamp data type) to one or more of the SQL Server main data tables.
    2. Refresh your linked tables in the Access interface.
    3. There is some code in the codebank which automatically refreshes linked tables. You could possibly use this code to refresh the linked tables somewhere in the startup of the app. Also try deleting all the tables out of the mdb and re-linking the tables back in. Check your ODBC DSN Connection or even try creating a new one. I like to use Windows authentication verses SQL Server authentication on the ODBC DSN Connection.

    4. *** Check to make sure you have a primary key in the tables and an autonumber (which is set to increment). Check the tables as sometimes the upsizing wizard doesn't configure them correctly. Did you use DRI or Triggers when upsizing the tables? (There was a checkbox for this in the upsizing wizard and you should have checked DRI - Direct Referential Integrity!) It sounds like the problem is relational so check the relationships in SQL Server on the tables.

    5. Make sure you're not experience possible network connectivety issues to the SQL Server db and that the form itself isn't causing update problems. Test by adding data directly into the tables verses via the form. Make sure you/users have permissions in SQL Server for all (including the relational) tables.
    6. Maybe consider re-indexing the tables via SQL Server.

    7. *** Again, make sure the relationships of the tables on SQL Server have no issues. If you somehow got or had any orphaned records, SQL Server might be complaining when adding new records or it especially didn't setup the relationship correctly during upsizing! Check this. Especially check all the relationships of the tables on SQL Server. Possibly delete relationships on the tables and re-create them and see if you get any errors when re-establishing the relationships (make sure to refresh or delete/relink the tables in the mdb after making any modifications to the SQL Server tables!)
    Last edited by pkstormy; 02-15-08 at 16:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    8. check for differences in regional settings between your system and the user(s) reporting issues.

    9. if you have users with non-US-English Windows and/or Office, what localisation do they have?

    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts