VBA to SQL error

Question

I am using a SQL server database to hold data.

The data is entered onto the excel sheet, and a macro then runs to insert the data into a SQL database.

One of the cells on the excel sheet has a value called 'ONE HUNDRED'. When I run the macro, I get an error saying 'Incorrect syntax near ONE HUNDRED'

Could someone please advise why I am getting this error?

Code is below.

Sub server()

Dim sConnString As String


    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

    objMyConn.ConnectionString = "Provider=sqloledb;Data Source=xx;Initial Catalog=xx;Integrated Security=SSPI;": MsgBox "Connection Successful"
    objMyConn.Open

    Set objMyCmd.ActiveConnection = objMyConn

    Dim val1 As String, val2 As String, val3 As String, val4 As String, val5 As String

    val1 = Range("D9").Value
    val2 = Range("D10").Value
    val3 = Range("D11").Value
    val4 = Range("D12").Value
    val5 = Range("D13").Value

    Dim item1 As String
    item1 = "INSERT INTO [xx].[dbo].[server]("
    item1 = item1 & "  [server_name],[ip_address], [phys_location], [phys_or_virt], [it_contact] "

    item1 = item1 & "  )Values("
    item1 = item1 & "  '" & val1 & "', '" & val2 & "', '" & val3 & "', '" & val4 & "', '" & val5 & "'"

    objMyConn.Execute item1


End Sub

and the cell is val5, right?

Amen Jlili
– Amen Jlili

2015-02-19 12:21:28 +00:00
Commented Feb 19, 2015 at 12:21 — Amen Jlili
– Amen Jlili, Commented Feb 19, 2015 at 12:21
You have a missing ")".

Amen Jlili
– Amen Jlili

2015-02-19 12:23:54 +00:00
Commented Feb 19, 2015 at 12:23 — Amen Jlili
– Amen Jlili, Commented Feb 19, 2015 at 12:23

DaniloZ · Accepted Answer · 2015-02-19 12:25:17Z

4

Try closing parenthesis at the end sql string

answered Feb 19, 2015 at 12:25

DaniloZ

494 bronze badges

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

Comments

Amen Jlili · Accepted Answer · 2015-02-19 12:50:08Z

1

Add a parenthesis at the end of item1

item1 = item1 & "  '" & val1 & "', '" & val2 & "', '" & val3 & "', '" & val4 & "', '" & val5 & "')"

answered Feb 19, 2015 at 12:50

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

Collectives™ on Stack Overflow

VBA to SQL error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related