title: Dealing with TypeGuessRows in Registry
body: <p>I have a VB.net application which needs to read and write Excel workbooks, using OLEDB and the MS Office Access Database driver.  Over time, I have learned a vast amount here on StackOverflow which allowed me to get everything working the way it should be.  Those topics covered everything from connectionstrings to the &quot;magic&quot; TypeGuessRows setting / 255 character limitation, now located in the registry.  Over time, that issue by itself has morphed due to the evolutions by Microsoft over the years, so I won't reference those (hundreds? thousands?) of posts here.  Suffice it to say that I was able to piece it all together and get it working.</p>
  <p>For reference: my connectionstring looks like this:</p>
  <pre><code>&quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source='@DBQ';Extended Properties='Excel 12.0 Xml;HDR=YES';&quot;
  </code></pre>
  <p>where '@DBQ' is replaced at runtime by the appropriate pathname of the selected Excel workbook. Note that it does not contain TypeGuessRows (since that has moved to the registry), and IMEX=(whatever) has not effect either, so it does not appear.  The result is that everything works quite well, AS LONG AS TYPEGUESSROWS is set to zero in the registry.</p>
  <p>Using regedit to manually hunt down and set TypeGuessRows=0 in all occurrences (one of the things I learned about here on StackOverflow), this has worked and avoids the 255-char truncation perfectly.</p>
  <p>The problem is that whenever there is a Windows update, it gets set back to the default value, 8. There is no warning or announcement of this; I only know about it when I start seeing the &quot;truncated at 255 characters&quot; symptom showing up again.  So then I have to go back and use RegEdit again.</p>
  <p>The obvious solution would be that the app does this setting on its own, but that seems to require manipulation of the Security features, which is something I'd prefer to avoid entirely as it seems to be more complex than I want to deal with presently.</p>
  <p>So, I thought, &quot;why not just scan the appropriate registry keys, and report if they are not zero&quot;, then issue a message to the user to get it sorted using regedit.</p>
  <p>I wrote the following function, which looks at all of the various registry keys I have found which contain TypeGuessRows:</p>
  <pre><code>    Public Function CheckRegistry() As Integer
      Dim Val1 As Integer = 0, Val2 As Integer = 0
      Dim KeysToCheck As String() = New String() {
         &quot;HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel&quot;,
         &quot;HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Lotus&quot;,
         &quot;HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel&quot;,
         &quot;HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel&quot;
          }
      '
      '   try two methods for checking the registry values.
      '
      '   method 1 uses the My.Computer class
      '
      For Each Key As String In KeysToCheck
          Dim aVal As Object = My.Computer.Registry.GetValue(Key, &quot;TypeGuessRows&quot;, CType(&quot;BOGUS&quot;, Object))
          If IsNothing(aVal) Then                         ' did we hit Nothing?
              Continue For                                ' have to skip it
          ElseIf aVal.Equals(CType(&quot;BOGUS&quot;, Object)) Then ' did we hit one not exist?
              Val1 = -9999                                ' set a crazy value
          End If
          Val1 += CInt(aVal)                              ' otherwise, get its value and add to total
      Next
      '
      '   method 2 uses the RegistryKey class
      '
      Dim aKey As Microsoft.Win32.RegistryKey
      For Each Key As String In KeysToCheck
          Dim LM As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.LocalMachine   ' point to HKLM
          Key = Key.TrimStart(&quot;HKEY_LOCAL_MACHINE\&quot;.ToCharArray)                          ' peel off the start (a convenience)
          aKey = LM.OpenSubKey(Key, Microsoft.Win32.RegistryKeyPermissionCheck.ReadSubTree)   ' try to read the subkey
          If IsNothing(aKey) Then Continue For                                            ' did we hit Nothing?  have to skip it
          'If CInt(aKey.GetValue(&quot;TypeGuessRows&quot;, 0)) &lt;&gt; 0 Then aKey.SetValue(&quot;TypeGuessRows&quot;, 0)
          Val2 += CInt(aKey.GetValue(&quot;TypeGuessRows&quot;, 0))                                 ' otherwise, get its value and add to total
      Next
      Return Val1 + Val2                                  ' return the sum of both methods
  End Function
  </code></pre>
  <p>This works well enough, EXCEPT FOR THE LAST ONE, the one with &quot;...ClickToRun...&quot; in the key name.
  No matter what, this ALWAYS returns as Nothing, so the attempt to check it fails. Even in the first method, where I specifically ask it to return a &quot;BOGUS&quot; object, it always returns as Nothing.</p>
  <p>The problem is that it is THIS KEY which gets updated by Windows Update.  Thus far I have never seen any of the other three get reset.</p>
  <p>If I &quot;walk the tree&quot; of this key (i.e., try looking at &quot;HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office&quot;), it works fine until I hit the ClickToRun branch, at which point it just returns Nothing.</p>
  <p>So, I guess I have two questions:</p>
  <ol>
  <li>Is there a way to read this key so that it does not return as Nothing?</li>
  <li>Can someone provide a (hopefully not too messy / complex) guide to getting the Security settings correct to allow &quot;ReadWriteSubTree&quot; for the permission check?  N.B. if I try that now, I get Security exception, telling me that the app does not have the appropriate permission. That's why the aKey.SetValue line is commented out.</li>
  </ol>
  <p>Further info:
  the machine is Win 10 Pro, 64-bit, with appropriate drivers installed. The app is set to &quot;Enable ClickToRun security settings&quot;, and &quot;full trust application&quot;.  I have played with those settings and the appropriate entries in app.manifest, but thus far that only causes (much) other grief and difficulties elsewhere.  That's why I'd prefer to not mess with it.</p>
  <p>Any help would be really appreciated.</p>