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

Question

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

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
I've checked the Range of [TableName], it covers the required columns and Rows
The query works fine on columns which have multiple values
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.

Does the query work if the value is moved to row 2 (ie checking to see if it only works if some data is in row 1)? Also, are there other columns in the table and do they have values in the rows where [Column1] is blank/null? — mcalex
– mcalex, Commented Sep 13, 2018 at 4:33
I'm checking the results using a simple query which is - " Select distinct [territory] from [RawData$A10:ES29100]" — jaspreet
– jaspreet, Commented Sep 13, 2018 at 6:43
@mcalex yes it works when i write something in row #2. i tried some random row #s - It does not work around row 40 or something — jaspreet
– jaspreet, Commented Sep 13, 2018 at 6:44

Tim Williams · Accepted Answer · 2018-09-13 05:07:25Z

2

From: https://learn.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.

answered Sep 13, 2018 at 5:07

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Williams Over a year ago

Maybe also see: concentra.co.uk/blog/…

Rohit Saluja Over a year ago

It would be really helpful if you can also provide the example for the connection string with TypeGuessRows set to 0.

jaspreet Over a year ago

I Tried the following connection string but it didn't help With cnn .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "excel 12.0;IMEX=1;MAXSCANROWS=0; TYPEGUESSROWS=0" .Properties("Data Source") = ThisWorkbook.FullName .Properties("Jet OLEDB:DataBase PassWord") = PWD .Open End With

Tim Williams Over a year ago

TypeGuessRows is a registry setting

Tim Williams Over a year ago

Try adding the IMEX setting.

Walerij Bogdanow · Accepted Answer · 2021-04-19 02:07:55Z

0

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!

answered Apr 19, 2021 at 2:07

Walerij Bogdanow

213 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related