CODEWITHSUNDEEP
excelvbams-accessexcel-2016ms-access-97
error500
error500

Reputation: 25

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

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

Upvotes: 1

Views: 1690

Answers (2)

error500
error500

Reputation: 25

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.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 2

Related Questions