Inserting rows with SQL INSERT into an empty worksheet

[dageci]

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

 

[lrobbo314]

Is there some reason in particular that you are doing this through an ADO connection? There are easier ways of doing this that wouldn't have these issues. I tried pre-formatting rows in column B as numbers, which worked to get it added with the apostrophe, but it inserts the value below where the formatting stops.

The code below should work.

Sub InsertRow()
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row() + 1
Range("A" & LR & ":" & "B" & LR).Value = Array("Computers", 30)
End Sub

 

[dageci]

Thanks Irobbo314 for your reply.
I'm creating a control called Virtual Forms for Excel that can connect to various databases from Excel VBA, including Excel Workbook, to give Excel users what Access users have for decades. Create master-detail & lookup forms without coding.
It works, but when the datasource is Excel Workbook the problem is that it needs to have at least 1 row of data in every Worksheet, because of this limitation.
So, now I'm stuck with this challenge. For now the only workaround is to insist that all worksheets have at least 1 row of data to be able to work with Excel Workbook as the datasource.
So I was hoping that maybe here someone did find a workaround.