473,320 Members | 1,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Truncate in VBA

48
Hi,

Is it possible to fire a "Truncate <table>" from VBA (access2003) into SQL server ?
Special considerations ?

Thanks
Jan 22 '08 #1
6 29160
ADezii
8,834 Expert 8TB
Hi,

Is it possible to fire a "Truncate <table>" from VBA (access2003) into SQL server ?
Special considerations ?

Thanks
I don't think you can do it directly through VBA, but you can indirectly through ADO by executing a Stored Procedure (let's call it StoredProcedure1) via a Command Object:
  1. Create a Stored Procedure that will execute a TRUNCATE TABLE against an Authors Table on a Database residing on an SQL Server.
    Expand|Select|Wrap|Line Numbers
    1. ALTER PROCEDURE dbo.StoredProcedure1
    2. AS TRUNCATE TABLE  dbo.Authors
  2. Execute the Stored Procedure via an ADO Command Object.
    Expand|Select|Wrap|Line Numbers
    1. Dim cnn As ADODB.Connection, cmd As ADODB.Command
    2.  
    3. Set cnn = CurrentProject.Connection
    4. Set cmd = New ADODB.Command
    5.  
    6. cmd.ActiveConnection = cnn
    7. cmd.CommandText = "StoredProcedure1"
    8. cmd.CommandType = adCmdStoredProc
    9.  
    10. cmd.Execute
    11.  
    12. Set cmd = Nothing
    13. Set cnn = Nothing
    14.  
  3. Hope this helps, I'm sort of out-of-my-field on this one!
Jan 23 '08 #2
Jim Doherty
897 Expert 512MB
Hi,

Is it possible to fire a "Truncate <table>" from VBA (access2003) into SQL server ?
Special considerations ?

Thanks
ADeziis example is solid and spot on I throw this into the pot for no other than your interest level of potential difference in method of execution from the 'Access' side creating a temporary querydef setting an ODBC connection string to the server and then executing the .SQL property of simply.

TRUNCATE TABLE dbo.tblBlah

has the same effect but 'does' rely on ODBC connectivity so for me has more overhead but does NOT need a specific stored procedure. Depends on your design flavour.

Personally I keep work server side so I'd go with ADezii on the actual way to go on this (as your stored procedure could then go on to account for elements such as why the table was truncated and maybe fire the reasons off to another audit system all from within the same procedure etc etc

Jim :)
Jan 23 '08 #3
wquatan
48
Thanks ADeziis and Jim !

I'm completely new into SQL server but not into Access. Till now lack of time made me put code (query's) on the Access-side (despite preferring it server-side)

In this particular case (because of the provided example, thanks for that) I'll go for the ADeziis example.

But just out of curiosity, how would I do the "TRUNCATE TABLE dbo.tblBlah" from VBA ?

Thanks
Jan 23 '08 #4
ADezii
8,834 Expert 8TB
Thanks ADeziis and Jim !

I'm completely new into SQL server but not into Access. Till now lack of time made me put code (query's) on the Access-side (despite preferring it server-side)

In this particular case (because of the provided example, thanks for that) I'll go for the ADeziis example.

But just out of curiosity, how would I do the "TRUNCATE TABLE dbo.tblBlah" from VBA ?

Thanks
To the best of my knowledge you can't do it directly from VBA. Mr. Doherty would have a better idea on this than I would, he seems to be better qualified.
Jan 23 '08 #5
Jim Doherty
897 Expert 512MB
Thanks ADeziis and Jim !

I'm completely new into SQL server but not into Access. Till now lack of time made me put code (query's) on the Access-side (despite preferring it server-side)

In this particular case (because of the provided example, thanks for that) I'll go for the ADeziis example.

But just out of curiosity, how would I do the "TRUNCATE TABLE dbo.tblBlah" from VBA ?

Thanks

Using ODBC connectivity to SQL server the logic would be to create a temporary passthrough query on the fly set its connect property in code to the relevant connection string (the example below uses windows integrated security). We set the necessary SQL property of the temporary query to the TRANSACT SQL command that gets fired server side and also set the 'returns records' property of the passthrough query to false (we arent returning a recordset to the client here obviously we are merely executing a process on the server)

The below is the code that will perform that action and relies on a reference to the DAO 3.6 object library (I leave error handlng to you nothing destructive this will either work or it will not)

Expand|Select|Wrap|Line Numbers
  1.  On Error Resume Next 
  2. Dim mydb As DAO.Database
  3. Dim qdf As DAO.querydef
  4. Set mydb = CurrentDb
  5. Set qdf = mydb.CreateQueryDef("")
  6. With qdf
  7. .Connect = "ODBC;DSN=yourservername;UID=username;DATABASE=yourdbname;Trusted_Connection=Yes"
  8. .SQL = "TRUNCATE TABLE dbo.tblBlah"
  9. .ReturnsRecords = False
  10. .Execute
  11. .Close
  12. End With
  13. 'If you wish the query to persist in the database
  14. 'as a permanent query then you MUST supply a name for the query
  15. 'shown between speechmarks in the createquerydef("") statement
  16. 'ie replace createquerydef("") with createquerydef("qryTruncate") for instance.
  17. 'if you do save it to the database as a permanent
  18. 'query then uncommenting the below line will delete it once this routine ends
  19. 'CurrentDb.QueryDefs.Delete (qdf.Name)
  20.  
  21.  
And thankyou ADezii for your gracious response but in your presence I can with confidence say "No way Jose" :)

Hope this helps you a little wquatan

Jim :)
Jan 24 '08 #6
ADezii
8,834 Expert 8TB
Using ODBC connectivity to SQL server the logic would be to create a temporary passthrough query on the fly set its connect property in code to the relevant connection string (the example below uses windows integrated security). We set the necessary SQL property of the temporary query to the TRANSACT SQL command that gets fired server side and also set the 'returns records' property of the passthrough query to false (we arent returning a recordset to the client here obviously we are merely executing a process on the server)

The below is the code that will perform that action and relies on a reference to the DAO 3.6 object library (I leave error handlng to you nothing destructive this will either work or it will not)

Expand|Select|Wrap|Line Numbers
  1.  On Error Resume Next 
  2. Dim mydb As DAO.Database
  3. Dim qdf As DAO.querydef
  4. Set mydb = CurrentDb
  5. Set qdf = mydb.CreateQueryDef("")
  6. With qdf
  7. .Connect = "ODBC;DSN=yourservername;UID=username;DATABASE=yourdbname;Trusted_Connection=Yes"
  8. .SQL = "TRUNCATE TABLE dbo.tblBlah"
  9. .ReturnsRecords = False
  10. .Execute
  11. .Close
  12. End With
  13. 'If you wish the query to persist in the database
  14. 'as a permanent query then you MUST supply a name for the query
  15. 'shown between speechmarks in the createquerydef("") statement
  16. 'ie replace createquerydef("") with createquerydef("qryTruncate") for instance.
  17. 'if you do save it to the database as a permanent
  18. 'query then uncommenting the below line will delete it once this routine ends
  19. 'CurrentDb.QueryDefs.Delete (qdf.Name)
  20.  
  21.  
And thankyou ADezii for your gracious response but in your presence I can with confidence say "No way Jose" :)

Hope this helps you a little wquatan

Jim :)
You are quite welcome, Jim. Thanks for the code snippet, I was wandering how to implement it myself.
Jan 24 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: martin | last post by:
Hi, We have a heavily used production server and a table which logs every hit on a web site. This table has grown large over time and we want to clear it down as efficiently as possible. We would...
3
by: LineVoltageHalogen | last post by:
Greeting All, I have a stored proc that dynamically truncates all the tables in my databases. I use a cursor and some dynamic sql for this: ...... create cursor Loop through sysobjects and...
1
by: New MSSQL DBA | last post by:
I have recently been assigned to take over several MSSQL environments and found some of the existing practice confusing. As most of my previous experiences are on Oracle and Unix platform so would...
5
by: ronin 47th | last post by:
Hi group, In one of the books 'Gurus Guide to Transact SQL' i found this info: ------------------------------------------------------------ TRUNCATE TABLE empties a table without logging row...
2
by: rdraider | last post by:
Hi, I am trying to create a script that deletes transaction tables and leaves master data like customer, vendors, inventory items, etc. How can I use TRUNCATE TABLE with an Exists? My problem is...
9
by: Sumanth | last post by:
Are there any implementations of truncate in db2. Is it going to be implemented in the future? Is there an alternate way of doing a truncate of a table that has a high record count without using...
14
by: Sala | last post by:
Hi I want to truncate all data in database ... pls help me how i ll truncate?
10
by: Troels Arvin | last post by:
Hello, Until this date, I believed that DB2 has no TRUNCATE TABLE command. But then I came across...
5
by: Timothy Madden | last post by:
Hello I see there is now why to truncate a file (in C or C++) and that I have to use platform-specific functions for truncating files. Anyone knows why ? I mean C/C++ evolved over many years...
8
by: ananthaisin | last post by:
How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not .... I have given truncate statement in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.