How to import Table from Access DB into Excel using vb.net

Question

I'm trying to import a table from Access into an Excel sheet.

I found some answer on Google saying to Dim a connection and recordset as Dim cnn As ADODB.Connection, but it doesn't work in VB.Net.

Dim ws As Worksheet
Dim file As String

ws = Globals.ThisAddIn.Application.ActiveSheet
file = "C:/Downloads/db.accdb"

With ws.QueryTables.Add("OLEDB;" & file, ws.Range("A1"), "SELECT * FROM [TableName]")
    .Name = "Source Data Table"
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh(False)
End With

I don't understand how the connection from QueryTables.Add() works, and I get an error at .Refresh(False).

Answer 1

Seems like the error was this part:`

ws.QueryTables.Add("OLEDB;" & file, ws.Range("A1"), "SELECT * FROM [TableName]")

It should be like this:

ws.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file, ws.Range("A1"), "SELECT * FROM [TableName]")

After that, the data was successfully added into Excel.