Delete Records in a Excel table using RecordSet ADO

[Manuel Cavero]

Hello everyone:

I'm trying to delete all records in a RecordSet ADODB Object connected to a Excel Sheet, using two differents ways:

1. Delete records using an ADODB Connection Object
2. Delete records using an ADODB RecordSet Object

Also, as far as I know MS recommend to use the 2nd

So, what I'm trying to do is:

Dim Conn as string
Dim RS as adodb.Recordset

Conn = _[INDENT]"Provider=Microsoft.ACE.OLEDB.12.0;" & _[/INDENT]
[INDENT]"Data Source= " & Path & ";" & 
"Extended Properties=Excel 12.0;"

[/INDENT]
Set RS = New adodb.RecordSet

With RS
    .ActiveConnection = Conn
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Source = "SELECT * FROM [FTE$]"
    .Open
    Set .ActiveConnection = Nothing
    .Delete 1
    .ActiveConnection = Conn
    .Update
    .Close
End with

What I'm trying with this code, it's delete all records from the table FTE, but no one is deleted....

Thanks a lot and have a nice day!

 

[ranman256]

cant you use the code to delete the cells in excel?
why ADO?

 

[Manuel Cavero]

Hello ranman256

The code doesn't delete the cells, the code does delete the Recordset records...And I'm using ADO because its the only way I know to operate with a closed file. Do you know another way?

Thanks a lot ranman256, have a nice day!

 

[Kyle123]

Why don't you use sql to do the delete instead of trying to use a disconnected recordset?

 

[Manuel Cavero]

Hello Kyle

I tried, but unfortunately did not work. I used a Connection ADODB as this:

Dim Conn as ADODB.Connection

With Connn
    .ConnectionString = _[INDENT=4]"Provider=Microsoft.ACE.OLEDB.12.0;" & [/INDENT]
[INDENT=4]"Data Source= " & Path & ";" & _[/INDENT]
[INDENT=4]"Extended Properties=Excel 12.0"[/INDENT]
    .CursorLocation = adUseClient
    .Open
    .Execute "DELETE * FROM TABLE"
End With

Many thanks Kyle and have a nice day!!

 

[Kyle123]

It should be

DELETE FROM [FTE$]

 

[Manuel Cavero]

Hello Kyle; you are right about the name....

I tried but I've get an error with a message that says:

Error Number -2147467259 (80004005) on execution time
This ISAM does not admit the deletion of data in a linked table....????

But the table it is not linked to another one, nor has a connection....

Dim Conn           As New ADODB.Connection
Dim RS              As New ADODB.Recordset
'Delete
With Conn
    .ConnectionString = _[INDENT=3]"Provider=Microsoft.ACE.OLEDB.12.0;" & 
"Data Source=Path" & _
"Extended Properties=Excel 12.0;"[/INDENT]
    .CursorLocation = adUseClient
    .Open
    .Execute "DELETE * FROM [FTE$]"
    .Close
End With

What's happening?