Results 1 to 10 of 10
  1. #1
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Creating a Code Library Add In???

    I started this years (and years) ago - How do you create a code library add in?

    Background:

    Currently BE is SQLSE, 2 FEs - one for daily stuff (reports, single entry, looking up info) and the other FE for importing monthly data from CSV files.
    One CSV file could/would be around 4,000 lines, another CSV file could/would be up to 30,000 lines
    I have two FEs that have a lot of code that is common to both. So I am make changes to one FE and don't get the changes copied to the other FE. I am getting real tired of having to try and find the changes that I missed. There was a tool (VBA Code Compare) that would compare the code from both FEs, but right now it still only works with mdb files. An update is in the works, but unknown at this time when the update will be completed.


    What little I remember is that , in A97/A2K, you could create a dB (.mda) with the common code, go to the IDE add in manager and add the dB to both FEs.
    Tada! Code only had to be modified in one place.


    But it doesn't seem to function the same way in the new dB format (accdb).



    Any pointers, info, examples???

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure what you are doing, but put the required code in a separate library db (can leave prefix as .accdb), and in the other db's link to it in the vba editor through tools>references

    Some points to note (not exhaustive)

    the code cannot reference functions/objects in the calling db except as they are passed back via global variables or function/sub parameters
    use codedb rather than currentdb to reference any tables that belong to the library db
    if you edit library code when called from another db, any changes will be lost when you exit - so not a good way to develop new routines

    Alternatively, put all your common subs and functions into one (or more) modules and just copy out to the other db's when changes are made

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks, Alex.

    Its kind of like a payroll system, but our system does other things and my two partners consider it proprietary.....

    Lets say there is a FE named MainFE and a FE named ImportFE. And the company has 50 employees.

    MainFE is for single entry; one at a time. You have a form to enter an employees basic info. Then there is another form to enter work hours. You select an employee, enter the hours, the date is default entered. When you leave that record, a sub, "CreateDetails", is called that does all of the calculations necessary.
    Lots of calculations: Gross pay, Fed Tax, State tax, city tax, HCA, loan repayment, 401(k) and the like. Some employees also get a fringe amount that can be sent to various places. All of these calculated amounts are saved in a table.

    Takes a while to enter all of the hours and there is the "fat fingering" to watch out for.


    Then there is the "ImportFE". The hours are sent to a CSV file. That file is imported into a table, then each of the records is processed, which entails calling the "CreateDetails" sub multiple times.


    Hours for 50 emps entered manually doesn't seem bad..... but when it is 3,000



    The MainFE" is now up to 25MB. The "ImportFE" is in the 10MB range. At the time of 97/2000, they were one FE (always was split)... but was not stable. Kept having corruption - so split the FE into 2 FEs.


    Due to scope creep (that I haven't been able to control ), the size and amount of code has grown. But I have duplicate code modules in the FEs I would like to consolidate into a common "library"....


    So what I'm trying to do is cut the "CreateDetails" code out of both of the FEs and put it into a "code library" that I can "add in" to each of the FEs.
    If I'm in the "MainFE" and enter in "hours", "CreateDetails" gets called as normal. If I am in the "ImportFE" and import all of the emp hours, it all runs seamlessly.


    Haven't tried your suggestion yet, but will in the next few days.....

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    OK, you have 2 front-ends! But how many back-ends? From your opening post follows you have 2 of them too, or none!

    Keep all your data (at least which is not user-specific) in single back-end, so there will be no need to copy the data somewhere. You can have as many front-ends as you need - identical (for different users for same tasks) or different (for different tasks) - which all get data from same back-end.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    one other thought, if users have both front ends is to make one of them the library to the other.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I did say "Currently BE is SQLSE", but not specifically SQLS express 2014....

    Only 2 of us will have access to "ImportFE." I've been reading about creating Add-Ins but can't find much info on HOW to do it.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Perhaps this link will help: http://www.databasedev.co.uk/access-add-ins.html

    Although written for A2003 MDA files, the principles still apply for ACCDA addins

    I haven't yet done this myself but its on my list of things to do ... so if you try it, please let me know how it works out
    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

  8. #8
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    You can use an .mdb file as a library file, not necessary to change it to .mda Add-In. I have customized FormWizard and ReportWizard Forms in my library. See that you don't give the same names of Library Functions, Forms in the FE Database, from where you call the Library Functions. Visit the following link for details and learn how to do it: https://www.msaccesstips.com/2009/03...e-library.html

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just following on from my previous reply, I tried creating the sample add-in as described in the link I provided.
    It was very easy to do and worked exactly as described.

    If you decide to try creating an ACCDA addin for any reason, the addin path is a hidden folder: C:\Users\YourUserName\AppData\Roaming\Microsoft\AddIns
    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

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks, Colin. I haven't had time lately to experiment with this, but from your comments, I hope to figure it out.

    Marking this closed for now....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-27-2016, 10:20 AM
  2. Creating a VBA code
    By Esmatullaharifi in forum Programming
    Replies: 2
    Last Post: 01-03-2015, 02:10 PM
  3. VBA code for creating new record
    By Naveen Marapaka in forum Programming
    Replies: 3
    Last Post: 12-23-2012, 02:13 PM
  4. Creating 'Common' VBA code
    By Nevsky78 in forum Programming
    Replies: 4
    Last Post: 06-18-2012, 02:07 AM
  5. Need help creating library report
    By iansan5653 in forum Reports
    Replies: 5
    Last Post: 01-31-2012, 06:23 PM

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