• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting rows with SQL INSERT into an empty worksheet

Davor Geci

New Member
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:
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

Excel Ninja
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.
 
Top