Removing , (comma) from table (My code included)

[Jasper XAFAX]

Hi all,

I have spend quite some time on figuring out how to remove the , (comma) from a field in a table. I have come up with the following code.

------------------------------------------
Dim rst As ADODB.Recordset
Dim strCnn As ADODB.Connection
Dim antwoord As String
Dim str As String

Set strCnn = New ADODB.Connection
Set rst = New ADODB.Recordset


strCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & "G:\NAWKLANT\Fontys\Fontys.mdb" & ";Persist Security Info=False"
strCnn.Open

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
rst.Open "SELECT * FROM TempCur WHERE Naam Like '*,*' ORDER BY Naam;"
strFind = "'*,*'"
strReplace = "'""'"

strCnn.Close
Set strCnn = Nothing
Set rst = Nothing

----------------------------------------------------------------------

I have to admit I do not fully understand this piece of code. I have build it myself (that is why it isn't working) from pieces of code I already had and combined it with a little help and source from internet.

Could someone point out what I am doing wrong?

Thanks in advance!

Jasper

[Twodogs]

Dim gCmd as ADODB.Command
SET gCmd = NEW ADODB.Command
gCmd.ActiveConnection = strCnn 

While Not rst.Eof
xyz = replace(rst.fields("Naam").Value, ",", "")
abc = rst.Fields("whateverthefieldnameis").value
gcmd.CommandText = "UPDATE tblName SET fieldname = '" & xyz & "' WHERE Fieldname LIKE '%,%' AND IdentityColumn = '" & abc & "'"
gCmd.Execute
rst.Movenext
Loop

[Jasper XAFAX]

Hi Twodogs,

First of all let me thank you for taking the time to read and reply to my question.
I have implemented your code into mine but I get an error. (I have to translate it to english so it could be a bit off)
"I need an OLE DB session object to do the desired process. This is not supported"
I get this error at the following piece of code "gCmd.ActiveConnection = strCnn"
I did forget to mention that I am working in VBA, Is this why I get this error?

This is how I implemented it.
----------------------------------------------
Dim rst As ADODB.Recordset
Dim strCnn As ADODB.Connection
Dim gCmd As ADODB.Command

Set strCnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set gCmd = New ADODB.Command
gCmd.ActiveConnection = strCnn


strCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & "G:\NAWKLANT\Fontys\Fontys.mdb" & ";Persist Security Info=False"
strCnn.Open

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
rst.Open "SELECT * FROM TempCur", strCnn, , , adCmdText

Do While Not rst.EOF
xyz = Replace(rst.Fields("Naam").Value, ",", "")
abc = rst.Fields("Naam").Value
gCmd.CommandText = "UPDATE TempCur SET Naam = '" & xyz & "' WHERE Naam LIKE '%,%' AND IdentityColumn = '" & abc & "'"
gCmd.Execute
rst.MoveNext
Loop

strCnn.Close
Set strCnn = Nothing
Set rst = Nothing

---------------------------------------------------------------------
If I leave out the code that is giving me the error it comes up with the following error message "This proces is not allowed when the object is closed" in the Do While Not part.

Isn't that strange? I thought I opened it with the line earlier?

Regards,

Jasper

[Jasper XAFAX]

Hi People,

I still haven't found a solution to the problem I have. I like to bring this thread to attention once more.

Thanks in advance,

Jasper

[Cimperiali]

Private Sub Command1_Click()
    Dim rst As ADODB.Recordset
    Dim strCnn As ADODB.Connection
    Dim gCmd As ADODB.Command
     
    Set strCnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    'Set gCmd = New ADODB.Command
    'The connection has not been established yet
    'you should move the following line down a bit
    'after the strCnn.Open sentence...
    'gCmd.ActiveConnection = strCnn
     
     
    strCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\NAWKLANT\Fontys\Fontys.mdb"
    strCnn.Open
     
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockBatchOptimistic
    rst.Open "SELECT * FROM TempCur", strCnn  ', , , adCmdText
     
    Do While Not rst.EOF
        rst.Fields("Naam").Value = Replace(Trim(rst.Fields("Naam").Value & ""), ",", "")
        rst.Update
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    strCnn.Close
    Set strCnn = Nothing
    'you should close recordset first
    'Set rst = Nothing

End Sub

[Jasper XAFAX]

Hi Cimperiali,

Thank you for replying to my thread.

The code doesn't seem to do anything. I have tried different approaches but nothing seems to work.
I have tried to insert the 'gCmd.ActiveConnection = strCnn after and under the strCnn.Open line and it will give me te errormessage that I need to specify a "With" variable.
The connection will be opened because when I try to debug it I need to close the connection manually. What am I doing wrong?
My table name is "TempCur" and the field that I am trying to update is "Naam".

I presume that the pieces of code behind the ' Symbol that I put in my code before are not required.

Best regards,

Jasper

[Cimperiali]

G:\NAWKLANT\Fontys\Fontys.mdb
you sure you have writing permissions on this db?

[Jasper XAFAX]

100% sure of it.

[Cimperiali]

Here, try the following, and be aware of two things:
using a staticc record, you will not see updating immediately: you will have to requery.
Using an UpdateBatch, the update will trig, but not as soon as you call it...

Moreover: you cannot expect to open access while running your program and see immediate changings...This may also mean: if you open that table from inside your program in the meanwhile...

[Jasper XAFAX]

Hi guys,

This worked.

-----------------------------------------------------------------------

Dim Dbs As DAO.Database
Dim Rec As DAO.Recordset

Set Dbs = CurrentDb
Set Rec = Dbs.OpenRecordset("TempCur", dbOpenTable)

Do While Not Rec.EOF
    Rec.Edit
    Rec.Fields("Naam").Value = Replace(Trim(Rec.Fields("Naam").Value & ""), ",", "")
    Rec.Update
    Rec.MoveNext
Loop

Rec.Close
Set Rec = Nothing

----------------------------------------------------------------------
I know it's just DAO but I don't get ADO but I couldn't have done it without you.

Best regards,

Jasper