Open Access 97 MDB in Excel 2016 VBA - Trouble with my script

Question

My company recently upgraded Office 2010 to 2016 which broke some of the automation made by a previous employee. We are running some old software that uses Access 97 and do daily exports of certain tables.

I've figured out how to open the database in Excel 2016, but when I update the macro to match the settings the scripts hangs because the Data Link Properties opens up to choose the settings instead of using the settings set in the script. Here's the script, any help is greatly appreciated.

Sub WebAdsExcelMacro()

' Don't show confirmation window
Application.DisplayAlerts = False

'
' WebAdsExcelMacro Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Workbooks.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Jet.Oledb.4.0;Data Source=X:\Database\Path\DB.mdb;Password=;User ID=Admin;Mode=Sh" _
        , _
        "are Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet" _
        , _
        " OLEDB:Engine Type=4;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 OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
        , "Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$1")). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Categories")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "X:\Database\Path\DB.mdb"
        .ListObject.DisplayName = "Table_Web"
        .Refresh BackgroundQuery:=False
    End With
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Title"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Desc"
    Columns("C:C").Select
    Selection.Replace What:=". ", Replacement:=", ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],FIND("", "",RC[-2])-1)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],LEN(RC[-3])-FIND("", "",RC[-3]))"
    Range("A3").Select
    Dim WebFolder As String
    WebFolder = Environ$("USERPROFILE") & "\Save\Path\"
    LatestWebFolder = Environ$("USERPROFILE") & "\Save\Path\Latest\"
    If Len(Dir(WebFolder, vbDirectory)) = 0 Then MkDir WebFolder
    If Len(Dir(LatestWebFolder, vbDirectory)) = 0 Then MkDir LatestWebFolder
    ChDir LatestWebFolder
    ActiveWorkbook.SaveAs Filename:= _
        LatestWebAdsFolder & "Web.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
        
ThisWorkbook.Saved = True

For Each w In Application.Workbooks
w.Save
Next w
Application.Quit

End Sub

Are you using 32 bit office or 64 bit office? With 64 bit, you will probably need to install the 32 bit drivers too... i dont have the link handy, but that should steer you in the right direction. — braX, Feb 15 '21 at 15:04
I'm using 32-bit, also this same script works perfect on Excel 2010 — error500, Feb 15 '21 at 15:30
I doubt you need all those extended parameters. Why not simply without anything else: `OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Database\Path\DB.mdb`? — Parfait, Feb 16 '21 at 02:01

score 2 · Answer 1 · answered Feb 15 '21 at 15:34

2

Access 97 mdb support has been dropped in Office 2013 and subsequent versions (source).

Either downgrade back to Office 2010, or convert the database to an accdb or more modern mdb file format.

You can read here how to convert an Access 97 file to a more modern version of the mdb format, but it requires a copy of Office 2003. With a copy of the Access 2007 database engine, you can also perform the conversion, but it requires a bit of VBA (use DBEngine.CompactDatabase with dbVersion40), and simultaneous installations of the Access Database Engine and Access itself are not supported.

answered Feb 15 '21 at 15:34

Erik A

31,639
12
42
67

If you read my post it says that I got it working, my problem is with my script. Excel 2016 does in fact work with Access 97 MDB files. Change provider settings to Jet 4.0 – error500 Feb 15 '21 at 16:09
I've read your post. If you're using an unsupported configuration, anything goes, so we can't help you. – Erik A Feb 15 '21 at 16:17
From my understanding Access 97 is unsupported with Access 2016 without conversion, however opening an Access 97 in Excel 2016 is supported from my research. What's the difference between opening the file myself and getting a script to open it for me? – error500 Feb 15 '21 at 18:24
Technically, OP is connected to database via backend and never directly opening it with frontend Access.exe. As long as driver/provider is available, one should be able to run this QueryTable code. In fact this code can work without Access installed and can be used for other DBs: SQLite Oracle, MySQL, etc. swapping out the source connection. – Parfait Feb 16 '21 at 01:58
@Parfait AFAIK support has been dropped entirely, so from the database engine too, and it can't be run without the Access Database Engine (unless you're using MDAC) – Erik A Feb 16 '21 at 07:17
Got it. Might be due the `Jet 3.x IISAM` driver. Though I am curious to test it. – Parfait Feb 16 '21 at 17:51

error500 · Accepted Answer · 2021-03-12T22:45:53.827

I found a workaround for my own problem so I'm posting to maybe help others in similar situations.

Access 97 MDB does work in Office 2016, first you need to download the Microsoft Access Database Engine 2010. Once you have that installed open your MDB file, a Data Link Properties window will pop up. Select Microsoft OLEDB Jet 4.0 from the Provider list then enter the path and security information under the connection tab. Hit OK and it will open successfully.

You can automate programmatically afterwards with an Auto Hot Key script.

Run, EXCEL.exe, C:\Program Files (x86)\Microsoft Office\root\Office16, max, ahk_excel
WinWait ahk_pid %ahk_excel%
sleep, 2000
send ^q ; This is my Excel macro hotkey for my VBA script which opens my mdb file, normally my VBA script would hang here because it wouldn't pass along the OLEDB Provider info.
sleep 1000
send +{TAB}
send {LEFT}
send {TAB}
send {UP}
send {TAB}
send {SPACE}
send X:\Database.mdb
send {TAB}
send {TAB}
send {TAB}
send {TAB}
send {TAB}
send {TAB}
send {ENTER}
sleep 5000
send {ENTER}

I don't know if this will help anyone else but it worked for my needs.

Thanks for posting your workaround, but it looks awfully fragile to use in a production environment just to support a file format that was officially killed off when Access 2013 was released eight (8) years ago. — Gord Thompson, Mar 12 '21 at 23:56
Not every one can convince the decision makers to make proper upgrades, some are forced to just figure it out — error500, Mar 14 '21 at 01:30

Open Access 97 MDB in Excel 2016 VBA - Trouble with my script

2 Answers2