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

So I'm trying to import an specific table from Access into an excel sheet.

I was trying to record a macro to see how that worked, but since I already know how to do it with a txt file, I thought it could be similar, but I can't make it work. I tried many things and also found some answer on google saying to Dim a connection and recordset as Dim cnn As ADODB.Connection and so, but it doesn't work in VB.Net.

Maybe is dumb but I don't know much about this. Here is my code.

    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 really don't understand how the connection from QueryTables.Add() work, and I always get an error at .Refresh(False) so.. I was trying everything I could imagine also looking to what the macro recorder but can't still understand.

CodePudding user response：

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.