VBA code to import .mdb file into excel 2013

[imalc]

I’m still only a beginner with VBA and I’m struggling to import data from a MDB file, I have tried to Google it but not found any code that works. I have a program “ASoft32” that stores its data in a .mdb file (WorkData). When I try and import it to Excel 2013 it comes up with “Cannot open database created in a previous version of your application Is it possible using VBA to convert the file then import it into Excel 2013?

[mdmackillop]

Don't know if this helps. I downloaded the Access sample database NorthWind.mdb. It imports OK to Excel 2016 using this code.

Sub Test()


    Dim Srce As String
    Dim Tbl As String
    Dim DipName As String
    
    Srce = "C:\VBAX\Nwind.MDB"
    Tbl = "Employees"
    Dispname = "TableNwind"
    
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Srce & ";Mode=Share Deny Write;Extended Propert" _
        , _
        "ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
        , _
        "Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
        , _
        "="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLE" _
        , _
        "DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Valid" _
        , _
        "ation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array(Tbl)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = Source
        .ListObject.DisplayName = Dispname
        .Refresh BackgroundQuery:=False
    End With
End Sub

[imalc]

Don't know if this helps. I downloaded the Access sample database NorthWind.mdb. It imports OK to Excel 2016 using this code.

Sub Test()


    Dim Srce As String
    Dim Tbl As String
    Dim DipName As String
    
    Srce = "C:\VBAX\Nwind.MDB"
    Tbl = "Employees"
    Dispname = "TableNwind"
    
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Srce & ";Mode=Share Deny Write;Extended Propert" _
        , _
        "ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
        , _
        "Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
        , _
        "="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLE" _
        , _
        "DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Valid" _
        , _
        "ation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array(Tbl)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = Source
        .ListObject.DisplayName = Dispname
        .Refresh BackgroundQuery:=False
    End With
End Sub

When I run this i'm getting an error [error 1004] on the last line of the code!

[mdmackillop]

I obviously can't test it. What is the error description?

Edit: Can you post the mdb file?