inserting data to access table from excel userform

Sagar0650

Board Regular
Joined
Nov 25, 2019
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
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
VBA 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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

juddaaaa

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
I believe it could be the extra comma in your fields
Change
VBA Code:
Sql = "INSERT INTO mytable([B], [D], [E], [P], [S],)..."
to
VBA Code:
Sql = "INSERT INTO mytable([B], [D], [E], [P], [S])..."
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
Is there any reason why you create a new recordset?

I'm sure it's not needed here
 

Sagar0650

Board Regular
Joined
Nov 25, 2019
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
I believe it could be the extra comma in your fields
i changed the code to :
VBA Code:
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

Board Regular
Joined
Nov 25, 2019
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,688
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top