vba access mssql inserting data

Question

I have some problem when inserting data to database. I am using mssql.

Private Sub EditMethodAdd_Click()

Dim rs As ADODB.Recordset
Dim introw
Dim strState As String
Dim strsql1 As String
Dim strsql2 As String
Dim all As String

Dim strConn As String
Dim conn As ADODB.Connection

MsgBox ("EditM1.Value:" & EditM1.value)


strConn = "DRIVER=SQL Server;SERVER=CHU-AS-0004;DATABASE=RTC_LaplaceD_DEV;Trusted_Connection=Yes;"

strsql1 = " INSERT INTO dbo.Method(MethodID, MethodClass, Category, Description, Description2, MSA, ReqType, Equipment, Location, Spec1, Spec2, Spec3, Spec4, Spec5, Spec6, PilotingYN) "
strsql2 = "   VALUES(EditM1.value, 'Piloting', EditM3.value, Null, Null, Null, Null, EditM2.value, EditM4.value, EditM5.value, EditM6.value, EditM7.value, EditM8.value, EditM9.value, EditM10.value, Null )"


all = strsql1 & strsql2

MsgBox ("ALL" & all)


Set conn = New ADODB.Connection
conn.Open strConn

Set rs = New ADODB.Recordset
rs.Open all, conn
MsgBox ("Insert Success")

EditMethodList.Requery

conn.Close
Set rs = Nothing
Set conn = Nothing

MsgBox "Data has been updated"
EditMethodList.Requery

End Sub

When I check the value for EditM1 by using MsgBox, it shows correct. But I got error message like this.

Is there anyone who can solve this problem? Thank you in advance.

paul bica · Accepted Answer · 2017-09-09 11:09:23Z

2

Delete the lines:

strsql1 = ...
strsql2 = ...

all = strsql1 & strsql2

and write this instead

all = "INSERT INTO dbo.Method(MethodID, MethodClass, Category, Description, Description2, MSA, ReqType, Equipment, Location, Spec1, Spec2, Spec3, Spec4, Spec5, Spec6, PilotingYN) "
all = all & "VALUES(" & EditM1.value & ", 'Piloting'," & EditM3.value & ", Null, Null, Null, Null," & EditM2.value & "," & EditM4.value & "," 
all = all & EditM5.value & "," & EditM6.value & "," & EditM7.value & "," & EditM8.value & "," & EditM9.value & "," & EditM10.value & ", Null )"

If you insert EditM1.value into doublequotes, as you did, VBA read it as a string and it does not refer to its value. You need to concatenate string and values with & to create your query.

edited Sep 9, 2017 at 11:09

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

answered Sep 8, 2017 at 8:15

gioxc88

5531 gold badge3 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andre Over a year ago

Don't do this. Use a parameter query, here is an explanation and example: stackoverflow.com/a/46057683/3820271

Tim Williams · Accepted Answer · 2017-09-12 01:06:21Z

2

You're putting the literal value "EditM1.value" into your SQL: you should instead be sending the Value of the control:

strsql2 = "   VALUES(" & EditM1.value & ", 'Piloting', " & _
                         EditM3.value & ", Null,..." 'etc

If any of the values being sent are not numeric then they should be wrapped in single quotes.

edited Sep 12, 2017 at 1:06

answered Sep 8, 2017 at 5:47

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

vba access mssql inserting data

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related