none
LoadFromText clears global and form instance variables RRS feed

  • Question

  • We use LoadFromText to load reports on-demand into the currently executing Access database. Unfortunately, we recently discovered that executing LoadFromText resets global and form instance variables (not immediately, but as soon as Access is idle). I can provide a minimal repro example, if needed.

    I found this issue documented in an old newgroup thread (0d4601c36be3$1cea9630$a501280a@phx.gbl). An Access MVP speculates that this must happen because  VBA code could be imported, but I disagree: DoCmd.TransferDatabase can also import reports that include code modules, but it does not reset variables.

    I know that the chance is slim, since LoadFromText is an undocumented and unsupported feature, but I'll ask nevertheless: Has anyone found a workaround or fix for this issue?

    Best regards
    Heinzi

    PS: If you are curious about why we do this: We used to store reports in a separate database and import them with TransferDatabase, but as the number (many hundreds) and size (images!) of reports increased, we ran into serious performance and stability issues. Individual text files created with SaveAsText are much easier to manage/version, they are faster, and they are much less prone to corruption than a multi-GB Access file.




    • Edited by Heinzi.at Friday, June 16, 2017 3:13 PM
    Friday, June 16, 2017 3:11 PM

All replies

  • Hi,

    As you said, the "feature" is undocumented, so I don't have a real answer for you. However, since you asked for a workaround, this is just a suggestion.

    Before loading the report from text, is it possible to store the variable contents in a temporary table and then reload them again (somehow) after the report is loaded?

    Just my 2 cents...

    Friday, June 16, 2017 3:45 PM
  • Or perhaps better still, use hidden textboxes on forms or the TempVars collection to store these values.  I find global scope variables particularly fragile.  I am a bit surprised to hear that loading a report object via LoadFromText will cause a form-level variable on an unrelated form to reset though.  At any rate, values stored in textboxes or TempVars are somewhat more resilient.

    -Bruce

    Friday, June 16, 2017 4:12 PM
  • Well, I don’t think issues centers around the feature being documented or not.

    The simply matter is you are at runtime attempting to modify your application and any application that attempts to modify the source code at runtime is doomed to failure. So from the get go this is a VERY bad development paradigm.

    It not at all clear why you at runtime “import” reports. The typical development approach for the last 50 years is:

    You develop, create, test, and design your application.

    You then compile the application down to an executable (an accDE). You then deploy that compiled application to each desktop. Any other approach quite much over time not going to work well.

    So end users should be using and receiving a correctly designed application that you deploy – that means a pre-built and compiled application.

    As for the global var re-set? Well, compiled applications NEVER in ANY circumstance re-set their local, or global variables – even for un-handled errors. In fact this is a SIGNIFICATE additional reason as to adopt distribution of a pre-compiled applications. So all variables remain intact no matter what occurs.

    Having said the above? I am not aware that importing reports, or importing reports as text will re-set all variables. I don’t see this behaviour at all.

    What you CAN NOT do is enter debug mode and single step mode. The debugger prevents loadFromText working.

    This tiny code snip works just fine if I hit f5 to run

    Sub TestImport()

       Dim s       As String
      
       s = "c:\test\tPerson.rpt"
      
      ' On Error Resume Next
       LoadFromText acReport, "T2", s
      
       Debug.Print s
      
      
    End Sub


    The value of “s” remains intact and it works just fine. I have in above commented out the on error resume next (it is not required).

    Do keep in mind that ANY unhandled error in a accDB WILL re-set and blow out all variables (local and global). And keep in mind that ANY unhandled error in a accDE will NEVER re-set never lose and will always retain the value of any variable – including cases where no error handling occurs. This thus means your application becomes VERY reliable and things like recordsets etc. ALWAYS retain their values – the result is a real solid working application and one that is far more reliable for your users.


    Regardless of my above points, I am not aware that loadFromText blows out and re-sets variables – it does not, but you likely having some un-handled error such as a locked file, permissions, or a missing file that is causing a un-handled error which in turn will blow up your variables – it not LoadFromText as that command does not re-set variables. This means your import code is resulting in a un-handled error.

    If you going to all the trouble to import the reports it seems to me that you should be ok – but why not keep those reports as the part of the development application since you need them anyway?


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada



    Friday, June 16, 2017 4:30 PM
  • Or perhaps better still, use hidden textboxes on forms or the TempVars collection to store these values.

    Yes, that's what we plan to do if we don't find a better workaround.

    I am a bit surprised to hear that loading a report object via LoadFromText will cause a form-level variable on an unrelated form to reset though. 

    Surprising, isn't it? If you're interested, I'll post a short repro example in reply to Albert's answer.

    Best regards
    Heinzi

    Friday, June 16, 2017 7:54 PM
  • It not at all clear why you at runtime “import” reports.

    Because (almost) every customer wants invoices and other order documents in their own corporate design (which usually involves more than just replacing an image).

    You then compile the application down to an executable (an accDE). You then deploy that compiled application to each desktop. Any other approach quite much over time not going to work well.

    We do. All our business logic is in a library accde and some .NET DLLs. We put a accdb in front of that to be able to import the reports that our consultants design on-premise for the customer (and for other technical reasons as well, but let's stick to the subject at hand).

    I am not aware that importing reports, or importing reports as text will re-set all variables. I don’t see this behaviour at all.

    Well, here's a minimal example to reproduce the issue:

    1. Create a report file (i.e., use SaveAsText on some arbitrary report).
    2. Create a new accdb with a single form an a single button named "mybutton". Add Form.Load and mybutton.Click event handlers:

    Option Compare Database
    Option Explicit
    
    Dim m_long As Long
    
    Private Sub Form_Load()
        m_long = 1
    End Sub
    
    Private Sub mybutton_Click()
        MsgBox m_long
        
        LoadFromText acReport, "TestReport", "C:\temp\Test.rpt"
        
        MsgBox m_long
    End Sub
    

    3. Start the form and click on the button.
    4. Note that "1" is displayed twice. (So, no values have been reset yet!)
    5. Click the button again! (This is important!)

    Expected result: "1" is shown.
    Actual result: "0" is shown.

    This also demonstrates that the values are not lost immediately after LoadFromText.

    Best regards
    Heinzi

    Friday, June 16, 2017 8:06 PM
  • Hi Heinzi,

    Pardon me, but I followed your simple instructions and did not get the same behavior as you described.

    I got the same value no matter how many times I click on the button.

    I am using Access 2010.

    In any case, if it's not too much trouble, like I said earlier, one workaround is to store the variables in a temporary location, like TempVars, so you can reload them again.

    Just my 2 cents...

    Friday, June 16, 2017 8:22 PM
  • Here's an image and video of my quick test.

    Friday, June 16, 2017 8:39 PM
  • Thanks, DBGuy, that's really interesting! I'll do some more tests on Monday when I'm back at work and try to find the difference.

    Friday, June 16, 2017 9:00 PM
  • Which version of Access are you using? If you like, I can send you my test file.

    Just a thought...

    Friday, June 16, 2017 10:06 PM
  • Hi Heinzi.at,

    I try to test the same code that you had posted above.

    I also got the same result like .theDBguy

    I always got the 1 in msgbox.

    I did not get 0.

    please try to test the code again and let us know about your results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 19, 2017 1:55 AM
    Moderator
  • Hi .theDBguy and Deepak!

    Thanks to both of you for trying to reproduce the problem, and my sincere apologies for posting incomplete repro instructions.

    I did some more tests and, apparently, the issue only occurs if the report has a code file associated with it. I tried the following two cases:

    A: A new empty report, close and save it, export with SaveAsText.
    B: A new empty report, click the "View Code" button on the Design ribbon, close and save the report, export with SaveAsText.

    In the exported file, the difference can be seen at the very bottom of the file (the part starting with "CodeBehindForm" is missing in case A).

    Code

    Demo

    According to some more tests, inline method calls (Control Source = "=SomeFunctionDefinedInAnExternalModule()") seem to be fine (i.e., don't cause module/global variable loss).

    This helps a bit, because it means that we could use slim-and-easy text files (with LoadFromText) for reports without code and accdb files (with DoCmd.TransferDatabase) for reports where code is required (for advanced formatting or gathering of additional data).

    In any case, if it's not too much trouble, like I said earlier, one workaround is to store the variables in a temporary location, like TempVars, so you can reload them again.

    We are also considering this workaround, but we'd like to keep it as a last resort, because it means going from "pretty code" (compile-time type checks, form scope) back to "ugly code" (run-time type checks, global scope).

    I'm still curious of anyone can think of a way to make LoadFromText work with reports with code modules.

    Best regards
    Heinzi

    Monday, June 19, 2017 8:47 AM
  • Hi Heinzi.at,

    I again try to reproduce the issue from your last post.

    I developed the same code as yours.

    Option Compare Database
    Option Explicit
    Dim m_long As Integer
    Private Sub Command0_Click()
    MsgBox (m_long)
    LoadFromText acReport, "Form37", CurrentProject.Path & "Form37.rpt"
    MsgBox (m_long)
    End Sub
    
    Private Sub Command1_Click()
    MsgBox (m_long)
    LoadFromText acReport, "Form38", CurrentProject.Path & "Form38.rpt"
    MsgBox (m_long)
    End Sub
    
    Private Sub Form_Load()
    
    m_long = 1
    
    End Sub

    this time I can reproduce the issue.

    but this behavior is no where mentioned.

    I try to find the official documentation regarding 'LoadFromText' , but only one blog is available.

    Quick Overview of SaveAsText and LoadFromText

    further you can try to submit the feedback to Access User Voice regarding this issue.

    Welcome to Access Suggestion Box!

    you can use the workarounds suggested by the community members to temporary solve the issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 7:20 AM
    Moderator