Executing SQL queries in VBA

[azizrasul]

I am trying to find a way of executing SQL statements using VBA. As an example, if I have an MS Excel table called "tblLicences" and I want to empty it, I can use

    sql = "DELETE tblLicences.* FROM tblLicences;"

which is what I would use if I were using MS Access. I assume that I can run a similar query (as an example) in Excel as well.

I am trying to do this using the following code behind a command button on a userform.

I am getting an error "3706: Provider cannot be found. It may not be properly installed." on the .Open line of code. I am using Excel 15.0 version on Windows 7 Ultimate.

Dim cn As Object, rs As Object, output As String, sql As String
 
    On Error GoTo ErrorHandler
 
    Set cn = CreateObject("ADODB.Connection")
 
    With cn
        .Provider = "Microsoft.JET.OLEDB.15.0;Data Source=C:\Users\Aziz\Desktop\MS Excel Files\Excel File.xlsm;Extended Properties=Excel 15.0 Macro;HDR=YES"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 15.0;HDR=YES"";"
        .Open
    End With
 
    sql = "DELETE tblLicences.* FROM tblLicences;"
    Set rs = cn.Execute(sql)
 
    rs.Close
    cn.Close
 
    Set cn = Nothing
    Set rs = Nothing

 

[ranman256]

action queries don't return a recordset. (only select queries do)
try just:
cn.execute (ssql)

 

[azizrasul]

action queries don't return a recordset. (only select queries do)
try just:
cn.execute (ssql)

I tried the following code, but I get an error.
-2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    Dim cn As Object, sql As String
    
    On Error GoTo ErrorHandler
    
    Set cn = CreateObject("ADODB.Connection")
    
    sql = "DELETE tblLicences.* FROM tblLicences;"

    With cn
        .Open
        .Execute (sql)
        .Close
    End With
    
    Set cn = Nothing