SOLVED

Simple VBA Code or should be

Brass Contributor

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!!!!!! 

5 Replies

@Ian Maclauchlan 

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.

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

@Ian Maclauchlan 

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)"

Thanks sorry to though i now have error message...
Run time 450
Wrong number of Arguements or invalid Property assignment.

Any thoughts?
best response confirmed by Ian Maclauchlan (Brass Contributor)
Solution

@Ian Maclauchlan 

Sorry, I can't help you with that. You might ask in the Access community hub.

1 best response

Accepted Solutions
best response confirmed by Ian Maclauchlan (Brass Contributor)
Solution

@Ian Maclauchlan 

Sorry, I can't help you with that. You might ask in the Access community hub.

View solution in original post