Delete all records from SQL Server table

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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