none
Problems Using ACE.OLEDB on Windows 10 with Office 365 (Click to Run). Where is TypeGuessRows? RRS feed

  • Question

  • Some years ago, I wrote a command line program to extract the contents of Excel Worksheets and/or named Ranges. The original used the JET engine, which of course is built into all versions of Windows. The current version first checks ACE, the Access Database Engine, and uses that, if found. Of course this is necessary to handle the newer Excel formats, mostly .xlsx, which of course JET does not understand. In addition, I set TypeGuessRows in the registry to check more than the first 8 rows, because we often get data that includes both numeric and alphanumeric data in the same column, which should be treated as text, and there is no way to specify that all data should be treated as text. (An example is postal codes, where U.S. zip codes are numeric, but Canadian code include alphabetic characters. If the first eight rows are only zip codes, then the Canadian postal codes are discarded.) While I sometimes use this tool from a command prompt, it is mostly used in a server process that processes incoming emails. If the email contains an Excel file as an attachment, then the data is extracted as CSV file(s) and processed. Naturally, I want to avoid Office automation on a server. All this works fine on the server, and worked fine for development and testing on my old desktop, which was Windows 7 (32 bit) with Office 2007. A few months ago, I replaced my desktop. The new desktop is Windows 10 (64 bit) with Office 365 (2016) 32 bit, installed as Click-to-Run. The application in question is generated for 32 bit. The application did not run on my desktop. The first problem is that the Office install did not install the Access Database Engine, as I would have expected. So I installed Access Database Engine 2010 Redistributable. (Should I have installed 2016 version?) So now I can find ACE.OLDB, but TypeGuessRows is not in the expected location in the Registry, but way deeper due to Click-To-Run install. Should I search out this location in my program? Will ACE 2010 find and use its value from the new location that Office 2010 never used? I assume ACE is self-contained and does not need Excel installed. Should I create a TypeGuessRows value at a location that ACE knows about? Why is this so tricky. Why can't JET be updated to handle the new Excel format? Can anyone help?
    • Moved by Hart Wang Monday, August 21, 2017 8:15 AM
    Thursday, July 6, 2017 6:50 PM

All replies

  • since this is an Office compatibility issue, ask at https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev.


    Visual C++ MVP

    Thursday, July 6, 2017 8:04 PM
  • Hi SYSPAU,

    Thank you for posting here.

    I am not sure what the current version of operating system is ? If the operating system is windows 10, you must use the Microsoft Access Database Engine 2016 Redistributable. Since the Microsoft Access Database Engine 2010 doesn't support windows 10.

    Since my computer is windows 10, I will install engine 2016, If I have new information, I will tell you.

    >>" So now I can find ACE.OLDB, "

    Did you install the database engine 2010 successfully except some information cannot be written into registry?  Did you try to manually configure the information with windows environment?

    Best Regards,

    Hart

    EDIT:

    When you install the access database engine 2010, you need to choose the appropriate version(32bits or 64bits ), otherwise you cannot install engine successfully.

    The below is my screen shot,


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Hart Wang Friday, July 7, 2017 2:54 AM
    Friday, July 7, 2017 2:46 AM
  • Thank you for your reply.

    I installed Microsoft Access Database Engine 2010 Redistributable (32-bit). I realize it is not supposed to support Windows 10, but I also saw warnings, IIRC, about mixing MSI installs with C2R installs (use /passive option when installing, else it refuses to install, and I verified that). Hence, I am unsure which version to install, also the version number in the connect string changes, but that's probably good, and I need to make mods to my code anyway.

    The other problem had to do with a couple Registry settings, mainly TypeGuessRows. Years ago, when I wrote the original version for JET, the only way to force the engine to scan all rows was to change this value. With ACE, there is a connection option, MAXSCANROWS, which should accomplish the save thing, the right way, and much easier. I remember that originally, this option was simply ignored, so back to the Registry (TypeGuessRows for ACE). I had forgotten about the option that didn't use to work, but now I will give it a try. If I install 2016 Redistributable (different version number), then I can determine that I should also use MAXSCANROWS instead of mucking with the registry, which I never really wanted to do to begin with.

    So I will try to replace 2010 version with 2016, check for that version, and use connect string option in that case. I will report back once I try that.
    Tuesday, July 18, 2017 7:57 PM
  • Hi,

    Did get the new information for the issue. if the issue has been resolved by yourself, please remember to close the thread by marking useful posts as answer.

    Best Regards,

    Hart


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 21, 2017 3:11 AM
  • So I've finally gotten back to this problem after taking care of more pressing issues. Results are not what I had hoped for. Extended attribute "MAXSCANROWS=0" is described in the connect string documentation, but it does not work. I had hoped I could give up on fiddling around with registry values, but no. I have to search for the location of TypeGuessRows. I am targeting x86 because most Office installs are 32-bit (at least mine is), so I installed the ACE 2016 download since apparently no ACE engine was installed with my Office 365 (2016). On 64-bit Windows (10 in my case), the registry path includes WOW6432Node in the path, but others here have 32-bit Windows, so another path to check, actually several, as it's not necessarily Microsoft.ACE.OLEDB.16.0. It could be 12.0, it that engine was installed. I just checked a machine with Office 2010, and apparently there's a 14.0 there. This is MADNESS. Why doesn't MAXSCANROWS work as advertised? My goal is rather simple: a command line tool that dumps worksheets from an Excel workbook to CSV files, spitting out exactly all the strings that were entered into all the cells. If the first few rows of a column are numeric, but other cells are alphabetic, I want all to be returned as strings. Simple. 
    Tuesday, August 8, 2017 10:07 PM
  • Forgot to mention that now in Windows 10, this now requires "Run As Administrator" privileges since it need to muck with the Registry. BIG minus, as normally this would run in a batch file, or in a manages Process from another .NET program. In an automated procedure, there is no one to click OK to run as administrator. In the seven or so years since I wrote the original version, I thought for sure that by now it would have been fixed to work as is should.
    Tuesday, August 8, 2017 10:26 PM
  • Hi,

    Thank you for your feedback. 

    >>"  It could be 12.0, it that engine was installed. I just checked a machine with Office 2010, and apparently there's a 14.0 there. This is MADNESS. Why doesn't MAXSCANROWS work as advertised? My goal is rather simple: "

    You can write your suggest on user voice website, the next product maybe add the new feature.

    Best Regards,

    Hart


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 14, 2017 7:46 AM
  • This shouldn't be a new feature. The MAXSCANROWS option has been documented for years now, but never implemented, apparently. The fact that the option is ignored forces me to find and change TypeGuessRows in the registry, which is something I shouldn't have to do. The little program I wrote here is a command line utility that will list and/or extract worksheets or named ranges as CSV files. I have another program that receives and processes emails. If an email has an Excel attachment, that program calls (using System.Diagnostics.Process) ExcelExtract to list the contents and then extract the data. I collect the log from StandardOutput. If I try this in Visual Studio debug on my Windows 10 machine, it fails on registry access (to HKLM whatever path to TypeGuessRows). So some emails I cannot test on my own machine, which is very annoying. This worked on my old Windows 7 machine, which of course also had UAC, but I guess not as strict. Interestingly, I can run ExcelExtract in Visual Studio debug just fine. If I run the executable from a command prompt (not as administrator), it prompts for permission, which is undesirable, but even worse, when I do give permission, it runs the command in another command window (as administrator), which disappears before I can even read the output, let alone save it. The server where this runs in production is still Server 2003, and at this rate we may never be able to upgrade.
    Tuesday, August 15, 2017 7:48 PM
  • Hi SYSPAU,

    Thank you for your explanation. 

    I think that it is related to product design, I cannot provide more support, i also suggest that you can post the issue on user voice website and ask why it isn't implemented. 

    Since the issue is not related to development, According to MSDN policy, i will move the case to off-topic forum.

    I hope that you can understand me. 

    Best Regards,

    Hart


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 16, 2017 3:09 AM
  • Thank you Hart for all your replies. Unfortunately for me, nothing has been resolved, and the issue has morphed a bit as well. I'll post an issue on user voice and hope for something positive, eventually. Again, thank you for responding. Keep well!
    Wednesday, August 16, 2017 4:13 PM