none
Error deleting records from Access 2010 table linked to SharePoint 2010 list

    Question

  • I have a two Access databases (A and B).  Database A contains several tables and Database B contains one table.  In Database A I have two queries that I use to update the table in Database B -- one to delete all records in the table, the other to insert records into the database.  When I execute all this locally everything works fine -- the DELETE query removes all the records in Database B and the INSERT query populates the database as expected.

    I have now linked both databases to SharePoint 2010 using the MS Access 2010 Database Tools > SharePoint button.  All that works fine and I can see the tables as lists in SharePoint.

    The INSERT query also works properly -- i.e. when I run it new records are inserted from Database A into Database B and they then appear in the SharePoint lists.

    However, when I try to run the DELETE query, I get this error:

    "The Microsoft Office Access database engine could not find the object 'BudgetRollup'. Make sure the object exists and that you spell its name and the path name correctly."

    The queries are:

    DELETE * FROM BudgetRollup IN 'c:\data\FY2012BudgetRollup.accdb';

    INSERT INTO BudgetRollup IN 'c:\data\FY2012BudgetRollup.accdb' SELECT * FROM [Rollup Query];

    One other point -- if I delete all records manually via the SharePoint list, and then run the DELETE query, it indicates that '0' records will be deleted (as expected) and then (appears to) run/complete without any additional errors.  But if there are records in the list/table, then it indicates that XX errors will be deleted, and then displays the above error.

    Any ideas what's going on here?  Is it not possible to run a DELETE on a linked table?

    Wednesday, April 04, 2012 3:34 PM

Answers

  • Yes, you quite have this correct. I don't think you can even update an external database with linked tables.

    However, since the data in question IS linked, then why not just place the link in the current database and use that?

    The real problem here is with external data sources, you have to be logged on, tested for permissions etc. 

    I mean, can you do this:

    DELETE * FROM BudgetRollup IN 'www.amazon\MyMoneyIOweYouTable'

    In other words, you cannot go and delete records in your next doors neighbor table here. You as a general rule MUST open the table and be logged on as a legitimate user in the CURRENT database.

    In your case, just remove the external references and path name. You then simply setup a linked table to that external data source. You can then do select and delete operations on those tables.

    I actually surprised that delete and inserts as you have. I assumed selects would work, but then again 100% native Access tables might allow this both ways.

    As above shows, attempting to operate on an external database which in term has external links means quite much that all bets are off.

    So you cannot as a rule attempt to use external systems with links no more then you can open up a linked database on your next door neighbor's computer that THEN links to their bank account.

    Those types of hops and jumping between systems in general does not work. It might have worked if you talking about a word or Excel or simple power point file on YOUR computer, or even a mdb file with Access, but as such it is a Mount Everest of a difference to open a file and that of attempting to link to a link to a link.

    Gee, I sure I could find 15 or maybe find 200 links and eventually link far enough along to get to your bank account? ;-)  So, using external links in general will not work, and much worse is those to external database systems.


    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    kallal@msn.com

    • Marked as answer by Bruce Song Wednesday, April 18, 2012 8:10 AM
    Thursday, April 05, 2012 7:29 AM

All replies

  • I am reasonable sure if you attempting to use an external link from another database, well that never did work.

    In other words, you cannot execute something on an external database that is linked to something else.

    On the other hand, just setup a link in the current database and you should be fine.

    So, I don't think you could ever execute a sql command on any database in which the table in question was a linked table. I suppose it might have worked with an Access to Access link, but for any ODBC or link to SQL server or SharePoint, you could never operate a link on a link.

    However, as noted, in most cases the simple solution is to thus ensure the link to the external database is simply included in the current database.

    So it certainly possible to run a delete on a linked table, but your examples here are not linked tables, but in fact external tables.


    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, April 04, 2012 11:51 PM
  • Maybe I need to clarify (or maybe I'm just being dense here, in part because it's been a long time since doing anything with Access).  I created Database A with some tables that have records in them.  Before introducing SharePoint into the mix, I did the following:

    From within Database A I run the following query ([Rollup Query] just does a rollup of records from multiple tables):

    SELECT [Rollup Query].* INTO BudgetRollup IN 'c:\data\DatabaseB.accdb' FROM [Rollup Query];

    This creates and populates the table BudgetRollup in Database B.

    I then run this query

    DELETE * FROM BudgetRollup IN 'c:\data\DatabaseB.accdb';

    which deletes the records from Database B.  I verified this by opening Database B and confirming that no records remain.

    Then I run this query from Database A

    INSERT INTO BudgetRollup IN 'c:\data\DatabaseB.accdb' SELECT * FROM [Rollup Query];

    When it completes I now have records in Database B again.

    I then linked the table BudgetRollup to SharePoint, creating a list in SharePoint (i.e. I now have a table that is "external" to Database A and "linked" to SharePoint, correct?).  After doing this, I am still able to run the INSERT query from Database A against DatabaseB.accdb, it's just the DELETE query that throws an error.  So in this case I am running against an external database that contains a linked table, correct?

    thanks

    Thursday, April 05, 2012 2:00 AM
  • Yes, you quite have this correct. I don't think you can even update an external database with linked tables.

    However, since the data in question IS linked, then why not just place the link in the current database and use that?

    The real problem here is with external data sources, you have to be logged on, tested for permissions etc. 

    I mean, can you do this:

    DELETE * FROM BudgetRollup IN 'www.amazon\MyMoneyIOweYouTable'

    In other words, you cannot go and delete records in your next doors neighbor table here. You as a general rule MUST open the table and be logged on as a legitimate user in the CURRENT database.

    In your case, just remove the external references and path name. You then simply setup a linked table to that external data source. You can then do select and delete operations on those tables.

    I actually surprised that delete and inserts as you have. I assumed selects would work, but then again 100% native Access tables might allow this both ways.

    As above shows, attempting to operate on an external database which in term has external links means quite much that all bets are off.

    So you cannot as a rule attempt to use external systems with links no more then you can open up a linked database on your next door neighbor's computer that THEN links to their bank account.

    Those types of hops and jumping between systems in general does not work. It might have worked if you talking about a word or Excel or simple power point file on YOUR computer, or even a mdb file with Access, but as such it is a Mount Everest of a difference to open a file and that of attempting to link to a link to a link.

    Gee, I sure I could find 15 or maybe find 200 links and eventually link far enough along to get to your bank account? ;-)  So, using external links in general will not work, and much worse is those to external database systems.


    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    kallal@msn.com

    • Marked as answer by Bruce Song Wednesday, April 18, 2012 8:10 AM
    Thursday, April 05, 2012 7:29 AM