Delete all records from SQL Server table

• Vic Rauch-303403

  SSCommitted

  Points: 1576

  More actions

  December 15, 2006 at 1:04 pm

  #69166

  I am converting an Access backend to a SQL Server backend and keeping the Access front end.  I have a problem that I think is tied to the dbSeeChanges option in regard to SQL Server tables and the way Access works with those tables.  I have written a routine in Access to loop through all the SQL Server tables.  During the loop, I want to clear out all the records from each table, then immediatly re-fill that table with the current data from the production database.  This way I can refresh the data whenever I need to for testing against the current, all-Access production databases.  My delete statement looks like this:

    DoCmd.SetWarings False

    DoCmd.RunSql "DELETE * FROM dbo_" & rs!TableName & ";"

    DoCmd.SetWarnings True

  When I try to delete records from a table without a primary key, I get this message: Run-time error '3086':

  Could not delete from specified tables.

  I beleive I need to learn something about keyed/non-keyed SQL tables and the tables with/without an IDENTITY column.

  Help here greatly appreciated!

  Vic

  [font="Comic Sans MS"]Vic[/font]
  www.vicrauch.com

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  December 15, 2006 at 1:36 pm

  #678453

  Hello Vic,

  Can you remove "*" from your delete statement and then try it. Also check whether the table is having any relationship with a parent table.

  Hope this helps you.

  Thanks

   

  
  Lucky

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  December 15, 2006 at 4:01 pm

  #678483

  Every linked table from SQL you want to update in Access must have a primary key index in Access. The table doesn't actually have to have one in SQL Server (your bad), but it does in Access.

  You can create what is called a "pseudo index" for this purpose:

  In query window:

  create unique index pk_mytable on dbo_MyTable(keyCol) with primary;

  Make sure that the column is unique or the delete may fail.

  Hope this helps.

  Russel Loski, MCSE Business Intelligence, Data Platform

• Vic Rauch-303403

  SSCommitted

  Points: 1576

  More actions

  December 16, 2006 at 12:14 am

  #678513

  Lucky, the "*" does not seem to make any difference while running the query in Access.  I do think Russel hit on it though, as the table that does not allow me to delete any records does not have a primary key on it.

  Russel,  Thanks for the primary key.  I have a few little tables that just hold things, therefore no real need for a key at all in Access.  But if they need keys so Access and SQL Server will play will together, then they will all get a primary key.

  Again, thank you both,

  Vic

  [font="Comic Sans MS"]Vic[/font]
  www.vicrauch.com