ACCESS FEED: [MS_AccessPros] RE: Use of apostrophe in data ?

Senin, 18 November 2013

[MS_AccessPros] RE: Use of apostrophe in data ?

(IN the middle of replying I see you got your thing running however...)

I would advise against getting into the habit of running SQL Statements built from user input. You *probably* won't ever have a problem when doing so with Access Databases and you possibly might never come across a user malicious enough, or playful enough, to manipulate his/her input to generate a SQL-Injection attack but, just incase you do, it's best to not give them an easy starting point.

Although you are using VBA do you have some reason to avoid using DAO or ADODB recordsets to add records?

---In ms_access_professionals@yahoogroups.com, <desertscroller@...> wrote:

Thanks John,

I implemented the change and everything works great. The MVPs are a great resource.

Thanks again

Rod

---In ms_access_professionals@yahoogroups.com, <JohnV@...> wrote:

Rod-

Actually, what I'm doing is replacing a single quote (') with a double single quote ('') within the string. Because you are using a single quote as a delimiter, doing that still keeps the single quote within the string. For example, SQL interprets:

'O''Malley'

as

O'Mally

Use Replace within the VALUES clause everywhere you suspect you might have an embedded single quote.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of desertscroller@...
Sent: Sunday, November 17, 2013 7:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: Use of apostrophe in data ?

Thanks John for the reply. My command is as follows:

dbs.Execute "INSERT INTO tblTaxPayer " _
                  & "( strTaxPayerLicenseDt, strRegionCode, strTPT, strIssueDate, strNAICS, strBusinessClass, " _
                  & "strDBAName, strTaxPayerName, strTaxPayerMailingAddr1, strTaxPayerMailingCity, " _
                  & "strTaxPayerMailingState, strTaxPayerMailingZipCode ) VALUES " _
                  & "( '" & strLicDate & "', '" & strRegion & "', '" & strTPTNo & "', '" _
                  & strIssDate & "', '" & strNCode & "', '" & strBusClass & "', '" _
                  & strDBA & "', '" & strTPayerName & "', '" & strTPMAddr & "', '" _
                  & strTPMCity & "', '" & strTPMState & "', '" & strTPMZipCode & "' );"

If I understand your answer correctly, I would replace each value that may have an apostrophe with the REPLACE command. And the REPLACE command would return the original str when no apostrophe is present.
I did not know that I could embed commands like REPLACE in the SQL statement. Thanks for the suggestion. I will be trying it presently. Only the strDBA and strTPayerName string should possibly contain the apostrophe.
Thanks Again
Rod

---In ms_access_professionals@yahoogroups.com, <JohnV@...> wrote:
Rod-

What does the code look like that you use to write to your Access table? If you're building an SQL INSERT statement, try wrapping a Replace function call around the string. Like this:

strSQL = "INSERT INTO MyTable (CompanyName) " & _
"VALUES('" & Replace(strCompanyName, "'", "''") & "')

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of desertscroller@...
Sent: Sunday, November 17, 2013 5:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Use of apostrophe in data ?

Hi All, I am importing data from an EXCEL spreadsheet (an apostrophe may be imbedded in the string from EXCEL). When I then try writing the data to the appropriate table in ACCESS, I get an error message 3075 Syntax error (missing operator) in query expression.
Is there any easy way to allow the data to be imported as is? I can write a macro in EXCEL to run that would remove all apostrophes if needed but would prefer to maintain the data as is.
Using Office 2010 products.
Example of the data    -     JD'S HANDYMAN SERVICES

Thanks for any suggestions.
Rod