inserting data to access table from excel userform

[Sagar0650]

i have a table created in access database i.e mytable
this table has fields as A, B, C, D ,E , F, G , H , I , J , K, L, M , N, O, P, Q, R, S
this field names are just to practice
now i have written a code on userform button so that when i hit the button data gets added to table

below is the code

Private Sub CommandButton3_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim path As String, btext As String, dtext As String
Dim etext As String, ptext As String, stext As String

btext = TextBox6.Text
dtext = TextBox7.Text
etext = TextBox3.Text
ptext = TextBox4.Text
stext = TextBox5.Text

path = "C:\Users\Documents\demo.accdb"

Set conn = New ADODB.Connection

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path

Set rs = New ADODB.Recordset

Sql = "INSERT INTO mytable([B], [D], [E], [P], [S],)values(" & btext & " , " & dtext & " , " & etext & " , " & ptext & " , " & stext & ")"
conn.Execute Sql

End Sub

 

[Sagar0650]

here i want to insert data into only specific fields that is the reason why i have added only few column names in the sql query above
dont know if this is possible or no?

i am getting an error msg that "syntax error in INSERT INTO statement"

can someone please help me with this?

 

[welshgasman]

Debug.Print Sql and see what that shows, possibly null values?

 

[Micron]

& btext & " , " & dtext & " , " & etext & " , " & ptext & " , " & stext

If they are text, they need to be delimited:
('" & btext & "' , '" & dtext & "' , '" & etext & "' ,' " & ptext & "' , '" & stext & "')"

 

[Sagar0650]

it worked
thank you sir

 

[Micron]

You're welcome. If you did Debug.Print as suggested but for both versions you might see the difference more clearly. Just a thought...

 

[Sagar0650]

Debug.Print Sql and see what that shows, possibly null values?

it shows proper error. Thank you

If you did Debug.Print as suggested but for both versions you might see the difference more clearly. Just a thought...

thank you for great suggestion.