VBA & ADO: INSERT INTO Blank worksheet, Field Types

[dageci]

Hello,
do you have any tips on how do you insert a record into a BLANK worksheet using SQL INSERT INTO and that the columns are recognized properly?
For example, if we have a worksheet with 1 Number field, 1 date field, and 1 string field, and if we use the INSERT INTO when the worksheet in empty it will insert all the fields as a string (it will add the apostrophe in front)


Code example:

Private Sub InsertData()
'add a reference to Microsoft ActiveX Data Objects 2.8 Library
Dim cn As New ADODB.Connection
Dim sSQL As String
Dim sConStr As String


    sSQL = "INSERT INTO [Sheet1$] (ID, MyDate, StringColumn) VALUES (2,#2019/12/14#, 'Test string')"
    
    sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"


    cn.Open sConStr
    
    cn.Execute sSQL


Extit_sub:
    If cn.State = 1 Then cn.Close
    Set cn = Nothing
End Sub