conn.Open Error

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,263
I am trying to run some SQL statements within a MS Excel file that contains Excel tables.

When I try to run Sub Test(), I get an error on the conn.Open line i.e.

-2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Any idea where I am going wrong? The code was extracted from a YouTube video called 'Building Excel SQL Query Application Part 1 - Write SQL Statement in Excel to query Excel tables'

I am using Excel version 15.0.

Code:
Sub Test()
    
    QueryExcel ("SELECT tblLicences.Field1 FROM tblLicences WHERE (((tblLicences.Field1)='" & MotherBoardSerialNumber() & "'));")
    
End Sub

Public Sub QueryExcel(ByVal SQL_Statement As String)

    Dim conn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim sConnection As String, sSQL As String
    Dim ws As Worksheet
    Dim i As Integer, iCheck As Integer
    Dim num_records As Long
    
    On Error GoTo errHandle
    
    sConnection = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" & ActiveWorkbook.FullName & _
    ";Extended Properties=Excel 15.0;HDR=YES;IMEX=0;ReadOnly=False"""
    
    Set conn = CreateObject("ADODB.Connection")
    Set rst1 = CreateObject("ADODB.Recordset")
    
    sSQL = UCase(SQL_Statement)
    
    conn.Open
    
    If Left(sSQL, 6) = "UPDATE" Or InStr(sSQL, "INSERT INTO") = 0 Then
        conn.Execute sSQL, num_records
        MsgBox num_records & " records affected.", vbInformation
    Else
        rst1.Open sSQL, conn, adOpenDynamic, adLockOptimistic
        Set ws = Worksheets.Add
        With ws
            .Move , ThisWorkbook.Worksheets(Sheets.Count)
            .Range("A2").CopyFromRecordset rst1
            For i = 0 To rst1.Fields.Count - 1
                .Cells(1, i + 1) = rst1.Fields(i).Name
            Next
        End With
    End If
    
Door:
    If rst1.State <> 0 Then rst1.Close
    If conn.State <> 0 Then conn.Close

    Set rst1 = Nothing
    Set conn = Nothing
    Set ws = Nothing
    
    Exit Sub
    
errHandle:
    MsgBox Err.Number & ": " & Err.Description
    GoTo Door

End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,550
Members
416,923
Latest member
jarri

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