Forum Discussion

IanaMac's avatar
IanaMac
Brass Contributor
Oct 20, 2021
Solved

Simple VBA Code or should be

I have a simple spreadsheet with 4 columns Column A  has the unique field

I am try to insert new records to a SQL Table it works fine except when i add the WHERE parameter inother words where Column A doesnt exist in the SQL Table it must be a quote mark out of place!! 

Please put me out of my misery as i need to do the same for updates although that is easier! 

 

SQLStr = "INSERT INTO Customer_Master (GPCustID,RMCCustID, ComID, " & _
"LegalName, Partner) Values(" & _
GPCustID & " , " & RMCCustID & "," & ComID & "," & LegalName & "," & Partner & ")" & _
"WHERE(" GPCustID & " <> " & GPCustID &")"

 

THANKS!!!!!! 

Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 20, 2021

    IanaMac 

    Try this:

        SQLStr = "INSERT INTO Customer_Master (GPCustID, RMCCustID, ComID, " & _
        "LegalName, Partner) Values (" & GPCustID & ", " & RMCCustID & _
        ", " & ComID & ", '" & LegalName & "', '" & Partner & "')" & _
        " WHERE " & GPCustID & " Not In (SELECT GPCustID FROM Customer_Master)"

    I added single quotes around LegalName and Partner, assuming that those are text fields.

    • IanaMac's avatar
      IanaMac
      Brass Contributor
      Oct 20, 2021
      thanks yes makes sense!! Ok but I am getting error "Can not Find Stored Procedure False" any idea or what does this mean the complete code is

      Private Sub CommandButton1_Click()


      Dim MoviesConn As ADODB.Connection
      Dim MoviesCmd As ADODB.Command
      Dim r As Range
      Dim SQLStr As String

      Dim GPCustID As String
      Dim RMCCustID As String
      Dim ComID As String
      Dim LegalName As String
      Dim Partner As String

      Set MoviesConn = New ADODB.Connection
      Set MoviesCmd = New ADODB.Command

      MoviesConn.ConnectionString = StrSQL
      MoviesConn.Open

      Application.Cursor = xlWait
      Application.StatusBar = "Logging onto Database..."


      MoviesCmd.ActiveConnection = MoviesConn

      For Each r In Range("A4", Range("A4").End(xlDown))

      MoviesCmd.CommandText = _
      SQLStr = "INSERT INTO Customer_Master (GPCustID, RMCCustID, ComID, " & _
      "LegalName, Partner) Values (" & GPCustID & ", " & RMCCustID & _
      ", " & ComID & ", '" & LegalName & "', '" & Partner & "')" & _
      " WHERE " & GPCustID & " Not In (SELECT GPCustID FROM Customer_Master)"

      MoviesCmd.Execute
      Next r


      MoviesConn.Close

      Set MoviesConn = Nothing
      End Sub

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 20, 2021

        IanaMac 

        Change

         

        MoviesCmd.CommandText = _
        SQLStr = "INSERT INTO Customer_Master (GPCustID, RMCCustID, ComID, " & _
        "LegalName, Partner) Values (" & GPCustID & ", " & RMCCustID & _
        ", " & ComID & ", '" & LegalName & "', '" & Partner & "')" & _
        " WHERE " & GPCustID & " Not In (SELECT GPCustID FROM Customer_Master)"

         

        to

         

        MoviesCmd.CommandText = _
        "INSERT INTO Customer_Master (GPCustID, RMCCustID, ComID, " & _
        "LegalName, Partner) Values (" & GPCustID & ", " & RMCCustID & _
        ", " & ComID & ", '" & LegalName & "', '" & Partner & "')" & _
        " WHERE " & GPCustID & " Not In (SELECT GPCustID FROM Customer_Master)"

Resources