Executing SQL queries in VBA

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,262
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

Code:
    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.

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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,954
action queries don't return a recordset. (only select queries do)
try just:
cn.execute (ssql)
 

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,262
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,620
Messages
5,597,209
Members
414,131
Latest member
Balakrishnan2027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top