Errors in modImportExport.ExportSource during imex spec export

[jhgarrison]

Errors during imex spec export:

-------------------------------------
Exporting imex specs...       
  Spec 96
  1
  2
  3
  4
  41
  5
  6

-------------------------------------
ERROR: Error exporting 6
-------------------------------------
Error 10001: Error parsing JSON:
s (Click "Email subsc
          ^
Expecting ':' Source: modImportExport.ExportSource
  61

-------------------------------------
ERROR: Error exporting 61
-------------------------------------
Error 10001: Error parsing JSON:
s (Click "Email subsc
          ^
Expecting ':' Source: modImportExport.ExportSource
[9]
-------------------------------------

Export.log

[joyfullservice]

It looks like there might be a quote character in the IMEX import/export specification. If you are able to identify exactly where this is located in the table, we can probably make a tweak to the add-in to escape those characters in the JSON output. My hunch is that it is either in the SpecName or Field Name column. (You will need to show system objects to see these tables.)

Let me know what you find out! 😄

[jhgarrison]

You are correct, here's what's in the exported JSON

...
      },
      "Opted in": {
        "Attributes": 0,
        "DataType": 10,
        "IndexType": 0,
        "SkipColumn": false,
        "Start": 1889,
        "Width": 9
      },
      "Other email options (Click "Email subscriptions" at top for glob": {
        "Attributes": 0,
        "DataType": 12,
        "IndexType": 0,
        "SkipColumn": false,
        "Start": 1083,
        "Width": 350
      },
      "Other Skills": {
...

The problem is it's importing an Excel file where the field name contains quote characters:

It's an export from an membership management website. I can't easily change the field name at this point.

[joyfullservice]

Thanks for the screenshots! That is very helpful to see the additional detail. I see two potential issues here. One is the quotes, which we should be able to work around. The second is the truncating of the column name. Let me do a little testing on my end...

[joyfullservice]

Following up on this, it looks like the truncation on the field name is expected behavior, and may not be a problem.

[joyfullservice]

The fact that the quotes in the key name are not escaped is actually an issue with the modJsonConverter module, which comes from the excellent VBA-Tools/VBA-JSON project.

This issue is described here, with a pull request to resolve it located here. Incorporating the fix from the pull request seems to resolve the problem by correctly escaping the quotes in the key name.

Public Sub testJson()

    Dim dTest As New Dictionary
    
    dTest.Add "My ""Special"" column", "data"
    
    Debug.Print ConvertToJson(dTest, "  ")
    
End Sub

Before:

{
 "My "Special" column": "data"
}

After:

{
 "My \"Special\" column": "data"
}

I will plan to incorporate this fix in the add-in when I have the chance. It will be good to have that resolved as I could see that coming up in other areas where user-defined values are used as keys in the JSON definition files.

[hecon5]

@joyfullservice impressive speed.

[joyfullservice]

@joyfullservice impressive speed.

Thankfully this one was a quick fix. 😄

[jhgarrison]

This is still happening in 3.4.17 in the same two imex specs (don;t you hate it when that happens :-)

Do you want a new issue or can you reopen this one?

BTW, the Add-in is FANTASTIC. I feel so much better now that I can save the project in git... and the ability to do diffs!
Thank you!

-------------------------------------
Beginning Export of all Source
ShopUsersVer6.00.accdb
VCS Version 3.4.17
Using Fast Save
9/25/2021 9:20:42 PM
-------------------------------------
-------------------------------------
Exporting project...          
  ShopUsersVer6.00.accdb
[1]
-------------------------------------
Exporting vb project...       
  Shop User Database
[1]
-------------------------------------
Exporting vbe references...   
  stdole
[10]
-------------------------------------
No modified vbe forms found in this database.
-------------------------------------
Exporting proj properties...  
  Current Project Properties (Access)
[1]
-------------------------------------
Exporting saved specs...      
  Import-DoorCardList
  Import-memberlist
  import usage
  Import-5-Usage
  Import-7-Usage
  Import-8-Usage
  Import-9-Usage
  Import-10-Usage
  Import-11-Usage
  Import-12-Usage
[10]
-------------------------------------
Exporting db properties...    
  Database Properties (DAO)
[52]
-------------------------------------
Exporting shared images...    
  gwglogo
[1]
-------------------------------------
Exporting themes...           
  Office Theme
[1]-------------------------------------
Exporting imex specs...       
  Spec 96
  1
  2
  3
  4
  41
  5
  6

-------------------------------------
ERROR: Error exporting 6
-------------------------------------
Error 10001: Error parsing JSON:
s (Click "Email subsc
          ^
Expecting ':' Source: modImportExport.ExportSource
  61

-------------------------------------
ERROR: Error exporting 61
-------------------------------------
Error 10001: Error parsing JSON:
s (Click "Email subsc
          ^
Expecting ':' Source: modImportExport.ExportSource
[9]
-------------------------------------
No modified tables found in this database.
-------------------------------------
No modified queries found in this database.
-------------------------------------
No modified forms found in this database.
-------------------------------------
No modified macros found in this database.
-------------------------------------
No modified reports found in this database.
-------------------------------------
Exporting table data...       
  DBConfig
    Sanitized in 0.003 seconds.
  tblShop Use Reason
    Sanitized in 0.001 seconds.
  tblToolList
    Sanitized in 0.002 seconds.
  ttbAuthCapability
    Sanitized in 0.001 seconds.
  ttbLogMessageTemplates
    Sanitized in 0.001 seconds.
  ttbOrderItemTypes
    Sanitized in 0.001 seconds.
[6]
-------------------------------------
No modified modules found in this database.
-------------------------------------
No modified table data macros found in this database.
-------------------------------------
Exporting relations...        
  Shop User tabletblShop_LogIn_LogOut
  tblShop Use ReasonttbAuthCapability
  ttbAuthUserShop User table
[3]
-------------------------------------
Exporting doc properties...   
  Database Documents
[38]
-------------------------------------
No modified nav pane groups found in this database.
-------------------------------------
Exporting hidden attributes...
  Database hidden attributes
[17]
-------------------------------------
Done. (1.85 seconds)

--------------------------------------------------
                PERFORMANCE REPORTS
--------------------------------------------------
Object Type                   Count     Seconds
--------------------------------------------------
Project                       1         0.01
VB Project                    1         0.01
VBE References                10        0.04
Proj Properties               1         0.00
Saved Specs                   10        0.10
DB Properties                 52        0.14
Shared Images                 1         0.01
Themes                        1         0.00
IMEX Specs                    9         0.51
Table Data                    6         0.19
Relations                     3         0.02
Doc Properties                38        0.08
Hidden Attributes             17        0.01
--------------------------------------------------
TOTALS:                       150       1.12
--------------------------------------------------

--------------------------------------------------
Operations                    Count     Seconds
--------------------------------------------------
Read File                     37        0.03
Parse JSON                    29        0.48
Convert to JSON               4011      0.75
Compute SHA256                84        0.01
Console Updates               3         0.05
Compare Dictionary            28        0.00
Get Modified Date             3         0.00
Get File Property Hash        3         0.00
Clear Orphaned                11        0.25
Write File                    17        0.01
Verify Path                   25        0.00
Write to Disk                 1         0.00
Create Folder                 1         0.00
Export Theme                  1         0.00
Get VBA Hash                  129       0.05
Delete File                   6         0.00
App.ExportXML()               6         0.15
Sanitize XML                  6         0.00
Format XML                    6         0.00
--------------------------------------------------
Other Operations                        0.14
--------------------------------------------------


WARNING: The performance monitoring for operations still
had items in the call stack. This typically happens when
performance logging is started for an operation, but not
closed at the conclusion of the operation.
The call stack currently contains the following 1 items:
 - Parse JSON

[jhgarrison]

Some additional info. I tried running the export again today (with 3.4.17) and it got to this state

with the selection flashing at a high rate of speed (like it was being continuously refreshed in a loop). I tried capturing it the flashing with SnagIt but that caused

This what happened with 3.4.16 the second time and was what led to the leftover .laccdb file that later interfered with the install of 3.4.17.

Deleting the leftover .laccdb file then allowed the export to run, with the same two errors on the quotes in the field names.

So it appears that you can export once, you get the error, then a subsequent export gets into a loop.

With the following sequence of events:

1. Open my database
2. Database Tools/Addins/Full Export
3. This "works" the first time, albeit with errors on the fields with quotes
4. I note that this leaves a .laccdb file in the %APPDATA%/MSAccessVCS directory
5. A subsequent attempt to export goes into the loop and hangs Access, requiring killing the process (or clicking the close button which prompts you to kill the program).

It almost feels like the looping behavior is very similar in both the install case and the hang case.

[joyfullservice]

Can you delete the IMEX source files and try again? They should be in the imexspecs folder. I think the error you are having now is when it tries to read the existing (incorrectly formatted) file to compare it to the current data. If you delete the file, it will simply export a correctly formatted file, and everything should work as intended going forward.

[joyfullservice]

As to why Access is hanging during the export, you might check your current error handling settings. If your database does not allow breaking on errors, it will simply hang the program if an error is encountered. (Make sure the following box is checked to view/debug errors.)

One of the great benefits of having this add-in written in VBA is that you can actually debug and step through the source code live, just like you debug your own database application.

I recommend setting your VBA general options to Break in Class Modules:

There is also a setting in the add-in that will give you more opportunity to debug unexpected errors with the add-in code:

I recommend this for anyone actively developing the add-in, or trying to troubleshoot edge cases.

Hope that helps!

[jhgarrison]

Deleted the two wonky imexspecs and re-exported, and all is working smoothly now.
I will set things up as you suggest so I can troubleshoot any future problems.
Thanks

[joyfullservice]

Awesome! Thanks for the update!