We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Delete all data from a SQL Server Table using VBA

schmir1
schmir1 asked
on
Medium Priority
1,001 Views
Last Modified: 2013-11-27
How do I delete all data from a SQL Server Table using VBA.  I was using the following for a Access table but it doesn't work for SQL Server:
  DoCmd.RunSQL "DELETE * FROM Commitment_Outlook_bak"
Comment
Watch Question

Commented:
http://doc.ddart.net/mssql/sql70/ta-tz_10.htm

truncate table Commitment_Outlook_bak

or

delete from Commitment_Outlook_bak

Author

Commented:
What code to I need?  Truncate table doesn't work by itself.

Commented:
DoCmd.RunSQL "truncate table  Commitment_Outlook_bak"
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I tried:
  DoCmd.RunSQL "truncate table  Commitment_Outlook_bak"
And got this error:
  Error Number 3129
  Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Note: I'm running this in VBA.

Author

Commented:
I tried:
  DoCmd.RunSQL "DELETE FROM Commitment_Outlook_bak"
I got error:
  Error Number 3086
  Could not delete from specified tables.
Fired up Server management Studio and I CAN'T DELETE records with it either.
Guess I need to ask another question.

Author

Commented:
I had to relink the tables then it worked.  Thanks for you help.

Author

Commented:
Works great.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.