Error 31602 when using IMEX Data Tasks with isladogs frmManagingTasks Form

[ebs17]

I dealt directly with the Excel table as it appears.

Sub test_XLFile()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim i As Long
    Dim sFullPath As String
    
    sFullPath = "D:\!!Samples\export1\export1.xlsx"
    Set db = CurrentDb
    sSQL = "SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & sFullPath & "].[Sheet1$] AS T"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    For i = 0 To rs.Fields.Count - 1
        Debug.Print i, rs.Fields(i).Type, rs.Fields(i).Name
    Next
    rs.Close
End Sub

Field names and data types are read or interpreted correctly. TransferSpreadsheet for both acImport and acLink will work exactly the same way. So no explicit import specification is necessary, and therefore no saved import, and ultimately no control of the import using the IMEX form.

The SQL statement shown in the code could be used directly in an append query. Entering the path to the Excel file shouldn't overwhelm anyone.

 

[Danick]

Hi @Danick
Just for confirmation, did you see my post #12 as that may have got 'buried' by the subsequent exchanges.
I tried on two different computers running different versions of Access 365 and get no errors.
Much as I'd like to help, I cannot replicate your issue.

If you are unable to do so either, I strongly suggest you try the alternative approach as described in post #12 -18.
As already stated, link to the 2 Excel files using standardised names for each.
As the import isn't straightforward due to poor field names etc, import the data first to the temp (staging) tables which allows you to fix any issues using another set of queries or a VBA procedure for the final import

Hello @isladogs.

Yes I saw your post 12 which is why I uploaded just the one spreadsheet (export1.xlsx) for others to try. Forget about the first spreadsheet (export.xlsx) with the name errors. I am able to get (export1.xlsx) to fail by itself using the IMEX form on two separate computers running different versions of office every time. I created a blank database in both cases and was never able to change the folder path using IMEX. I'm not even able to run the task using the IMEX. So I just wanted to see if anyone else was getting this error when trying to change the folder path of this file using IMEX.

 

[Danick]

I dealt directly with the Excel table as it appears.

Sub test_XLFile()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim i As Long
    Dim sFullPath As String
   
    sFullPath = "D:\!!Samples\export1\export1.xlsx"
    Set db = CurrentDb
    sSQL = "SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & sFullPath & "].[Sheet1$] AS T"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    For i = 0 To rs.Fields.Count - 1
        Debug.Print i, rs.Fields(i).Type, rs.Fields(i).Name
    Next
    rs.Close
End Sub

Field names and data types are read or interpreted correctly. TransferSpreadsheet for both acImport and acLink will work exactly the same way. So no explicit import specification is necessary, and therefore no saved import, and ultimately no control of the import using the IMEX form.

The SQL statement shown in the code could be used directly in an append query. Entering the path to the Excel file shouldn't overwhelm anyone.

Hello ebs17

Please understand that the discrepancy in this thread are ONLY related to the task in the IMEX form. There are no other issues importing the files once the path is set.

 

[isladogs]

I'll continue to monitor this thread but as I am unable to replicate your issue, I cannot add any further suggestions at the moment.
As stated there are no issues when I tested your files with my IMEX app

Perhaps it would be better to change to the approach suggested above

 

[arnelgp]

another possible alternative than IMEX cinema.

 

[ebs17]

There are no other issues importing the files once the path is set.

If I take this statement to be 100 percent, your problem is not with the file or its contents, but solely with the path used. There's hardly anything anyone else can do.
What is this in the original?
The fact that valid paths are used, ideally without any special characters, should be understandable without further explanation.

 

[Danick]

Hello @isladogs,

I couldn't get IMEX to work on that one file and then when it failed on another SAP import, I figured I had to find another way. So thinking outside the box, I went the other way and just imported all my forms, tables, modules, queries, etc into your IMEX database just to see if it would work. And it does. So there must be something I was importing (or not importing) from you application that was messing up the IMEX app.

In any case, I've got it working now. Thank you so much for your patience in helping me try to figure out what went wrong. I have no idea what I missed in trying to adapt the IMEX app into my database, but I've spent too long now in trying to figure it out...

 

[isladogs]

Hi
No idea why importing into my database fixed it but I'm glad you got it sorted in the end.
I'm going to do some changes to this before I use it in a presentation to Access Europe in May
I'll look into your issue at the same time.
If I ever work out what happened in your case, I'll let you know