CODEWITHSUNDEEP
sqlvbaexcel
Deniz Acaroğlu
Deniz Acaroğlu

Reputation: 1

ADODB Insert into another sheet

I am trying to create a record on another excel sheet with SQL insert command I am able to select with query but I don't know exactly how to insert a record my code is:

Function database_add(Urun_barkodu, Urun_kodu, Urun_adi, Urun_kategori) As String

  Dim cn As Object, rs As Object, output As String, sql As String, Insert As String
  Dim add_data As String
  Dim rst As ADODB.Recordset
  '---Connecting to the Data Source---
  Set cn = CreateObject("ADODB.Connection")
  Set rst = New ADODB.Recordset
  With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
  End With
  add_data = "INSERT INTO [ürünler$] (columns(1),columns(2),columns(3),columns(4))"
  add_data = add_data & "VALUES (Urun_barkodu, Urun_kodu, Urun_adi, Urun_kategori)"
  cn.Close
  cn.Open
  rst.Open add_data, cn, dOpenStatic, adLockReadOnly, adCmdText
  '---Clean up---
  rs.Close
  cn.Close
  Set cn = Nothing
  Set rs = Nothing
End Function

It gives me an error like:

syntax error in INSERT INTO STATEMENT

Upvotes: 0

Views: 887

Answers (1)

user4039065
user4039065

Reputation:

I'm guessing that Urun_barkodu, Urun_kodu, Urun_adi and Urun_kategori are string vars. You need to concatenate them into the string.

add_data = "INSERT INTO [ürünler$] (columns(1),columns(2),columns(3),columns(4)) "
add_data = add_data & "VALUES ('" & Urun_barkodu & "', '" & Urun_kodu& "', '" & Urun_adi & "', '" & Urun_kategori & "');"

Upvotes: 1

Related Questions