Excel-VBA SQL query returns blank when even if there is one record in the column

user_10356418 0
Viewed 1491

I'm writing a SQL query in Excel VBA but it returns blank column (doesn't read the value)

Query - Select Distinct [Column1] from [TableName]

There are 29k rows in the [Column1], out of which Row #48 has some value and all other are null (Blank). The code above returns only null (Blank) value. However i Tested some cases and following are my observations -

1.If I copy the value from Row #48 and move to Row #1 (deleting the original value at Row#48), the query works

  1. If I add some other values in the column, the query works and sometimes it doesn't depending on which Rows I add the Value in

  2. I've checked the Range of [TableName], it covers the required columns and Rows

  3. The query works fine on columns which have multiple values
  4. Tried changing format of the cells but didn't help

Appreciate it if someone could tell me why is this happening and How should i go ahead Correcting this.

vba excel sql
2 Answers

From: https://docs.microsoft.com/en-us/sql/odbc/microsoft/setting-options-programmatically-for-the-excel-driver?view=sql-server-2017

Rows to Scan The number of rows to scan to determine the data type of each column. The data type is determined given the maximum number of kinds of data found. If data is encountered that does not match the data type guessed for the column, the data type will be returned as a NULL value.

For the Microsoft Excel driver, you can enter a number from 1 to 16 for the rows to scan. The value defaults to 8; if it is set to 0, all rows are scanned. (A number outside the limit will return an error.)

Likely what is happening is the Excel driver scans the first x rows, guesses a data type, then the later rows don't match that type and so get assigned as null. This is why moving the value up fixes the problem. Try setting to scan all rows.

user_4788840

The Setting is here: \HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

I have changed "TypeGuessRows" from 8 to 0, and now the queries work!

user_134632820
Related