Clear table in Access from Excel

[tiredofit]

I am trying to clear the contents of a table in Access from Excel, using VBA.

This code works:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Option Explicit[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Sub DeleteDataUsingActiveX()
    
    Dim MoviesConn As ADODB.Connection
    Set MoviesConn = New ADODB.Connection
    
    Dim MoviesCmd As ADODB.Command
    Set MoviesCmd = New ADODB.Command
    
    MoviesConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mydb\Database2.accdb;Persist Security Info=False;"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    MoviesConn.Open
    
    MoviesCmd.ActiveConnection = MoviesConn
    
    MoviesCmd.CommandText = "DELETE * FROM Table3"
    
    MoviesCmd.Execute
    
    MoviesConn.Close
    
    Set MoviesConn = Nothing
    
End Sub

but this fails:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub DeleteDataNotUsingActiveX()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim MoviesConn As ADODB.Connection
    Set MoviesConn = New ADODB.Connection
    
    Dim MoviesData As ADODB.Recordset
    Set MoviesData = New ADODB.Recordset
    
    MoviesConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MydbDatabase2.accdb;Persist Security Info=False;"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    MoviesConn.Open
    
    With MoviesData
    
        .ActiveConnection = MoviesConn
        .Source = "DELETE * FROM Table3"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
        
    End With
    
    MoviesData.Close
    MoviesConn.Close
    
    Set MoviesData = Nothing
    Set MoviesConn = Nothing
    
End Sub

with an error on this line:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]MoviesData.Close

and the data is not cleared from that table.

My question is:

1. Why is the second method erroring
2. What is the difference between the two methods?

Thanks


[/FONT]<strike></strike>
[/FONT]
<strike></strike><strike></strike>
[/FONT]
<strike></strike>

 

[Legacy 134911]

Hello tired,

This is all you really need.

Sub DeleteDataUsingActiveX()
    
    Dim MoviesConn As New ADODB.Connection
    
    With MoviesConn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mydb\Database2.accdb;"
        .Open
        .Execute "DELETE * FROM Table3", , adCmdText
        .Close
    End With
    
End Sub

Both of your procedures return a closed recordset...

Replace:
    MoviesData.Close
With:
    Debug.Print MoviesData.State = adStateClosed

 

[tiredofit]

Thanks!