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.