Problem with transferring values from an XML file to VBA variables in an Access database.

[PGAC]

Dear all,

I have been advised that my post

http://www.tek-tips.com/viewthread.cfm?qid=1709889

may get a better response if posted here.

Apologies for double-posting.

 

[MakeItSo]

It would have also been OK to post your code here. But that's no biggie.
Have you set breakpoints and single-stepped through your code to make sure the correct node is selected?
Try moving away from selecting nodes by index. if .PreserveWhiteSpace is set to true, you will have an extra node before each childnode containing the whitespaces.
Then childnode(0) will not contain the desired text!

You can avoid all this by using XPath instead:

strName =domUserFile.SelectSingleNode("/CAPSSID/User/Name").Text
lngPermission =domUserFile.SelectSingleNode("/CAPSSID/User/Permission").Text
...
...

Hope this helps!

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Any news on this?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PGAC]

After some trial on error on several PCs, I suspect my version of Office (2010) is corrupting the VBA when I compile the code. Once compiled, any string variables throw run time error 5. I tried importing all objects into a new database on a different PC and no problems.

Thanks for your help.

 

[MakeItSo]

Office 2010 does not corrupt VBA.
You run into problems if you want to use deprecated VBA commands that are no longer supported in Office 2010 (like Application.FileSearch) but other than that you should do fine.
Probably just the references / reference versions that cause problems, very probably the ADO part.
You need different jet engines for Access 2003 and Access 2010.

The runtime error 5, invalid procedure call... suggests that on the non-working computers one of the references cannot be properly resolved. Look into your references there and check for "MISSING".

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PGAC]

That's the problem - there are no references marked as missing. Many time I have created a new database, imported all objects (on some occasions, used the SaveAsText / LoadAsText for VBA modules in case one of them is corrupt) and added all the references again. When I run compile, no errors are flagged. The runtime error 5 only occurs when the code is executed when the database opens. Said code used to work fine on my PC until about mid last week. All other PCs are fine. I can only conclude something is wrong with this one PC.

 

[MakeItSo]

Just a wild guess here:
Is Microsoft XML, v2.6 the highest MSXML version number you got?
Cause that's kinda old.
I reckon that you have some 3rd party software installed on the computers where this works; perhaps a software that uses this MSXML version - and other dependent DLLs.
So although the dll itself may not be missing on the other computers, some dependent component may be missing.
This means the reference itself will not be missing but you will fail when trying to load the XML object.

You might want to try using at least MSXML 4 and MSXML.DOMDocument40 instead.
After all, you state that

300 With domUserFile.DocumentElement.ChildNodes(0) <- problem seems to start here
310 strName = .ChildNodes(enmUserXMLName).Text

Which would indicate that probably domUser.DocumentElement is NULL hence the invalid call when trying to access a property of a nonexisting childnode because the XML already failed to load.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PGAC]

It doesn't seem to be the MSXML version either as I have tried versions 3 and 6 too.

What happens at line 310 is the string variable strName is updated with the value from .ChildNodes(enmUserXMLName).Text.

Any attempts to then use this variable produce run time error 5. For example,

400 If strName <> CAPSSID.fcnUserName Then . . .

produces the error. fcnUserName is an API function that returns the Windows username and works fine and returns a value as string. Also, with the XML files, the nodes that are checked for values do not have Null values.

To overcome the XML issue, I tried converting all string variables into variants. The XML code worked fine but then other functions that use string variables stopped working and produced run time error 5.

For whatever reason, when the code is compiled, the VBA just can't cope with string variables anymore. I've sent the PC to our IT department to be wiped and re-imaged so hopefully it will work again once I get it back.

 

[PGAC]

The PC was reimaged and said problem has not occured since.