I am inserting data from my excel sheet to my sql server database table using VBA code.
My table’s all columns created as VARCHAR datatype. this is why i have added a alter table command in VBA code once insert command is over. Here I am getting an error when it tries to alter my table’s transdate column from VARChar to date datatype.

Please guide what I can do. If I remove TransDate column line from alter command then code is working fine.

Sub UpdateMFData()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim sSQL As String
    Dim ws As Worksheet
    Dim i As Integer
    Dim lastRow As Long
    Dim colCount As Integer

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("MF_Data")

    ' Initialize the connection string
    sConnString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=vngfsdata;Integrated Security=SSPI;"

    ' Create a new ADODB Connection
    Set conn = New ADODB.Connection

    ' Open the connection
    On Error GoTo ErrHandler
    conn.Open sConnString

    ' Check if table MF_Data exists
    sSQL = "IF OBJECT_ID('dbo.MF_Data', 'U') IS NOT NULL DROP TABLE dbo.MF_Data;"
    conn.Execute sSQL

    ' Create the table MF_Data
    colCount = ws.UsedRange.Columns.Count
    sSQL = "CREATE TABLE dbo.MF_Data ("
    For i = 1 To colCount
        sSQL = sSQL & "[" & ws.Cells(1, i).Value & "] NVARCHAR(MAX), "
    Next i
    sSQL = Left(sSQL, Len(sSQL) - 2) & ");" ' Remove last comma and space, and close statement
    conn.Execute sSQL

    ' Insert data from Excel to SQL Server
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        sSQL = "INSERT INTO dbo.MF_Data VALUES ("
        For j = 1 To colCount
            ' Convert dates to SQL-compatible format (using CAST or CONVERT)
            If IsDate(ws.Cells(i, j).Value) Then
                If j = 4 Then ' Column Trans_Date (assuming it's the 4th column)
                    sSQL = sSQL & "CAST('" & Format(ws.Cells(i, j).Value, "yyyy-MM-dd") & "' AS DATE), "
                Else
                    sSQL = sSQL & "'" & Replace(ws.Cells(i, j).Value, "'", "''") & "', "
                End If
            Else
                sSQL = sSQL & "'" & Replace(ws.Cells(i, j).Value, "'", "''") & "', "
            End If
        Next j
        sSQL = Left(sSQL, Len(sSQL) - 2) & ");" ' Remove last comma and space, and close statement
        conn.Execute sSQL
    Next i

    ' Alter table to change column datatypes
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Unique_Code VARCHAR(11) NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Folio_No FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Pan_No NVARCHAR(10) NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Trans_Date DATE NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN NAV FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Units FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Amount FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Mobile_No NUMERIC(10) NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Purchase_SIP FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Div_Payout FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Div_Reinvest FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Redemption FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN STP_In FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN STP_Out FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Switch_Out FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Switch_In FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN SWP_Out FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Net_Units FLOAT NOT NULL; "
    conn.Execute sSQL
    sSQL = "ALTER TABLE dbo.MF_Data " & _
           "ALTER COLUMN Net_Invest FLOAT NOT NULL; "
    conn.Execute sSQL

    ' Create indexes
    sSQL = "CREATE INDEX idx_Unique_Code ON dbo.MF_Data (Unique_Code); "
    conn.Execute sSQL
    sSQL = "CREATE INDEX idx_Folio_No ON dbo.MF_Data (Folio_No); "
    conn.Execute sSQL
    sSQL = "CREATE INDEX idx_Trans_Date ON dbo.MF_Data (Trans_Date); "
    conn.Execute sSQL

    MsgBox "Data has been updated successfully and table structure modified.", vbInformation

ExitHandler:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not conn Is Nothing Then
        conn.Close
        Set conn = Nothing
    End If
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    Resume ExitHandler
End Sub