Compile error on recordset

I am getting a Compile error Expected: End of statement

at the line that reads
strInsert = "INSERT INTO Temp_Input (LOC) VALUES ('"rs2.Fields(1).Value"');"
with rs2 Highlighted.

Private Sub Command4_Click()
Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim strInsert As String

With cnn2
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=Y:\0_Sales_Planning\DataSource\Vendor Attributes\Vendor Agent Attribute\Aegis\Import\Vendor Attribute File Irving Ridgepoint.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"";"

.Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Ridge Point MA Sales$]"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenStatic
rs2.LockType = adLockReadOnly
rs2.Open cmd2

While Not rs2.EOF
strInsert = "INSERT INTO Temp_Input (LOC) VALUES ('"rs2.Fields(1).Value"');"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailOnError

rs2.MoveNext
Wend
End Sub
KekingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:

strInsert = "INSERT INTO Temp_Input (LOC) VALUES ('" & rs2.Fields(1).Value & "');"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
The Value property, btw is the default so you can get by with just this:


strInsert = "INSERT INTO Temp_Input (LOC) VALUES ('" & rs2.Fields(1) & "');"


Also worth noting if you are not already aware - the recordset field's index is zero-based, so rs2.Fields(1) is the second selected column.  rs2.Fields(0) is the first.
0
KekingAuthor Commented:
Thank you for the quick answers!
0
mbizupCommented:
Glad to help out :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.