ODBC Delete on a Linked Table Failed RRS feed

  • Question

  • I am trying to delete all records in a SQL table

    I tried a delete query and DoCmd.RunSQL "Delete * From table name"

    The DoCmd.RunSQL command worked when the table was Access

    The delete query runs if I open it directly but not with a VBA DoCmd.OpenQuery

    Both result in the same Access Error 3156, SQL Server Native Client 11.0 query expired

    The delete query runs for about 3 seconds when I open it directly

    Access 2016 front end, SQL Express 2017 on Win 2012 server

    Thursday, January 9, 2020 12:58 AM

All replies

  • Not sure what the reason would be, but I would not use DoCmd.OpenQuery, and CERTAINLY not while SetWarnings is false, because you are not getting enough information.

    Rather use CurrentDB.Execute "queryname", dbFailOnError

    While having an error handler in place.

    In the error handler inspect the Errors collection (NOT just Err.Description), and you may find there is more than one error record (? DBEngine.Errors.Count in the immediate window), and by checking each record you may find out more.

    Another option is to use a Passthrough query, and rather than Delete, you can use T-SQL syntax like Truncate Table, which is MUCH faster because not logged.

    -Tom. Microsoft Access MVP

    Thursday, January 9, 2020 2:00 AM
  • I tried the .Execute solution - same error 3156 "ODBC--delete on a linked table failed" after about a 2 minute delay

    I am able to run a query to append records to this table using DoCmd.OpenQuery so it appears to be specific to deleting and only when I run from VBA since the query does execute when I open it directly

    I am new at using SQL so creating a T SQL query is not an option

    Thursday, January 9, 2020 7:46 PM
  • Update

    The problem appears to be that the table was open

    If I close the table before running the SQL Delete it works

    Friday, January 10, 2020 3:45 PM
  • Ah, I did not expect a table was open, or that it would affect Delete. Maybe if a record was dirty.

    In production systems users should never be interacting with the tables directly.

    Glad you figured it out, and thanks for posting back.

    -Tom. Microsoft Access MVP

    Friday, January 10, 2020 3:52 PM