• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Update"SQL query" in Excel Macro

Can any one check what is wrong with the SQL squery syntax in Excel Macro
I am getting the syntax error.

Please note Ark means sheet.

Urgently required
Sub Ark2_Button1_Click()
 For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" &_
                        "WHERE EventID = <eventid> "
Next

   Ark2.Activate
End Sub

Open in new window

0
Ammar Iqbal
Asked:
Ammar Iqbal
  • 15
  • 9
  • 6
  • +1
1 Solution
 
BusyMamaCommented:
I think you need a space after the last ampersand (before "WHERE EventID).
0
 
Ammar IqbalAuthor Commented:
how ?
0
 
BusyMamaCommented:
Literally, a space after the last & sign in your code.  See attached.
Sub Ark2_Button1_Click()
 For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
Next

   Ark2.Activate
End Sub

Open in new window

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
awking00Commented:
I think BusyMama wants you to change
... & "'" &_
                        "WHERE EventID = <eventid> "
to this
 & "'" & _
                        "WHERE EventID = <eventid> "
with a space after the ampersand.
0
 
Ammar IqbalAuthor Commented:
i think i have done the same. Please  inidicate the exact place .
0
 
Ammar IqbalAuthor Commented:
not figuring out . help please
0
 
BusyMamaCommented:
In Line 7 of the code you provided, you have

&_

instead of

& _

No space in the example you provided, space in the response awking00 and I provided.
0
 
Ammar IqbalAuthor Commented:
i need to exceute this query as early as possible
0
 
Ammar IqbalAuthor Commented:
i have done this, but   when i click debug,  the  debug arrow points to "Where" clause
0
 
BusyMamaCommented:
Please post the code with the change you made.  Mine debugs without error on my machine.
0
 
Ammar IqbalAuthor Commented:
here it is
Sub Button1_Click()
  For i = 1 To 1052

   Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
 Next

   Ark2.Activate

End Sub

Open in new window

0
 
BusyMamaCommented:
My apologies, it debugs for me so I'm at a loss.  My only suggestion would be to save it, close and re-open and see if you continue to have the same error message.

Perhaps awking00 is still following the thread and can jump in.
0
 
Ammar IqbalAuthor Commented:
is the syntax right?
0
 
Ammar IqbalAuthor Commented:
Whenever i click  "Run", it says "object required" and two options debug and end
0
 
Ammar IqbalAuthor Commented:
to be precise run time error 424
0
 
awking00Commented:
What happens if you put a space between the double quotes and the word WHERE?
0
 
BusyMamaCommented:
What is <eventid> and does Excel know where to get it?
0
 
jan24Commented:
If you're getting "Object Required" it implies to me that it thinks you are treating something as an object that isn't an object.

So my hunch would be that this is some problem with Ark1 and/or Ark2.

Do you have anywhere lines that defines what Ark1 and Ark2 are?  
... If yes, please can you post the full code (or attach the workbook) including those lines.  
... If no, then you will need to add lines to define them.  Let us know what you are trying to achieve and we'll help you define them.
0
 
jan24Commented:
Actually, thinking about it perhaps you have worksheets that are called Ark1 and Ark2, right?
In which case instead of Ark2.Cells you should put ThisWorkbook.Worksheets("Ark2").Cells

This can be a bit longwinded, so if you want to refer to these worksheets multiple times then you can try this approach:
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "UPDATE EventAttendees SET FirstName = '" & Replace(Ark1.Cells(i, 1), "'", "''") & "'" & _
                       ", LastName = '" & Replace(Ark1.Cells(i, 2), "'", "''") & "'" & _
                        ", Email= '" & Replace(Ark1.Cells(i, 3), "'", "''") & "'" & _
                        ", CompanyName = '" & Replace(Ark1.Cells(i, 4), "'", "''") & "'" & _
                        "WHERE EventID = <eventid> "
    Next

    Ark2.Activate

End Sub

Open in new window

0
 
Ammar IqbalAuthor Commented:
It goes fine, Can you send me the syntax  of insert query in excel macro. I want to  Insert First name, lastname, email and company name in EventAttendees. using excel macro.

Need to genrerate this query.

Exact Syntax
0
 
BusyMamaCommented:
You have the update query syntax fine:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

But the code is looking for an "object".  So maybe you need to tell it (define) the database, the table, the Ark1 variable, or something else it needs in order to run.

You also need to Dim i as Integer if you haven't already done so.
0
 
Ammar IqbalAuthor Commented:
Update is working fine, can you please temll me the exact syntax of insert query in macro
0
 
jan24Commented:
This link gives you the syntax of INSERT INTO, along with some worked examples.
If you're still stuck let us know!

http://www.w3schools.com/sql/sql_insert.asp
0
 
Ammar IqbalAuthor Commented:
exact syntax  of insert query in Excel macro. There  are amny records in excel which i have to uplaod in database, so i need the exact syntax of insert query in excel macro as early as possible.
Thank you
0
 
BusyMamaCommented:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
0
 
jan24Commented:
For your particular situation, here's a modified VBA code with an INSERT INTO.  Let me know if this works!
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName) VALUES (" & _
                       Replace(Ark1.Cells(i, 1), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 2), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 3), "'", "''") & ", " & _
                       Replace(Ark1.Cells(i, 4), "'", "''") & ") "
    Next

    Ark2.Activate

End Sub 

Open in new window

0
 
Ammar IqbalAuthor Commented:

It works how to nisert  EventID  in  this query.  it is not present in sheet 1  but i will add it later. For now, i need t oadd one column in this query   which is event ID
0
 
jan24Commented:
As an alternative, if you are dealing with text strings in your data then you might want to wrap the values in inverted commas.   If you do then try this:
Sub Button1_Click()
    Dim Ark1 As Worksheet
    Dim Ark2 As Worksheet
    
    Set Ark1 = ThisWorkbook.Worksheets("Ark1")
    Set Ark2 = ThisWorkbook.Worksheets("Ark2")
  
    For i = 1 To 1052



        Ark2.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName) VALUES (" & _
                       "'" & Replace(Ark1.Cells(i, 1), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 2), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 3), "'", "''") & "', " & _
                       "'" & Replace(Ark1.Cells(i, 4), "'", "''") & "') "
    Next

    Ark2.Activate

End Sub

Open in new window

0
 
Ammar IqbalAuthor Commented:
the point is that i will  to exceute these 1052 queries in  the database, or in other words  i will insert1052 records  in  db.  So i need to form a template/macro  query  over here. this exceutes fine, but i want t oadd some more columns which is not present in sheet 1, but in db.
 for example ,Need to add 'EventId' in this insert query
0
 
Ammar IqbalAuthor Commented:
is this the way to do it ? Please correct it
For i = 1 To 1052



        Sheet3.Cells(i, 1) = "INSERT INTO EventAttendees (FirstName, LastName, Email, " & _
                       "CompanyName, EventID) VALUES (" & _
                       Replace(Sheet1.Cells(i, 1), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 2), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 3), "'", "''") & ", " & _
                       Replace(Sheet1.Cells(i, 4), "'", "''") & ") " & _
                       <EventID>
                       
    Next

    Sheet3.Activate

Open in new window

0
 
jan24Commented:
Hi Ammarr77,
Is it just eventid you're talking about, or other fields too?
I'm guessing that eventid is supposed to be a unique id.  If so then probably the best way is not to have it in the INSERT INTO query at all.  Instead edit your database and set the table to automatically generate a new eventid automatically for you, in the background.  That way you can be sure you get a unique id.
You do this by turning on the AUTO_INCREMENT feature for that field.  The link below explains this further:
http://www.w3schools.com/sql/sql_autoincrement.asp
Exactly how this is done will depend on the database system you are using.  If you have a problem with that then I suggest you close this question, and start a new one under the category for that database system, because at this stage it is no longer anything to do with Excel and you need to get in touch with a different group of experts!
0
 
BusyMamaCommented:
Jan24 is right, the type of database might make a big difference.  You can copy/paste easily from Excel into Access, or use many other methods that are quicker and probably easier depending on the tools you have.  Also, if it's a one-time thing, or if it is going to be something that becomes a new business process would make a difference how you should approach the solution.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 15
  • 9
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now