Updating Legacy VBA to the 21st Century

Hi,

I am starting to work with a file written in excel 97 & access 97 for forecasting, it is fairly involved and does a great job of profiling etc. However the business has undergone an upgrade and we are all now using Windows 7 & Office 2013 (x64), from what was previously XP & 2003/7

As a result the coding, written by a number of parties over the years, is now failing

Upon opening the file I encounter an Run-Time error 3706, falling over the

Set dbConn = New adodb.Connection
      dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=" & ThisWorkbook.path & "\" & mdb_name

Following my practice of Google and forums, which until now have solved any/all problems I found and installed the Microsoft Access Database Engine 2010 Redistributable but this has not solved this issue - although that might have been too easy.

I have straight swapped the code to

  Set dbConn.ConnectionString = "Provider=microsoft.ace.oledb.12.0;" & _
                          "Data Source=" & ThisWorkbook.path & "\" & mdb_name

which changes the error to a Compile error "Can't find project or library"

I would very much not have to go down the route of virtual machines and x86 software to continue with this, but it is critical to my sanity that I get this working... and a total re-write I feel is beyond my skill and the time I have available.

Any thoughts?