Truncate in VBA

[wquatan]

Hi,

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

Thanks

[ADezii]

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.
   

   ALTER PROCEDURE dbo.StoredProcedure1
   AS TRUNCATE TABLE  dbo.Authors

2. Execute the Stored Procedure via an ADO Command Object.
   

   Dim cnn As ADODB.Connection, cmd As ADODB.Command
    
   Set cnn = CurrentProject.Connection
   Set cmd = New ADODB.Command
    
   cmd.ActiveConnection = cnn
   cmd.CommandText = "StoredProcedure1"
   cmd.CommandType = adCmdStoredProc
    
   cmd.Execute
    
   Set cmd = Nothing
   Set cnn = Nothing
    

3. Hope this helps, I'm sort of out-of-my-field on this one!

[Jim Doherty]

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 :)

[wquatan]

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

[ADezii]

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.

[Jim Doherty]

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)

 On Error Resume Next 
Dim mydb As DAO.Database
Dim qdf As DAO.querydef
Set mydb = CurrentDb
Set qdf = mydb.CreateQueryDef("")
With qdf
.Connect = "ODBC;DSN=yourservername;UID=username;DATABASE=yourdbname;Trusted_Connection=Yes"
.SQL = "TRUNCATE TABLE dbo.tblBlah"
.ReturnsRecords = False
.Execute
.Close
End With
'If you wish the query to persist in the database
'as a permanent query then you MUST supply a name for the query
'shown between speechmarks in the createquerydef("") statement
'ie replace createquerydef("") with createquerydef("qryTruncate") for instance.
'if you do save it to the database as a permanent
'query then uncommenting the below line will delete it once this routine ends
'CurrentDb.QueryDefs.Delete (qdf.Name)
 
 

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 :)

[ADezii]

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)

 On Error Resume Next 
Dim mydb As DAO.Database
Dim qdf As DAO.querydef
Set mydb = CurrentDb
Set qdf = mydb.CreateQueryDef("")
With qdf
.Connect = "ODBC;DSN=yourservername;UID=username;DATABASE=yourdbname;Trusted_Connection=Yes"
.SQL = "TRUNCATE TABLE dbo.tblBlah"
.ReturnsRecords = False
.Execute
.Close
End With
'If you wish the query to persist in the database
'as a permanent query then you MUST supply a name for the query
'shown between speechmarks in the createquerydef("") statement
'ie replace createquerydef("") with createquerydef("qryTruncate") for instance.
'if you do save it to the database as a permanent
'query then uncommenting the below line will delete it once this routine ends
'CurrentDb.QueryDefs.Delete (qdf.Name)
 
 

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.