SOLVED

Simple VBA Code or should be

%3CLINGO-SUB%20id%3D%22lingo-sub-2864089%22%20slang%3D%22en-US%22%3ESimple%20VBA%20Code%20or%20should%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2864089%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20simple%20spreadsheet%20with%204%20columns%20Column%20A%26nbsp%3B%20has%20the%20unique%20field%3C%2FP%3E%3CP%3EI%20am%20try%20to%20insert%20new%20records%20to%20a%20SQL%20Table%20it%20works%20fine%20except%20when%20i%20add%20the%20WHERE%20parameter%20inother%20words%20where%20Column%20A%20doesnt%20exist%20in%20the%20SQL%20Table%20it%20must%20be%20a%20quote%20mark%20out%20of%20place!!%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20put%20me%20out%20of%20my%20misery%20as%20i%20need%20to%20do%20the%20same%20for%20updates%20although%20that%20is%20easier!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESQLStr%20%3D%20%22INSERT%20INTO%20Customer_Master%20(GPCustID%2CRMCCustID%2C%20ComID%2C%20%22%20%26amp%3B%20_%3CBR%20%2F%3E%22LegalName%2C%20Partner)%20Values(%22%20%26amp%3B%20_%3CBR%20%2F%3EGPCustID%20%26amp%3B%20%22%20%2C%20%22%20%26amp%3B%20RMCCustID%20%26amp%3B%20%22%2C%22%20%26amp%3B%20ComID%20%26amp%3B%20%22%2C%22%20%26amp%3B%20LegalName%20%26amp%3B%20%22%2C%22%20%26amp%3B%20Partner%20%26amp%3B%20%22)%22%20%26amp%3B%20_%3CBR%20%2F%3E%22WHERE(%22%20GPCustID%20%26amp%3B%20%22%20%26lt%3B%26gt%3B%20%22%20%26amp%3B%20GPCustID%20%26amp%3B%22)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANKS!!!!!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2864089%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2864207%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20VBA%20Code%20or%20should%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2864207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F149334%22%20target%3D%22_blank%22%3E%40Ian%20Maclauchlan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20%20%20%20SQLStr%20%3D%20%22INSERT%20INTO%20Customer_Master%20(GPCustID%2C%20RMCCustID%2C%20ComID%2C%20%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22LegalName%2C%20Partner)%20Values%20(%22%20%26amp%3B%20GPCustID%20%26amp%3B%20%22%2C%20%22%20%26amp%3B%20RMCCustID%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22%2C%20%22%20%26amp%3B%20ComID%20%26amp%3B%20%22%2C%20'%22%20%26amp%3B%20LegalName%20%26amp%3B%20%22'%2C%20'%22%20%26amp%3B%20Partner%20%26amp%3B%20%22')%22%20%26amp%3B%20_%0A%20%20%20%20%20%20%20%20%22%20WHERE%20%22%20%26amp%3B%20GPCustID%20%26amp%3B%20%22%20Not%20In%20(SELECT%20GPCustID%20FROM%20Customer_Master)%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20added%20single%20quotes%20around%20LegalName%20and%20Partner%2C%20assuming%20that%20those%20are%20text%20fields.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2864817%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20VBA%20Code%20or%20should%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2864817%22%20slang%3D%22en-US%22%3Ethanks%20yes%20makes%20sense!!%20Ok%20but%20I%20am%20getting%20error%20%22Can%20not%20Find%20Stored%20Procedure%20False%22%20any%20idea%20or%20what%20does%20this%20mean%20the%20complete%20code%20is%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20MoviesConn%20As%20ADODB.Connection%3CBR%20%2F%3EDim%20MoviesCmd%20As%20ADODB.Command%3CBR%20%2F%3EDim%20r%20As%20Range%3CBR%20%2F%3EDim%20SQLStr%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20GPCustID%20As%20String%3CBR%20%2F%3EDim%20RMCCustID%20As%20String%3CBR%20%2F%3EDim%20ComID%20As%20String%3CBR%20%2F%3EDim%20LegalName%20As%20String%3CBR%20%2F%3EDim%20Partner%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20MoviesConn%20%3D%20New%20ADODB.Connection%3CBR%20%2F%3ESet%20MoviesCmd%20%3D%20New%20ADODB.Command%3CBR%20%2F%3E%3CBR%20%2F%3EMoviesConn.ConnectionString%20%3D%20StrSQL%3CBR%20%2F%3EMoviesConn.Open%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Cursor%20%3D%20xlWait%3CBR%20%2F%3EApplication.StatusBar%20%3D%20%22Logging%20onto%20Database...%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMoviesCmd.ActiveConnection%20%3D%20MoviesConn%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20r%20In%20Range(%22A4%22%2C%20Range(%22A4%22).End(xlDown))%3CBR%20%2F%3E%3CBR%20%2F%3EMoviesCmd.CommandText%20%3D%20_%3CBR%20%2F%3ESQLStr%20%3D%20%22INSERT%20INTO%20Customer_Master%20(GPCustID%2C%20RMCCustID%2C%20ComID%2C%20%22%20%26amp%3B%20_%3CBR%20%2F%3E%22LegalName%2C%20Partner)%20Values%20(%22%20%26amp%3B%20GPCustID%20%26amp%3B%20%22%2C%20%22%20%26amp%3B%20RMCCustID%20%26amp%3B%20_%3CBR%20%2F%3E%22%2C%20%22%20%26amp%3B%20ComID%20%26amp%3B%20%22%2C%20'%22%20%26amp%3B%20LegalName%20%26amp%3B%20%22'%2C%20'%22%20%26amp%3B%20Partner%20%26amp%3B%20%22')%22%20%26amp%3B%20_%3CBR%20%2F%3E%22%20WHERE%20%22%20%26amp%3B%20GPCustID%20%26amp%3B%20%22%20Not%20In%20(SELECT%20GPCustID%20FROM%20Customer_Master)%22%3CBR%20%2F%3E%3CBR%20%2F%3EMoviesCmd.Execute%3CBR%20%2F%3ENext%20r%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMoviesConn.Close%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20MoviesConn%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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 (Contributor)
Solution

@Ian Maclauchlan 

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