Question Table find and replace

[BagManX]

I am not much of a programmer but I have a requirement to export an Access 2013 table query to XML and i need to be able to replace all the invalid XML characters before I can export it. How can I do a table wide find and replace for these.

For instance:
& needs to be replaced with &
< needs to be replaces with <
> needs to be replaced with >
' needs to be replaced with &pos;
" needs to be replaced with "

Thank you ahead of time, any suggestions on the best way to do there, I have to search the entire table and replace all these wherever it sees them and i have not found a way to do it

 

[llkhoutx]

Use the Replace function, nested in an If in a query, or just 1 at a time.

 

[JLCantara]

@BagManX: very easy using VBA, not so using sql...

 

[BagManX]

Sadly I am not much of a VBA coder, I have got it to work in an update query kind of but it is still giving me some trouble :banghead:

 

[JLCantara]

If you get tired of trying, test this one:

 Public Function rplc()
     Dim rst As Recordset
    
    Dim I As Long
    Dim str As String
    
    Set rst = CurrentDb.OpenRecordset("sqlTextes")
    
    With rst
        .MoveLast
        .MoveFirst
        For I = 1 To .RecordCount
            str = .Fields(1)
            str = Replace(str, "&", "&amp")
            str = Replace(str, """", "&quot")
            str = Replace(str, "<", "&lt")
            str = Replace(str, ">", "&gt")
            str = Replace(str, "'", "&pos")
            .MoveNext
            'Debug.Print str
        Next I
    End With
End Function

Note that sqlTextes is the query that returns the text to be converted and the text is in position 1 of the query (starting at 0).

Good luck, JLC.

 

[llkhoutx]

Don't you need a ".Edit" and a ".Update" when moving through records? Otherwise, I think you have it.

 

[Gasman]

Don't you need a ".Edit" and a ".Update" when moving through records? Otherwise, I think you have it.

I was thinking the same thing, but perhaps this allows you to do it on the fly, and not affect the original data?

 

[llkhoutx]

I thought that the purpose was to change the data, otherwise why do it with your code?

 

[Gasman]

I thought that the purpose was to change the data, otherwise why do it with your code?

I can see the benefit of leaving the data as it is in the Access db, in case changing it breaks anything, and modifying it for export to other systems as required.

 

[llkhoutx]

Are you doing something else with each recordset?

 

[JLCantara]

@Ilk machine truc post #6: your are right and I will edit my post now. Of course the goal was to leave the original file intact. And, very obviously, the transfer to XML is omitted.

 

[JLCantara]

Here is what should be the proper version:

 Public Function rplc()
     Dim rst As Recordset
    
    Dim I As Long
    Dim str As String
    
    Set rst = CurrentDb.OpenRecordset("sqlTextes")
    
    With rst
        .MoveLast
        .MoveFirst
        For I = 1 To .RecordCount
            str = .Fields(1)
            str = Replace(str, "&", "&amp")
            str = Replace(str, """", "&quot")
            str = Replace(str, "<", "&lt")
            str = Replace(str, ">", "&gt")
            str = Replace(str, "'", "&pos")
            .Edit
                .Fields(1) = str
                Debug.Print .Fields(1)
            .Update
            .MoveNext
        Next I
        
        ' Insert here the code that creates the XML file
     End With
    
End Function

Again sqlTextes is the query that fetches the proper record group.

P.S. - As is said in post #1, the point is to create an XML file, not to modify the original data.

 

[BagManX]

Awesome thank you all for pointing me in the right direction and helping so fast