By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

Truncate in VBA

P: 48
Hi,

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

Thanks
Jan 22 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,685
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
Expert 100+
P: 897
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

P: 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
Expert 5K+
P: 8,685
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
Expert 100+
P: 897
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
Expert 5K+
P: 8,685
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

Post your reply

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