Solved

Delete all data from a SQL Server Table using VBA

Posted on 2009-07-06
8
626 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

786 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