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

Dear all,


Recently I have re-written some VBA code in a split Access database to make it more efficient and eliminate some redundant functions/subs. Basically, an XML file is opened through VBA and certain data from it is transferred to variables created within the frontend database. The problem now is that it no longer works on *some* PCs:

PC #1: Win XP (32-bit) Access 2010 (32-bit) – doesn't work [my work PC]
PC #2: Win XP (32-bit) Access 2003 – doesn't work [other work PC]
PC #3 Win XP (32-bit) Access 2010 (32-bit) – does work [other work PC]
PC #4: Win 7 (64-bit) Access 2010 (32-bit) – does work [my personal laptop]

The above seems to suggest the version of Windows and Access is not really the problem.

The current setup is that each database user has an XML file that stores info on the user: name, permissions, a verification string and a list of the 10 previous records (using the autonumber primary key field) accessed from each section of the database (so that the user can pull up the last 10 records accessed for convenience).

==================================================
The XML file
==================================================
<?xml version="1.0" encoding="utf-8"?>
<CAPSSID>
<User>
<Name>david.short</Name>
<Permission>1</Permission>
<SecurityCode>1FAA3EE96042030BD116B1AB2ECEC7D20271C3E70CA1C95CE3623CCEC60CF483</SecurityCode>
<RecordLog>
<Collection/>
<Address>371;77;492;417;513;31;557;367;369;558</Address>
<Organisation>374;39;207;406;140;48;107;578;98;195</Organisation>
<Contact>556;557;1697;113;1699;804;77;1700;1701;1199</Contact>
<PhoneNumber>85;75;543;1164;310;259;1165</PhoneNumber>
<EmailAddress>1427;1396;371;1437;1319;2224;767</EmailAddress>
<Resource>56;55;54;52;51;53;121;133</Resource>
<Loan>47;62</Loan>
<Seminar>195;196;198;197;199;200;212;201;218;154</Seminar>
<FamilyMember>3;4;6;5;2;1</FamilyMember>
<Intervention>1086;1087;1088;1091;1093;1094;1085;1089;1090;1092</Intervention>
<Referral>4;1;2726</Referral>
<Appointment>3;8</Appointment>
</RecordLog>
</User>
</CAPSSID>
==================================================

The <Permission> is a bit-flag number that determines which forms a user can view/open (see enmSessionCollection below in which values are converted into big-flag format as 2^[enum value]). The <SecurityCode> is used to verify username (using an API for Win username) and their permissions to ensure no-one can cheat and acquire permissions they are not entitled to.

When the frontend of the database opens, it grabs info from the user.XML file. The references (I use early binding) set for the frontend are:

==================================================
Visual Basic For Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Scripting Runtime
Microsoft XML, v2.6
==================================================

Below is the code contained within various modules. I have not included all code from each module – only the code which is relevant to this particular process (obtaining values from the username.XML file).When I write code I tend to have line numbers and I call subs/functions as LibraryName.FunctionOrSubName e.g. VBA.CLng("12")

When the database opens, a hidden form is opened which runs a number of procedures. The only significant line of code from this form is:

Set CAPSSID.GclsSession = New CAPSSID.clsSession

==================================================
clsSession
==================================================
Option Compare Database
Option Explicit

Private MstrSession As String
Private MclcUserRecordLog() As VBA.Collection
Private MlngUserPermission As Long
Private MstrUserFile As String
Private MdomUserFile As MSXML2.DOMDocument

Public Enum enmSessionCollection
[_enmSessionCollectionAdmin] = 0
[_enmSessionCollectionMin] = 0
enmSessionCollectionAddress = 1
enmSessionCollectionOrganisation = 2
enmSessionCollectionContact = 3
enmSessionCollectionPhoneNumber = 4
enmSessionCollectionEmailAddress = 5
enmSessionCollectionResource = 6
enmSessionCollectionLoan = 7
enmSessionCollectionSeminar = 8
enmSessionCollectionFamilyMember = 9
enmSessionCollectionIntervention = 10
enmSessionCollectionReferral = 11
enmSessionCollectionAppointment = 12
[_enmSessionCollectionMax] = 12
[_enmSessionCollectionReadOnly] = 31
End Enum

Public Property Get strSession() As String

10 On Error GoTo ErrorCode

100 strSession = MstrSession

60000 ExitCode:
60010 Exit Sub

65000 ErrorCode:
65010 Call CAPSSID.subErrorNotifier(VBA.Err.Number, VBA.Err.Description, _
"{04FD3C1E-E58C-4E2F-977D-ADB26A649920}", VBA.Erl)
65020 Resume ExitCode

End Property

Private Sub Class_Initialize()

10 On Error GoTo ErrorCode

100 Dim lng As Long

200 MstrSession = CAPSSID.fcnGenerateGUID
210 MstrUserFile = _
CAPSSID.fcnCAPSSIDFolder(enmCAPSSIDFolderUsers, False) & "\" & _
CAPSSID.fcnUserName & ".xml" ' gets the location of the username.xml file

300 Call subUserRecordLogCreate ' prepares the array collection to receive record numbers from XML
310 Call CAPSSID.subLoadXMLUserFile(MstrUserFile, MdomUserFile, MlngUserPermission)

400 For lng = _
(CAPSSID.[_enmSessionCollectionMin] + 1) To _
CAPSSID.[_enmSessionCollectionMax]
410 Call subUpdateUserRecordLogFromXMLUserFile(lng) ' adds record numbers from XML to array collection
420 Next lng

60000 ExitCode:
60010 Exit Sub

65000 ErrorCode:
65010 Call CAPSSID.subErrorNotifier(VBA.Err.Number, VBA.Err.Description, _
"{14311561-D35F-4DD7-860D-81220271C76C}", VBA.Erl, True)
65020 Resume ExitCode

End Sub
==================================================



==================================================
modXML
==================================================
Option Compare Database
Option Explicit

Public Enum enmUserXML
enmUserXMLName = 0
enmUserXMLPermission = 1
enmUserXMLSecurityCode = 2
enmUserXMLRecordLog = 3
End Enum

Public Sub subXMLLoadFile(ByRef strFile As String, _
ByRef dom As MSXML2.DOMDocument, _
Optional ByRef blnQuitOnFail As Boolean = False)

10 On Error GoTo ErrorCode

100 Dim blnQuit As Boolean

200 If dom Is Nothing Then _
Set dom = New MSXML2.DOMDocument

300 If dom.Load(strFile) = False Then
310 If blnQuitOnFail = True Then _
blnQuit = True
320 Call VBA.Err.Raise(53, , Access.AccessError(53))
330 End If

60000 ExitCode:
60010 If blnQuit = True Then _
Call Access.Quit(acQuitSaveNone)
60020 Exit Sub

65000 ErrorCode:
65010 Call CAPSSID.subErrorNotifier(VBA.Err.Number, VBA.Err.Description, _
"{747EFCCB-AC46-404E-AB31-2DB7714DCE07}", VBA.Erl)
65020 Resume ExitCode

End Sub

Public Sub subLoadXMLUserFile(ByRef strUserFile As String, _
ByRef domUserFile As MSXML2.DOMDocument, ByRef lngUserPermission As Long)

10 On Error GoTo ErrorCode

100 Dim strName As String
110 Dim lngPermission As Long
120 Dim strSecurityCode As String

200 Call CAPSSID.subXMLLoadFile(strUserFile, domUserFile)

300 With domUserFile.DocumentElement.ChildNodes(0) <- problem seems to start here
310 strName = .ChildNodes(enmUserXMLName).Text
320 lngPermission = .ChildNodes(enmUserXMLPermission).Text
330 strSecurityCode = .ChildNodes(enmUserXMLSecurityCode).Text
340 End With

400 If strName <> CAPSSID.fcnUserName Then _
Call VBA.Err.Raise(CAPSSID.enmCAPSSIDErrorUnauthorisedUser, , _
CAPSSID.c_GstrErrorUnauthorisedUser)
410 If CAPSSID.fcnSHA256WithMask(strName & lngPermission) <> strSecurityCode Then _
Call VBA.Err.Raise(CAPSSID.enmCAPSSIDErrorUnauthorisedUser, , _
CAPSSID.c_GstrErrorUnauthorisedUser)

500 lngUserPermission = lngPermission

600 Call subXMLUserFileRecordLogVerification(domUserFile)

60000 ExitCode:
60010 Exit Sub

65000 ErrorCode:
65010 Call CAPSSID.subErrorNotifier(VBA.Err.Number, VBA.Err.Description, _
"{6463AFF1-708C-434F-8BB2-3D261E4A47A7}", VBA.Erl, True)
65020 Resume ExitCode

End Sub
==================================================

subErrorNotifier is a sub that handles all errors that occur within the frontend (and should itself produce an error, the application terminates to prevent an endless loop of calls to subErrorNotifier).

Through trial and error, I have found that the problem starts at section 300-340 of the subLoadXMLUserFile procedure. What seems to happen is at

310 strName = .ChildNodes(enmUserXMLName).Text

there is some sort of "corruption" as any attempts to use strName after the XML value has been copied or perform any functions on .ChildNodes(enmUserXMLName).Text produces run time error 5 (Invalid procedure call or argument). As mentioned though only some PCs produce the run time error 5. For other PCs, the codes runs without a single glitch.

I have tried re-creating the frontend by creating an empty database an importing all objects and recreating the database from scratch and importing the VBA from the SaveAsText/LoadAsText commands in case the VBA is corrupted but it is the same result everytime - some PCs work flawlessly others produce run time error 5.

I have also tried strName = VBA.CStr(ChildNodes(enmUserXMLName).Text) but it makes no difference whatsoever. Using a different version of Microsoft XML (I tried 6.0) has no effect either.

I've already spent over a full day trying to fix this but to no avail. I'm really stuck. Any help will be greatly appreciated. Many thanks for your time and effort.



Regards,

David