Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    pdanes is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2019
    Posts
    208

    Import VBA code

    I recently had a database crash, hard, to the point where it lost ALL the VBA code. Forms, queries, data – all good, but every line of VBA code gone. I have backups, but I had done several days worth of development work just prior to this and hadn't backed that up yet. (Yes, yes, I know – spare me.) Found numerous others who had experienced this exact same event – ALL code mysteriously vanishes, and nobody had a solution. Apparently just another one of Access's more endearing features.



    But one of the things I had been fiddling with just prior to the crash was exporting all code to text files. I have those, and the code there is somewhat newer than my most recent backup, so I'm trying to re-import that. All the normal code modules and class modules load just fine, but when I try to import the code formerly attached to a form, it doesn't go in the right place, complaining that the name Form_DataDS (for example) is invalid. Instead, Access discards the name, creates a new class module, and simply calls it Class1, Class2, etc. I then have to manually copy the code out of the newly created class module into the form's code module, which doesn't exist, due to the crash, although the form's HasModule property is still set to Yes. I have to manually open the form in design view, click on something, anything to create an empty event procedure, which then creates the code module in the VBA window, and only than can I manually copy in the code that Access insisted on putting in a new class module. It doesn't help to set the HasModule property to No, not even completely removing the form.

    Is there a way to make this behave properly? I'm kind of alternating between doing all this manually and simply going to older backups, and redoing all the development work. Naturally, neither option is attractive.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have you tried importing everything into a new blank database then importing the code?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    pdanes is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Minty View Post
    Have you tried importing everything into a new blank database then importing the code?
    I tried simply creating a new, blank database, with nothing in it yet. Same result on import attempts.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could try a decompile. The code just might be there, but isn't visible. If that makes it show up again, then recompile and possibly import everything into a fresh db. And for cryin out loud, split the db if you have not. There is nor reason to lose all the data over something like this as well, which could happen if not split.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Micron View Post
    You could try a decompile. The code just might be there, but isn't visible. If that makes it show up again, then recompile and possibly import everything into a fresh db. And for cryin out loud, split the db if you have not. There is nor reason to lose all the data over something like this as well, which could happen if not split.
    Tried that - one of the first things. Some dregs of the code are still lurking in there somewhere, because it complains about name conflicts when I save the DB after importing the modules that do read in successfully. But it also refuses to compile, whines about duplication when I try to re-specify the proper libraries, which also vanished in the crash. I'm afraid the DB is properly hosed, and I will simply have to go to the backup, manually load what I can from the saved text exports and simply do the remaining development work over again. It's nuisance, but it's not a tragedy.

    And thanks for the extra advice, but I already know about splitting. However, I have very good reasons for not splitting this one, reasons that have nothing to do with this particular brainlock episode on my part. It's a bit involved and I don't want to bring unrelated topics into this thread, but I understand what it is and why it's generally a good idea to do it. But there are exceptions - special circumstances that make it a less than optimal design choice, and this is one of them.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I had something similar happen around 10 years ago. I was putting breakpoints in the code, executing the code, then while still in break mode, editing the code. Bad things happened after a while.
    What happens is that the VBA Project gets corrupted - if the corruption is mild, you can still look at the code... if the corruption is bad, all of the code disappears.

    I had copied all of the code from around 30 forms into separate text files - also copied the modules code to text files.
    Next, I had to open each form/report and set the HasModule property to No. Next was the "Compact & Repair".
    After that I was able to import the queries, forms and report into a new dB.

    The long part was:
    I created the Modules, pasted in the code for each module and compiled the dB.
    And since I wasn't able to export the code, for each of the forms/reports, I had to open each one in design view (one at a time), select one of the controls, create an event procedure, then paste in the code from the text file for the form. Next step was to click on the correct event for each control on the form that had code to relink the control to the associated VBA code event. Last step per form/report was to compile the dB, save, then "Compact & Repair".


    Lessons:
    - I no longer edit code while it is executing.
    - I do a "Compile" (in the IDE) about every 10 - 15 lines/changes.
    - I now do a "Compact & Repair" and save about every10 minutes.
    - And I make a back up about every 30 minutes.

    So far, so good.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    complaining that the name Form_DataDS (for example) is invalid
    just a guess but assuming the form actually exists perhaps the forms hasModule property is set to No?

  8. #8
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by ssanfu View Post
    I had something similar happen around 10 years ago. I was putting breakpoints in the code, executing the code, then while still in break mode, editing the code. Bad things happened after a while.
    What happens is that the VBA Project gets corrupted - if the corruption is mild, you can still look at the code... if the corruption is bad, all of the code disappears.
    Yeah, mine disappeared completely - even library references.

    The long part was:
    I created the Modules, pasted in the code for each module and compiled the dB.
    And since I wasn't able to export the code, for each of the forms/reports, I had to open each one in design view (one at a time), select one of the controls, create an event procedure, then paste in the code from the text file for the form. Next step was to click on the correct event for each control on the form that had code to relink the control to the associated VBA code event. Last step per form/report was to compile the dB, save, then "Compact & Repair".
    That's what I'm doing now. Lost some work, but no help for it at this point.

    - I no longer edit code while it is executing.
    I do that. It is too useful a capability (for me) to just stop using it.

    - I do a "Compile" (in the IDE) about every 10 - 15 lines/changes.
    - I now do a "Compact & Repair" and save about every10 minutes.
    I don't usually do it that often, but I do use those steps. Also I do a decompile pretty regularly.

    - And I make a back up about every 30 minutes.
    I even have a mechanism right in the database for making a backup copy of itself, time-stamped directly into the saved name. I just got lazy and casual, and hadn't used it for a while. But will I learn? Probably not - I'll be religious about doing it for a while, in the wake of this incident, then I'll likely slack off again.

  9. #9
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Ajax View Post
    just a guess but assuming the form actually exists perhaps the forms hasModule property is set to No?
    The forms all exist and their HasModule property is still set to Yes. I mentioned that in the original post. The code is still in there, somewhere, which is one of the frustrating things about it. The file size has not dropped, as would be expected if all the code had truly evaporated, and when I do an import, it whines about name conflicts between the newly imported code and something in the database - presumably the old code, since there isn't really anything else. Also, if I try to set the HasModule property to No, it warns me that code attached to the form will be lost, so it still has some links to something in there somewhere, including names of routines. But it won't show them or execute them. Probably, if I were familiar enough with Access's guts, I could go in with a hex editor and fix the problem directly, but I don't know anywhere close to enough about the innards to pull that off. Learning that might be interesting, but at this point, it's easier to just go to my backup and re-do some development work. It's not all that much and I have notes on most of it. It just pisses me off that Access will throw such ill-mannered tantrums.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If your suspicion that the code is still there is correct, then you may be able to import it from another database.
    However I strongly doubt that any lingering code is in a state where it could be rescued.
    I have worked extensively with hex editors and in my experience, once the the database reaches the state you describe, it is unlikely you will get enough useable info back via that approach.

    Also, why not setup an automated system for running your backup routine on a schedule that works for you e.g. each day or more frequently if appropriate. That way it sill happen without your intervention
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    If your suspicion that the code is still there is correct, then you may be able to import it from another database.
    However I strongly doubt that any lingering code is in a state where it could be rescued.
    I have worked extensively with hex editors and in my experience, once the the database reaches the state you describe, it is unlikely you will get enough useable info back via that approach.

    Also, why not setup an automated system for running your backup routine on a schedule that works for you e.g. each day or more frequently if appropriate. That way it sill happen without your intervention
    Hmmm, that's not at all a bad idea. I have code in the DB that opens a save dialog on closing of the main form, but I routinely dismiss it, because I open and close it so often during development. Usually I accept the save every so often, but in this case, I got slack. But you're right, an automated task would do it anyway. It would mean I'd have to remove accumulated backups occasionally, but that would be less work and less aggravating than this. I even already have such a system at work, after the network guys once wrecked the SAN and lost some other people some work. Not me, in that case, but after that incident I set up exactly what you propose here - an automated backup that took a snap of what I was doing every fifteen minues and copied all changes to my personal MEGA account. Good thought - I'll do it here on my home system as well. Thanks.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I no longer edit code while it is executing.


    I do that. It is too useful a capability (for me) to just stop using it.
    so do I

    one of the things I do on a regular basis (every few hours) whilst developing is to save close the file then open with the decompile switch which will get rid of all the bits of redundant code sitting in the background

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Did you export the modules as .cls files?

    I didn't see anyone else suggest this method but you may want to give it a try.
    I just tested it and it seems to work.


    1. Open the form module
    2. Delete anything that may be lingering such as "Option Compare Database" and "Option Explicit"
    3. Select Insert > File
    4. Navigate to your .cls file, select it, and click ok.


    There is some extra text at the top of the module you'll need to delete.
    Along the lines of:
    Code:
    VERSION 1.0 CLASS
    BEGIN
      MultiUse = -1  'True
    End
    Good luck.

    Click image for larger version. 

Name:	Screenshot 2020-12-19 133546.jpg 
Views:	25 
Size:	18.0 KB 
ID:	43705

    Click image for larger version. 

Name:	Screenshot 2020-12-19 133727.jpg 
Views:	25 
Size:	40.0 KB 
ID:	43706

    Click image for larger version. 

Name:	Screenshot 2020-12-19 133817.jpg 
Views:	25 
Size:	14.7 KB 
ID:	43707
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Standard modules are exported as .bas files.
    Class modules including form/report modules are exported as .cls files.

    However, both types are just text files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Ajax View Post
    so do Ione of the things I do on a regular basis (every few hours) whilst developing is to save close the file then open with the decompile switch which will get rid of all the bits of redundant code sitting in the background
    I wish Access had that as a normal part of the C&R process. Seems like exactly the sort of thing that should fall under such a heading, rather than being an unsupported and semi-hidden doodad.I have a small batch or VBScript file alongside all my development databases that does that - open with the decompile switch. It gets used all the time, and I don't think there is a serious Access developer anywhere that does not need to use this regularly. I don't understand why Microsoft continues to treat this function as a red-headed stepchild, instead of simply admitting that they are unable to write an application that doesn't routinely corrupt itself. We all know it anyway.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA code to import text Files
    By moorecurls in forum Programming
    Replies: 4
    Last Post: 02-21-2020, 02:58 PM
  2. Replies: 3
    Last Post: 02-13-2019, 10:22 PM
  3. VBA to Import CSV - Code problem with CR/LF vs LF only
    By jhrBanker in forum Import/Export Data
    Replies: 8
    Last Post: 09-11-2014, 01:32 PM
  4. Import Module Code Bug Problem
    By Ace2014 in forum Modules
    Replies: 17
    Last Post: 06-26-2014, 03:00 AM
  5. VBA Import Code Problem
    By jhrBanker in forum Import/Export Data
    Replies: 21
    Last Post: 05-16-2014, 10:15 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums