Apostrophe Issues

[TallMan]

Hello,

Bob had suggested that I use the Chr(34) format instead of the quotes but I could not get that to work. maybe I have been looking at this code too long but could someone tell me if they see a mistake with this? I am still getting the "Run-Time Error 3346"


While Not rs.EOF
sqlinsert = "INSERT INTO Pending_Tbl (Account_Number, Client_Name, Branch, Primary_FA_num, New_Subcategory_ID, DT_Code_Changed, Email, Dt_Contract_Received, Pending_Notes) VALUES ('" & rs.Fields("Account_Number") & "','" & Replace(rs.Fields("Client_Name"), "'", "''") & "', '" & rs.Fields("Branch") & "', '" & rs.Fields("Primary_FA_num") & "', '" & rs.Fields("New_Subcategory_ID") & "', '" & rs.Fields("DT_Code_Changed") & "', '" & Replace(rs.Fields("FA_Email_Address"), "'", "''") & rs.Fields("Dt_Contract_Received") & "', '" & rs.Fields("Pending_Notes") & "');"""""
db.Execute (sqlinsert)
rs.MoveNext
Wend


Any help you could give I would really appreciate. Thank you !!

 

[TallMan]

Uncle Gizmo,


The code is very large and extensive, this is the only piece I am having trouble with. Do you need to see the rest of the code to possibly solve the error?

Honestly I am not sure how to post the code.

 

[MSAccessRookie]

Hello,

Bob had suggested that I use the Chr(34) format instead of the quotes but I could not get that to work. maybe I have been looking at this code too long but could someone tell me if they see a mistake with this? I am still getting the "Run-Time Error 3346"


While Not rs.EOF
sqlinsert = "INSERT INTO Pending_Tbl (Account_Number, Client_Name, Branch, Primary_FA_num, New_Subcategory_ID, DT_Code_Changed, Email, Dt_Contract_Received, Pending_Notes) VALUES ('" & rs.Fields("Account_Number") & "','" & Replace(rs.Fields("Client_Name"), "'", "''") & "', '" & rs.Fields("Branch") & "', '" & rs.Fields("Primary_FA_num") & "', '" & rs.Fields("New_Subcategory_ID") & "', '" & rs.Fields("DT_Code_Changed") & "', '" & Replace(rs.Fields("FA_Email_Address"), "'", "''") & rs.Fields("Dt_Contract_Received") & "', '" & rs.Fields("Pending_Notes") & "');"""""
db.Execute (sqlinsert)
rs.MoveNext
Wend


Any help you could give I would really appreciate. Thank you !!

I see at least two issues with your VB Code sample.

1. There are Nine Fields in the INSERT INTO section of the Query, and there are only Eight values to insert. I am sure that there were meant to be Nine, but the way the code is formatted (see below) Access VB will only identify Eight values because the value for "Dt_Contract_Received" is not separate from the value for "FA_Email_Address".
2. I am not sure that you are using " and ' appropriately in all places of the code (I have not gotten into that in the example)

[FONT=Courier New]        While Not rs.EOF[/FONT]
[FONT=Courier New]        sqlinsert = "[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]        INSERT INTO Pending_Tbl [/FONT]
[FONT=Courier New]        ([/FONT]
[FONT=Courier New](1)         Account_Number, [/FONT]
[FONT=Courier New](2)         Client_Name, [/FONT]
[FONT=Courier New](3)         Branch, [/FONT]
[FONT=Courier New](4)         Primary_FA_num, [/FONT]
[FONT=Courier New](5)         New_Subcategory_ID, [/FONT]
[FONT=Courier New](6)         DT_Code_Changed, [/FONT]
[FONT=Courier New](7)         Email, [/FONT]
[FONT=Courier New](8)         Dt_Contract_Received, [/FONT]
[FONT=Courier New](9)         Pending_Notes[/FONT]
[FONT=Courier New]        ) [/FONT]
[FONT=Courier New]        VALUES [/FONT]
[FONT=Courier New]        ([/FONT]
[FONT=Courier New](1)         '" & rs.Fields("Account_Number") & "',[/FONT]
[FONT=Courier New](2)         '" & Replace(rs.Fields("Client_Name"), "'", "''") & "', [/FONT]
[FONT=Courier New](3)         '" & rs.Fields("Branch") & "', [/FONT]
[FONT=Courier New](4)         '" & rs.Fields("Primary_FA_num") & "', [/FONT]
[FONT=Courier New](5)         '" & rs.Fields("New_Subcategory_ID") & "', [/FONT]
[FONT=Courier New](6)         '" & rs.Fields("DT_Code_Changed") & "', [/FONT]
[FONT=Courier New](7) & (8)   '" & Replace(rs.Fields("FA_Email_Address"), "'", "''")[COLOR=red][B] &[/B][/COLOR] rs.Fields("Dt_Contract_Received") & "', [/FONT]
[FONT=Courier New](9)         '" & rs.Fields("Pending_Notes") & "');"""""[/FONT]
[FONT=Courier New]        db.Execute (sqlinsert)[/FONT]
[FONT=Courier New]        rs.MoveNext[/FONT]
[FONT=Courier New]        Wend[/FONT]

 

[Atomic Shrimp]

The whole VALUES part of the query looks messed up to me - it looks as though the quotes are enclosing the expressions, rather than wrapped around them, meaning that (if I'm right), it's going to try to insert '& rs.Fields("Account_Number") &' into your table as a string, rather than trying to insert the value of that recordset item itself.

My advice:

Create the query all over again in the visual query designer - use placeholders for the values to be inserted (i.e. in the 'Update To' row, type easily recognised static values such as 'banana' or 123456 under each column)
Switch to SQL view and copy and paste the SQL into your VBA (keep the existing code for reference - just remark it out)
Replace the placeholders with your recordset stuff, so replace 'banana' with rs.Fields("Account_Number") - or better still, rs.Fields!Account_Number - as the quotes might be breaking your string.

 

[DCrake]

Prevention is always better than the cure. If you validate the offending fields at point of input to make sure no invalid characters are save back to the table then you won't need to perform validation in your sql.