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

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?

 

[juddaaaa]

I believe it could be the extra comma in your fields
Change

Sql = "INSERT INTO mytable([B], [D], [E], [P], [S],)..."

to

Sql = "INSERT INTO mytable([B], [D], [E], [P], [S])..."

 

[JumboCactuar]

Is there any reason why you create a new recordset?

I'm sure it's not needed here

 

[Sagar0650]

I believe it could be the extra comma in your fields

i changed the code to :

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

now it is woking fine
thank you very much sir.

 

[Sagar0650]

Is there any reason why you create a new recordset?

i believe for inserting a record, only the connection is required & recordset is used to hold the record from DB while extracting it from DB

i was actually trying to insert into as well as export from access db & that was the reason i used recordset
but forgot to remove it from the code before raising query here. my bad

again thank you so much