Solved

Delete all data from a SQL Server Table using VBA

Posted on 2009-07-06
8
623 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"
0
Comment
Question by:schmir1
  • 5
  • 3
8 Comments
 
LVL 12

Expert Comment

by:kevin_u
ID: 24790125
http://doc.ddart.net/mssql/sql70/ta-tz_10.htm

truncate table Commitment_Outlook_bak

or

delete from Commitment_Outlook_bak
0
 

Author Comment

by:schmir1
ID: 24790168
What code to I need?  Truncate table doesn't work by itself.
0
 
LVL 12

Expert Comment

by:kevin_u
ID: 24790192
DoCmd.RunSQL "truncate table  Commitment_Outlook_bak"
0
 
LVL 12

Accepted Solution

by:
kevin_u earned 500 total points
ID: 24790193
DoCmd.RunSQL "DELETE FROM Commitment_Outlook_bak"
but this is slower.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:schmir1
ID: 24796238
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.
0
 

Author Comment

by:schmir1
ID: 24796491
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.
0
 

Author Comment

by:schmir1
ID: 24798581
I had to relink the tables then it worked.  Thanks for you help.
0
 

Author Closing Comment

by:schmir1
ID: 31600412
Works great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now