[RESOLVED] Update Syntax in Access not the same as in vb 6 code why?

[salsa31]

hey
i have a code that update a certain field if it contains this mark [']
when i run it in the access table it works fine
when i run it in the vb 6 code i get a error why?
this is the code
in access

UPDATE Customers SET FullName = Replace(FullName,"'","")

in vb 6

CN.Execute "UPDATE Customers SET FullName = Replace(FullName," ' ","")"

tnx for any help
salsa

[Thierry69]

> CN.Execute "UPDATE Customers SET FullName = Replace(FullName," ' ","")"

You must double the double quote

CN.Execute "UPDATE Customers SET FullName = Replace(FullName,""'"","""")"

[salsa31]

[dilettante]

Jet SQL Expression Service VBA does not include a Replace$() function.

MS Access uses a private API embedded in the Jet 4.0 and ACE engines to inject its own VBA engine as a substitute expression processor. This is also how MS Access allows "user defined functions" written in the Access VBA macro language to be called within Jet/ACE SQL expressions.

Some of this is historical: Originally MS Access used another language ("Access Basic") and needed a way to inject it. Jet SQL VBA goes back to the VB5 era which has no Replace$() function.

When VB6 expanded the range of intrinsic functions in its VBA namespace Microsoft didn't expend the Jet SQL Expression Service's VBA namespace. This hasn't happened in the later (renamed) ACE engines either.


It can get confusing because "VBA" means different things in different contexts. In VB6 it is just the namespace of a default global object, in Jet SQL it refers to an expression syntax wrapped around its own global object, and in MS Access it is a macro language wrapped around a global object and a procedural processor.

[dilettante]

The article How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003 contains a list of the functions accepted by the Jet SQL Expression parser.

[salsa31]

so you are saying sir it is not possible to run this code from vb ?

[gibra]

so you are saying sir it is not possible to run this code from vb ?

But, why you want to replace in your table all single quote with double quote ?
Isn't a good idea (by me is totally wrong).

[salsa31]

But, why you want to replace in your table all single quote with double quote ?
Isn't a good idea (by me is totally wrong).

i dont want to replace it with double quote
i just want to check if there is a quote then remove it thats all

this is how i run it in access

UPDATE Customers SET FullName = Replace(FullName,"'","")

i want to do the same thing with vb 6

[Gruff]

Sorry. I misunderstood the question. Deleting my answer.

Okay. The best way I can think of is two step.
1) Use your existing method inside access to clean that field of ")" in the FullName field.
2) Create a filter in the tool that adds ")" to the text that removes it before the text is added to the database.

[salsa31]

Sorry. I misunderstood the question. Deleting my answer.

Okay. The best way I can think of is two step.
1) Use your existing method inside access to clean that field of ")" in the FullName field.
2) Create a filter in the tool that adds ")" to the text that removes it before the text is added to the database.

can you give me an example please?

[Gruff]

You already know how to do #1
Regarding #2: Since I do not know how you input your text into the field in the first place I cannot say

[techgnome]

Og for the love of ENIAC....
CN.Execute "UPDATE Customers SET FullName = Replace(FullName,"","", """")"

first a case of not enough double quoting... then a case of too much in the wrong spot ....
Replace takes three paramerters, the haysatck, the needle and what to replace it with... this will look in FullName find , in the text and replace it with an empty string...
We're not building rockets here folks...

-tg

[Gruff]

So your saying Dil is wrong in post #4 TG?

[salsa31]

Og for the love of ENIAC....
CN.Execute "UPDATE Customers SET FullName = Replace(FullName,"","", """")"

first a case of not enough double quoting... then a case of too much in the wrong spot ....
Replace takes three paramerters, the haysatck, the needle and what to replace it with... this will look in FullName find , in the text and replace it with an empty string...
We're not building rockets here folks...

-tg

well sir that didnt help

CN.Execute "UPDATE Customers SET FullName = Replace(FullName,"","", """")"

i still get the same error

[gibra]

i dont want to replace it with double quote

I'm sorry, I misread.

[salsa31]

I'm sorry, I misread.

thats ok
i guess there isnt a way to make this update through vb 6 only through access it self

[gibra]

thats ok
i guess there isnt a way to make this update through vb 6 only through access it self

However, Replace doesn't work outside of MS Access environment.
Even disabling the SandBoxMode registry key, at least to me it never worked.
So you'd better open a recordset to change values.

[salsa31]

look at this
http://www.tek-tips.com/faqs.cfm?fid=418

[gibra]

look at this
http://www.tek-tips.com/faqs.cfm?fid=418

But it has nothing to do with your problem, that is instead related to SQL in Access database.

[salsa31]

But it has nothing to do with your problem, that is instead related to SQL in Access database.

LOL

[Schmidt]

Left(...), Mid(...) and Instr(...) are supported though, so an (although horrible looking)
workaround would be possible directly in "plain JET-SQL"...

Olaf

[IkkeEnGij]

the following seems to work(at least on my system):

Private Sub Command1_Click()
   Dim Conn As New ADODB.Connection
   Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\IkkeEnGij\Documents\TheDatabase.accdb"
   Conn.Open
   Conn.Execute "UPDATE Table1 SET Table1.TheName = Replace(Table1.TheName,chr(39),"""")"
   Conn.Close
End Sub

[salsa31]

Left(...), Mid(...) and Instr(...) are supported though, so an (although horrible looking)
workaround would be possible directly in "plain JET-SQL"...

Olaf

can you give me a example please what do you mean?

[IkkeEnGij]

does post#22 not work ?

[salsa31]

does post#22 not work ?

no my friend still same error

Set CN = New ADODB.Connection
StrSql = "Provider=Microsoft.Jet.OLEDB.4.0;"
StrSql = StrSql & "Data Source=" & App.Path & "\HairDesign.mdb;"
StrSql = StrSql & "Jet OLEDB:Database Password=035924794"
CN.ConnectionString = StrSql
CN.Open
CN.Execute "UPDATE Customers SET Customers.FullName = Replace(Customers.FullName,chr(39),"""")"
CN.Close

[IkkeEnGij]

on my system it works perfectly with ADO and DAO and VB6 sp6
aceoledb/acedao

Try with DAO
if it does not work, post an example database (unprotected)

[gibra]

the following seems to work(at least on my system):

Private Sub Command1_Click()
   Dim Conn As New ADODB.Connection
   Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\IkkeEnGij\Documents\TheDatabase.accdb"
   Conn.Open
   Conn.Execute "UPDATE Table1 SET Table1.TheName = Replace(Table1.TheName,chr(39),"""")"
   Conn.Close
End Sub

This isn't JET provider, this is ACE provider.
Using JET doesn't work.

[salsa31]

This isn't JET provider, this is ACE provider.
Using JET doesn't work.

then how should i do it?

[Schmidt]

then how should i do it?

As said, the SQL isn't really beautiful, but (for single Replacements) it should work as shown below:

Option Explicit 'Into a VB-Form in an empty Project

Const JetPrefix = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

Private Cnn As Object

Private Sub Form_Load()
  Dim DBName$: DBName = Environ("temp") & "\test1.mdb"
  If CreateObject("Scripting.FileSystemObject").FileExists(DBName) Then Kill DBName
 
  Set Cnn = CreateObject("ADOX.Catalog").Create(JetPrefix & DBName)
      Cnn.Execute "Create Table T1(ID Identity Primary Key, Txt Text(255))"
      Cnn.Execute "Insert Into T1(Txt) Values('O''Brien')"
  
  'show the original Value which sits in the Field
  Debug.Print GetRs("Select Txt From T1")(0)
  
  'do a (single) Replacement on all fields which contain an apostrophe
  Cnn.Execute "Update T1 Set Txt = Left(Txt,Instr(Txt,'''')-1) & Mid(Txt,Instr(Txt,'''')+1) Where Instr(Txt,'''')>0"
  
  'let's see if that worked...
  Debug.Print GetRs("Select Txt From T1")(0)
End Sub
 
Function GetRs(SQL As String) As Object
  Set GetRs = CreateObject("ADODB.Recordset")
      GetRs.CursorLocation = 3  'adUseClient
      GetRs.Open SQL, Cnn, 3, 3 'adOpenStatic, adLockOptimistic
End Function

Olaf

[gibra]

then how should i do it?

As already worte, use a ADODB.Recordset , loop records, and replace single quote. Ex:

rs.Open "SELECT Fullname FROM Customers .....", ...........
Do While Not rs.Eof
    rs!Fullname = Replace$(rs!Fullname, "'", vbNulString)
    rs.MoveNext
Loop

[IkkeEnGij]

This isn't JET provider, this is ACE provider.
Using JET doesn't work.

afaik ACE should work with .mdb to
there are a lot of possibilitys to try out
among others using a recordset (as you suggested)
if it does not work with ADO try with DAO
if it does not work with JET, try ACE
so i see at least 8 different methods to try

sql with ADO/DAO JET/ACE
recordset with ADO/DAO JET/ACE

but of cource if one asks a question about databases
including an example database would be the smart thing to do

[IkkeEnGij]

on my system it works perfectly with ADO and DAO and VB6 sp6
aceoledb/acedao

And i did try it out
but is was with an .accdb
just saying so there is no misunderstanding

[techgnome]

Perhaps the question that should be asked is "WHY?" why are you manipulating the data like this? What's the purpose of dropping the ' from the data? Other than to piss off a bunch of Irish?

-tg

[gibra]

Perhaps the question that should be asked is "WHY?" why are you manipulating the data like this? What's the purpose of dropping the ' from the data? Other than to piss off a bunch of Irish?
-tg

I agree 100%.
Why?

[salsa31]

As already worte, use a ADODB.Recordset , loop records, and replace single quote. Ex:

rs.Open "SELECT Fullname FROM Customers .....", ...........
Do While Not rs.Eof
    rs!Fullname = Replace$(rs!Fullname, "'", vbNulString)
    rs.MoveNext
Loop

        Dim Rs As New ADODB.Recordset
        Rs.Open "SELECT Fullname FROM Customers", CN
        Do While Not Rs.EOF
            Rs!FullName = Replace$(Rs!FullName, "'", vbNullString)
        Rs.MoveNext
        Loop
        Rs.Close

[salsa31]

Tg ANd Gibra
this is a old database AMIGOS

[IkkeEnGij]

i just tested with the .mdb Olaf put here
works perfectly with ACEDAO

Private Sub Command1_Click()
    Dim db As DAO.Database
    Set db = OpenDatabase("C:\0test\test1.mdb")
    db.Execute "UPDATE T1 SET T1.Txt = Replace(T1.Txt,""'"","""")"
    db.Close
    Set db = Nothing
End Sub

works perfectly with ACEOLEDB

Private Sub Command1_Click()
   Dim Conn As New ADODB.Connection
   Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\0test\test1.mdb"
   Conn.Open
   Conn.Execute "UPDATE T1 SET T1.Txt = Replace(T1.Txt,""'"","""")"
   Conn.Close
End Sub