Inserting rows with SQL INSERT into an empty worksheet

[Davor Geci]

Hello,
Let's say we have a worksheet called: Sales
In this worksheet, we have 2 columns: Product, Price


We want to insert into this worksheet from VBA using an SQL INSERT 1 record where the Product is "Computers" and the Price is 30.
If we do this when we have some records it is ok, but when we only have the first row that contains the column names (blank worksheet), the Price is getting an apostrophe and it is inserted as an text.
How to solve this?
Here is the sample code:

Private Sub CommandButton1_Click()
  Dim Connection As ADODB.Connection
  Dim ConnectionString As String
  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
    "Extended Properties=Excel 8.0;"
    
  Dim SQL As String
    
    ' worksheet Sales columns: Product, Price
    
  SQL = "INSERT INTO [Sales$] VALUES('Computers', 30)"


  Set Connection = New ADODB.Connection
  Call Connection.Open(ConnectionString)
    
  Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
  Connection.Close
  Set Connection = Nothing
End Sub

 

[Chihiro]

That's likely caused by how Excel's Schema is determined. First x number of rows is used to determine table column's data type.

Try setting Header=Yes and see what happens. If header isn't set, header row is used as sample and will likely result in column data type being interpreted as text.