Solved Encoding error in XML file generated in acess VBA (1 Viewer)

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
Hi all,

I have an issue regarding encoding in a XML file. I'm using czech letters like "Č, á, í"... inside a body tag of an XML and the error seems to occur whenever there are letters with acute or circumflex. I'm actually printing some text content into an xml file.

Snippet of the code

Code:
Dim xmlContent As String
Dim save As String

xmlContent = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & _
"<Main>" & vbCrLf & _
"<ID>" & Me.ID & "</ID>" & vbCrLf & _
"<Date>" & Now() & "</Date>" & vbCrLf & _
"<Name>" & "ěščřžýáíé" & "</Name>" & vbCrLf & _
"</Main>"

save = "C:...\test.xml"

Open save For Output As #1
Print #1, xmlContent
Close #1

When i open this file in some browser, it tells me there is some encoding error at line 5 and show only the ID and date, like a normal string, the name is not included. When I open the file in my notepad, everything seems fine.

Do I need to encode the whole content of the file or the file itself? What to do so it can read the 5th line also and show as a tag in browser?

Thank you
 

bastanu

AWF VIP
Local time
Today, 08:58
Joined
Apr 13, 2010
Messages
1,401
What happens if you change the encoding?
xmlContent = "<?xml version=""1.0"" encoding=""windows-1250""?>" & vbCrLf & _
 
  • Like
Reactions: Tea

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
What happens if you change the encoding?
xmlContent = "<?xml version=""1.0"" encoding=""windows-1250""?>" & vbCrLf & _
That is working.
But I have questions: why does UTF-8 not work?
Also the format of the generated file is in ANSI Western European 1252, is it possible to convert the file to utf-8 instead? Instead of changing the encoding in the file content? Is it possible to change the encoding in Acess settings or convert the whole file?
 

sonic8

AWF VIP
Local time
Today, 16:58
Joined
Oct 27, 2015
Messages
998
But I have questions: why does UTF-8 not work?
Also the format of the generated file is in ANSI Western European 1252, is it possible to convert the file to utf-8 instead? Instead of changing the encoding in the file content? Is it possible to change the encoding in Acess settings or convert the whole file?
You answered the first question yourself. If you put a "This is a pear" label on an apple, it still is an apple. That's what you did by writing the string "UTF-8" into your windows-1252 encoded file.

AFAIK, you cannot use UTF-8 encoding with the Open/Print statements.
The easiest alternative is probably the ADODB.Stream object, which supports many different encodings, including UTF-8.
 

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
You answered the first question yourself. If you put a "This is a pear" label on an apple, it still is an apple. That's what you did by writing the string "UTF-8" into your windows-1252 encoded file.

AFAIK, you cannot use UTF-8 encoding with the Open/Print statements.
The easiest alternative is probably the ADODB.Stream object, which supports many different encodings, including UTF-8.
Have never used ADODB.Stream. How would it look like?
 

GPGeorge

Grover Park George
Local time
Today, 08:58
Joined
Nov 25, 2004
Messages
1,776
  • Like
Reactions: Tea

sonic8

AWF VIP
Local time
Today, 16:58
Joined
Oct 27, 2015
Messages
998
Is the ADO library on every users computer? What if I want to share my database with someone, that can't change or add library references? Will it still work?
I think, you can safely assume that the ADO library is available on every computer where there either is Access or its runtime installed.
If you use Late Binding (CreateObject()), it will work independently of the references.
 
  • Like
Reactions: Tea

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
This would be the early binding if I understand correctly, because I'm declaring a specific object variable.

Code:
 Dim streamObject As ADODB.Stream

Set streamObject = New ADODB.Stream

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

Would the Late Binding look like this?

Code:
 Dim streamObject As Object

Set streamObject = CreateObject("ADODB.Stream")

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

I think, you can safely assume that the ADO library is available on every computer where there either is Access or its runtime installed.
If you use Late Binding (CreateObject()), it will work independently of the references.
I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
Thank you so much
 

GPGeorge

Grover Park George
Local time
Today, 08:58
Joined
Nov 25, 2004
Messages
1,776
This would be the early binding if I understand correctly, because I'm declaring a specific object variable.

Code:
 Dim streamObject As ADODB.Stream

Set streamObject = New ADODB.Stream

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing

Would the Late Binding look like this?

Code:
 Dim streamObject As Object

Set streamObject = CreateObject("ADODB.Stream")

With streamObject
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText Data:=strContent
    .Position = 3
End With

streamObject.SaveToFile save, adSaveCreateOverWrite
streamObject.Flush
streamObject.Close
Set streamObject = Nothing


I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
Thank you so much
Look in the References in the VBE:

1643840265528.png
 

sonic8

AWF VIP
Local time
Today, 16:58
Joined
Oct 27, 2015
Messages
998
Your code regarding Early/Late Binding looks fine to me.

I've looked and couldn't find any information regarding the availability of the ADO library reference on computers. Would you know where to look up this information for more details?
I don't know any source confirming this with certainty but I believe ADO ins included with Windows since Windows Vista at the latest.
If it isn't, I think it must be installed with Office.
 
  • Like
Reactions: Tea

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
Look in the References in the VBE:

View attachment 97998
Does it matter which version of library am I referencing to? What's the difference?
Your code regarding Early/Late Binding looks fine to me.


I don't know any source confirming this with certainty but I believe ADO ins included with Windows since Windows Vista at the latest.
If it isn't, I think it must be installed with Office.
Thank you for the infomation.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:58
Joined
Oct 29, 2018
Messages
21,360
Does it matter which version of library am I referencing to? What's the difference?
That's the point of using late binding - it won't matter which version is installed on the users' machine.
 

sonic8

AWF VIP
Local time
Today, 16:58
Joined
Oct 27, 2015
Messages
998
it won't matter which version is installed on the users' machine.
I think that was clarified already. The current question is, how to be sure that any suitable version of the library is installed on the users computer.
 
  • Like
Reactions: Tea

Tea

Member
Local time
Today, 16:58
Joined
Oct 30, 2020
Messages
51
I think that was clarified already. The current question is, how to be sure that any suitable version of the library is installed on the users computer.
That's right. I can't seem to find any information regarding this, if it's a part of a MS package or office in that matter.
 

GPGeorge

Grover Park George
Local time
Today, 08:58
Joined
Nov 25, 2004
Messages
1,776
Thank you, George! I was looking for this info from an official Microsoft source for quite some time.
Sometimes I wear my Sherlock Holmes hat to inspire me on internet searches. Occasionally it even helps. ;)
 

Users who are viewing this thread

Top Bottom