SQL VBA Query not picking up letter characters, only fully numeric fields

I've got a sub that I'm running which is basically taking the data from one sheet, running a SQL query on it and depositing the results into another sheet. I am noticing that while it is running 'correctly' the fields that should be coming through that have letters in them do not show end up coming through (pasted as blanks). Is there something I'm missing? Code below:

Sub sql_group()

Dim connection As New ADODB.connection
Dim rs As New ADODB.Recordset, arr As Variant, rs1 As New ADODB.Recordset
Dim sh As Worksheet, SQLQuery As String
Set sh = ThisWorkbook.Worksheets("Sheet1")


'Create second query string, join with another query and then pull amount from query and everything else from sheet
connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Workbooks(1).FullName & _
";Extended Properties=""Excel 12.0;HDR=YES;"";"

SQLQuery = "Select * from [TEMP$]  "
rs.Open SQLQuery, connection


With sh

.Cells.ClearContents
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset rs

End With
rs.Close
connection.Close
Set rs = Nothing
Set connection = Nothing


End Sub

I have tried different Provider types, changed fields for both data source/data dump locations, changed the data type for the required field to General and have not seen any success.

The problem is that ADODB expects to have columns with field types. In a regular database, every column has a type (string, numeric, date...), so there you don't have a problem.

In Excel, there is no thing like a type per column, you can put every kind of data into every kind of cell. So the driver tries to guess the field type by analyzing the content of the first n rows (was never able to figure out the value for n). If you have mainly numbers, the column is seen as a numeric column, if you have mainly strings, it is seen as string.

Now in your data, it seems you have mixed values (numbers and strings) in at least one of your columns. If the driver guesses the column is numeric and you issue an SQL command that hits string data, the content will be set to null because it cannot be converted to a number. This is the reason you don't see those values in the result.

Usually, it's a design flaw if you have mixed data types in one column, but of course I cannot judge your special case. If your data needs to be like that, best bet is to format the whole column as "Text" - in that case the driver will see all the content as string and copy all the data. Drawback: The copied data is really a string, no longer a number, even if it looks like a number.

Have a look to the following example:

• The first column ("Numval 1") contains mostly numbers in the first rows
• The second column ("Numval 2") contains mostly strings in the first rows
• The third column ("Numval 3") contains the same data as in column 1, but formatted as text.

Now letting your code run against that, the result table contains:

• In the first column only the numbers. The text stays empty.
• In the second column all the data, but as text. The driver guessed that the column contains text because the first rows where filled with text.
• In third column all the data, but as text. The driver guessed that the column contains text because the data is formatted as text.