Inconsistent ImportXML Runtime Automation Error

[TonyRandom]

I am experiencing an intermittent problem with importing an XML file into access using VBA. The import works fine, but when re-run again at a later time it errors out with a Run-time Automation Error as per the attached image.
Here is the code to the point that it errors out.

Function ImportRequest()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As Field
Dim fd As FileDialog
Dim objfl As Variant
Dim myImportFile, myPathName, strSQL1, strSQL2, strSQL3, strSQL4 As String
 
 
' Set MyDB to the current database.
    Set db = CurrentDb
    myPathName = db.Name
 
'Open file dialog to select file to import
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "XML Files", "*.xml", 1
        .title = "Choose Costing Request to import"
        .InitialFileName = "S:\Phoenix Files\"
        .InitialView = msoFileDialogViewDetails
        .Show
        For Each objfl In .SelectedItems
            myImportFile = objfl
        Next objfl
    End With
    Set fd = Nothing
 
'Import File to DB
    Const acAppendData = 2
    If Len(myImportFile) <> 0 Then
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase myPathName
    objAccess.ImportXML myImportFile, acAppendData

I have released all variables at the end of the function, just in case it might have been the cause, but this made no difference. The only solution so far is to close and re-open the application between imports.

** Edit **

[NeoPa]

Are you aware that your code defines strSQL1/2/3 as Variant variables? I'm guessing not. Each variable must individually be defined as String if you want them all to be strings.

Are you aware also that lines #24 through #26 will ensure that only the last of the selected files is ever processed? That seems a strange requirement. Maybe it's not what you were expecting that code to do.

You are using Application Automation to open the same database again to try to handle your import. Is there any reason why you don't simply run it within the already open Access application?

You include loads of code which is only minimally relevant to the process, yet only the first couple of lines where it starts to become properly relevant. How about including the code where the work is actually done to clear down all that you set up to enable this to run. Simply telling us that you've released all variables at the end of the function gives us no way to find any errors in that code (and I would guess from what you've told us that the problem surely lies there).

[TonyRandom]

Hi NeoPa
I have now declared all string variables individually as suggested. The file selection behaviour is as expected, and I have AllowMultiSelect set to false.
I am not sure how to run the application automation from the already open access session, as I have tried to set the objAccess variable as currentDB and commented out the OpenCurrentDatabase line. I am still having the issue that the automation works, then chooses not to.
Please see my entire code for this funtion below.

Option Compare Database
Option Explicit
 
Function ImportRequest()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As Field
Dim fd As FileDialog
Dim objAccess As Application
Dim objfl As Variant
Dim myImportFile As String
Dim myPathName As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
 
 
' Set MyDB to the current database.
    Set db = CurrentDb
    myPathName = db.Name
 
'Open file dialog to select file to import
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "XML Files", "*.xml", 1
        .title = "Choose Costing Request to import"
        .InitialFileName = "S:\Phoenix Files\"
        .InitialView = msoFileDialogViewDetails
        .Show
        For Each objfl In .SelectedItems
            myImportFile = objfl
        Next objfl
    End With
    Set fd = Nothing
 
'Import File to DB
    Const acAppendData = 2
    If Len(myImportFile) <> 0 Then
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase myPathName
    objAccess.ImportXML myImportFile, acAppendData
 
'RunSQL Query to append data to the Costing Table
    strSQL1 = "INSERT INTO tblCostCalc ( strItemCode, strDescription, lngQtyReq, strFoilItem, " & _
    "dblShimRepeat, lngStripWidth ) SELECT DISTINCT row3.ItemCode, row3.ItemDescription, " & _
    "row3.Quantity, row1.U_c_typ_folie, row1.U_c_del_opak_kroku, row1.U_c_rozmer_s_v FROM row3, row1;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL1
    DoCmd.SetWarnings True
 
'Remove records from imported xml tables
    strSQL2 = "DELETE row1.* FROM row1;"
    strSQL3 = "DELETE row3.* FROM row3;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.SetWarnings True
 
'End If
End If
 
 
 
'Release variables
Set db = Nothing
Set rst = Nothing
Set fld = Nothing
Set fd = Nothing
Set objfl = Nothing
 
 
 
End Function
 

I appreciate your comments and suggestions.

Tony

[TonyRandom]

Hi NeoPa,
The solution seems to lie with the CreateObject line. I managed to read your application Automation insight, and gleaned what was necessary from there. So far it appears to be working well.

Thanks
Tony

[NeoPa]

When referring to the current application in any MS Office app you simply use the predefined object Application. IE. In this case that would be :

Call Application.ImportXML)myImportFile, acAppendData)

Clearly, it is possible for you to get the result using Application Automation, yet I believe this would be a mistaken approach. It would be somewhat akin to giving directions of three left turns for a destination that's just up ahead on the right. It gets you there, but by a circuitous route.

TonyRandom:
The file selection behaviour is as expected, and I have AllowMultiSelect set to false.

In that case the code could be far simpler at :

myImportFile = .SelectedItems(0)