Accessing SQL Database in Excel-Vba

Accessing SQL Database in Excel-VBA

I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "select * from myTable"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL            

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

Unable to connect to SQL database via Excel VBA

Use the new Microsoft OLE DB Driver for SQL Server or even the new Microsoft ODBC Driver for SQL Server.

See Using ADO with OLE DB Driver for SQL Server for guidance on updating VB, VBA, and ASP.OLD apps to use the new driver.

How do I run a query from Excel VBA

Essentially, you are connecting to MS Access in two different ways: frontend with the Access COM object and backend with ADO. Additionally, you are combining two DB APIs, DAO with CurrentDb and ADO with ADODB.Connection, which both have recordset objects.

Consider either using first connection via the Access COM application and DAO or close the COM object and connect to new database with ADO.

Approach 1: Run all operations with COM connection and DAO

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' INITIALIZE DAO DATABASE
Set dbss = objAccess.CurrentDb

' OPEN DAO RECORDSETS
TMPQueryN = "SELECT * FROM [N]"
TMPQueryO = "SELECT * FROM [O]"
Set TMPRecordsetN = dbss.OpenRecordset(TMPQueryN)
Set TMPRecordsetO = dbss.OpenRecordset(TMPQueryO)

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE DAO OBJECTS
TMPRecordsetN.Close: TMPRecordsetO.Close
Set TMPRecordsetN = Nothing: Set TMPRecordsetO = Nothing: Set dbss = Nothing

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase
objAccess.Quit

Set objAccess = Nothing

Approach 2: Close COM connection without DAO and open ADO connection

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase()
objAccess.Quit()

Set objAccess = Nothing


' CONNECT TO DATABASE VIA ADO -----------------------------------------------------
Dim TMPConnection As ADODB.Connection
Dim TMPRecordsetN As ADODB.Recordset, TMPRecordsetO As ADODB.Recordset
Dim TMPQueryN As String, TMPQueryO As String

' OPEN CONNECTION
Set TMPConnection = New ADODB.Connection
MPConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath
TMPConnection.Open TMPConnectionString

' OPEN ADO RECORDSETS
Set TMPRecordsetN = New ADODB.Recordset
TMPQueryN = "SELECT * FROM [N]"
TMPRecordsetN.Open TMPQueryN, TMPConnection

Set TMPRecordsetO = New ADODB.Recordset
TMPQueryO = "SELECT * FROM [O]"
TMPRecordsetO.Open TMPQueryO, TMPConnection

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE ADO OBJECTS
TMPRecordsetO.Close: TMPRecordsetN.Close: TMPConnection.Close
Set TMPRecordsetO = Nothing: Set TMPRecordsetN = Nothing: Set TMPConnection = Nothing

How do I set up a connection to query data inside an Excel .xls file using Excel 2002?

Connection issue

First, there was an error in your Provider string, it should not contain the part with Data Source=C:\MyExcel.xls; since this is part of the connection string. So it should look like this:

    .Provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=1"

ODBC vs OLEDB

I've never used ODBC, but based on this answer, you can't use it to query an Excel file, so OLEDB is the way to go.

---

Insert Statement

Once you have a working ADODB connection, insert query should work as hoped. I'm providing an example below that worked for me, but there is a few caveats:

• I'm using the ACE.OLEDB.12.0 instead of JET.OLEDB.4.0 with Excel for Microsoft 365 MSO (Version 2112 Build 16.0.14706.20000) 64-bit on Windows 10.

• I'd suggest to set Mode=ReadWrite in your connection string to avoid potential writting permission issues (but it might work even without it.).

• Regarding the IMEX setting, I was having errors when it was set to IMEX=1, so I switched to IMEX=0 (see related question.

The example

With a workbook named Data.xls with the first sheet named Data and the following data :

^{Data for copy-paste}

I can run the following:

Dim wb As Workbook
Set wb = Workbooks("Data.xls")
Dim ws As Worksheet
Set ws = wb.Worksheets("Data")

'Create connection
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .ConnectionString = "Data Source=" & wb.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=0;Mode=ReadWrite;"
    .Open
End With

'Compose the INSERT statement.
Dim query As String
Const sep = ", "
query = "INSERT INTO [" & ws.Name & "$] " & _
    "(Id, Name, Age) " & _
    " VALUES (" & _
    4 & sep & _
    "'" & "Joe" & "'" & sep & _
    40 & _
    ")"

'Execute the statement.
conn.Execute query, adCmdText

'Close the connection
conn.Close

And it should insert the data as follow:

---

Should you use ACE or JET?

If JET works for you, you might as well use it. Based on this article , you should also have the 32-bit version of ACE available with Windows 7 to work with Excel 2002 (32-bit), but based on your comment it seems like it's causing some problems.

See also some interesting answer about JET vs ACE.

---

Related Topics