I have this Excel Spreadsheet where I need to make some queries. I've been using ADO OLEDB driver to use SQL language, because it's simpler than having to loop through find calls. It has been tested and used on several different Window and office versions (32 and 64 bits) and always it worked as intended. Yesterday a couple of coworkers stared to use it on their old machines (Windows 7 and Offices 2013 32bits) and this is where problems began.
I keep getting this error that says and quote "The Microsoft Access database engine could not find the object 'C:\Users...\Spreadsheet.xlsm'. Apparently, in some of those Computers, the driver is unable connect to the very same Excel file that is calling from, which is weird because that never happened before and this is not a M. Access file (which usually get locked once they are open). If I duplicate the file,and change its name and the driver source path to the copied file, all it works fine, so apparently the driver is working "fine". The last weird part is that there are several machines with similar environments and only in some of them it works.
Sadly I can't get to reproduce the error in my PC or any other.
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
cn.Open strCon 'This is where I get the error.
If i make a copy of the Excel spreadsheet and put it on C:\ (for example), and then change the path on the already opened excel file to the copied one, it works fine.
Something like this
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\UnopenCopyOfTheFile.xlsm" _
& ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
cn.Open strCon 'This works, but i end up with two files.
Does anybody know what could be causing this weird behavior?
I'm gonna answer my own question because it was a problem of enviroment and not really a programing issue. As I suspected, there was a problem with libraries in general, in particulary the one responsible with dealing with Access files (I couldn't even open a data source to Access file from Excel), so I reinstall the Access engine drive and restored/repaired the Microsoft Office installation. That made the trick.