convertng double quotes to single quotes in a column

[Morris]

Hi! As I got totally confused with juggling between single and double
quotes, and after reading many posts which didn't solve this
particular matter here I ask you:

After importing Excel file to Access table I have this value in one of
the columns:

The Homewell Practice, Suite "B"

It's got a double quote so before working with it I want to replace it
to a single one.

I tried:

DoCmd.RunSQL ("UPDATE MyTable SET MyTable.MyColumn =
Replace([MyColumn], """", ""'"");")

but this doesn't work.

I also tried different variations, but all of them failed.. Where
should I put more/less quotes and of which type?

Cheers,
Morris

 

[Douglas J. Steele]

See if my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Stefan Hoffmann]

hi Morris,

The Homewell Practice, Suite "B"

It's got a double quote so before working with it I want to replace it
to a single one.

DoCmd.RunSQL ("UPDATE MyTable SET MyTable.MyColumn =
Replace([MyColumn], """", ""'"");")
but this doesn't work.

There are too few " in it.

I also tried different variations, but all of them failed.. Where
should I put more/less quotes and of which type?

First of all, you must make a decision use

a) .RunSQL ".. SET [Field] = 'value')"
or
b) .RunSQL ".. SET [Field] = ""value"")"


a) is better for counting the quotation marks.

Then it is quite easy. You must escape (double) your delimiter using
Replace(). I use a public function and ' as delimiter:

Public Function SQLEscape(AString As String) _
As String

SQLEscape = Replace(AString, "'", "''")

End Function

use it as:

..RunSQL ".. SET [Field] = SQLEscape([Field]))"


btw, i'm using CurrentDb.Execute instead of DoCmd, e.g.

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "UPDATE Table SET Field=1 WHERE Field=0"

MsgBox "Records updated: " & db.RecordsAffected



mfG
--> stefan <--

 

[Morris]

hi Morris,

Then it is quite easy. You must escape (double) your delimiter using
Replace(). I use a public function and ' as delimiter:

Public Function SQLEscape(AString As String) _
As String

SQLEscape = Replace(AString, "'", "''")

End Function

btw, i'm using CurrentDb.Execute instead of DoCmd, e.g.

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "UPDATE Table SET Field=1 WHERE Field=0"

MsgBox "Records updated: " & db.RecordsAffected

mfG
--> stefan <--

Thanks a lot stefan, I just didn't realise I must escape the
delimiter, thought I can pack it all in one line.
What's currentdb.executre better than docmd? It returns parameters
like .recordsAffected, so probably some other stuff?

 

[Stefan Hoffmann]

hi Morris,

What's currentdb.executre better than docmd? It returns parameters
like .recordsAffected, so probably some other stuff?

In the past the usage of too many DoCmd calls made some of my
applications instable. This may not longer be true for Access 2003.

I'm using CurrentDb.Execute in a function like that (paste it in a
normal module):

---
Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property

Public Function SQLExecute(AStatement As String, _
Optional ASilent As Boolean = False, _
Optional AFailOnError As Boolean = False _
) As Boolean

On Local Error GoTo LocalError

Dim MousePointer As Long

MousePointer = Screen.MousePointer
Screen.MousePointer = ccHourglass
' ccHourglass = 11 (defined in MSComctlLib)

SQLExecute = False

If AFailOnError Then
CurrentDbC.Execute AStatement, dbFailOnError
Else
CurrentDbC.Execute AStatement
End If

SQLExecute = True

Screen.MousePointer = MousePointer

Exit Function

LocalError:
SQLExecute = False
Screen.MousePointer = MousePointer
If Not ASilent Then
MsgBox Err.Description & vbCrLf & _
"SQL: " & AStatement
End If

End Function
---

I don't need to fiddle with DoCmd.SetWarnings or .Echo and i can control
the behaviour in case of an error with or without using dbFailOnError.

CurrentDbC is another trick, gaining performance when using it instead
of CurrentDb.


mfG
--> stefan <--