ADO field, sql and Apostrophe''''s

[workindan]

I'm using Adodata to pull from a sql server, do a few things to it, then update another table. The issue is when a persons name has an apostrophe in it.

Here's what I'm currently doing:

**************************************
adocmd.commandtext = sqlstring
set adodta = adocmd.execute
...
tmpString = "UPDATE tableName SET Name = '" & Replace(Nz(adodata.fields("Name")),"'"," ") & "', Number = '" & adodata.fields("Number") & "', ......etc
**************************************

When a name like O'BRIEN pops up, the apostrophe still causes problems in the UPDATE sql statement as if it were still there even though it shouldn't be....what gives?

Access 2010
Sql Server 2008

 

[Norie]

Have you checked the SQL statement in SQL Server?

You can do that by using Debug.Print tmpString and copying the result from the Immediate Window (CTRL+G to open).