(Solved) – Not all columns appear in SQL query

(solved)-–-not-all-columns-appear-in-sql-query

In this Excel project, I want to copy an entire SQL table into Excel using this code:

Sub search()

Dim conn As New ADODB.Connection
Dim dataset As ADODB.Recordset
Dim sqlQry As String, sConnect As String

sqlQry = "select [Project],[Machine],[Mold],[ Desc],[Model],[Name],[Repair Type],[Entry],[Date],[Time],[Details],[Status],[Division],[A. Stroke],[P. Stroke],[PIC] from [SQLTEST].[dbo].[REPAIRLOG]"

sConnect = "[redacted]"
conn.Open sConnect

Set dataset = New ADODB.Recordset

    dataset.Open sqlQry, conn
    Sheet1.Cells(6, 1).CopyFromRecordset dataset
    dataset.Close

conn.Close
Set dataset = Nothing

End Sub

When executing the sub, only the 1st 10 columns out of 16 columns appears. What happened to the other 6?

Edit:

After posting I tried to remove the first 10 columns in the SQL query and start off from Details onwards. Nothing appears.

But then when I remove Details from the query, the remaining columns behind it can appear. So I can deduce that Details column in my database has issues.

But not sure what’s the cause. Its the only field in SQL whose data type is nvarchar(MAX) while everything else is nvarchar(50) or datetime.

Leave a Reply

Your email address will not be published. Required fields are marked *