0

I have an MS Access 2016 application that a few people use in one department. I know this whole thing has web dev written all over it but this access database has been their process for a while and there is no time right now to switch over.

Recently, a different department wants to use this application, but having their own copy. Currently, if I need to make changes, I'll make the changes in a copy of the app, they send me a current version when I'm ready to import their data, I import it and send them back a new one. However, currently I copy the data table by table and past it into the new database. This is inefficient and tedious, and now with 2 sets of data I'd be doing this for, that's crazy. There's over 20 tables so I don't want to have to manually copy over 40+ tables across the 2 apps for even the smallest change like altering a message to the user.

I know I can copy the code so I can avoid importing the data, but sometimes for big changes I'll change over 15-20 vba files.

So, a couple questions:

1.Is there a way to generate insert statements for the entire database that I could run in a script? So when I create the new copy I just upload 1 file and it populates all the data?

2.Are there any kind of dev tools that will help this process? Right now I'm thinking that it's just a downfall of creating an MS Access app, but there must be some way that people have made the "new release" process easier. My current system seems flawed and I'm looking to have a more stable process.

EDIT: Currently I have all my data stored locally, attached to the same access file as the front end. Since I will have 2 different departments using the same functionality, how do I manage the data/the front-end? These 2 departments should have their own access file to enter data using the forms, so having 1 front end between the 2 departments won't work.

Also, should I create 2 separate back-ends? Currently I would have nothing to distinguish what is being inserted/changed/deleted from one department from the other. If I were to attach a field specifying who entered the record, that would require a complete overall of all my queries which I don't have the time for as there are deadlines I need to meet.

Michael
  • 3,093
  • 7
  • 39
  • 83
  • 1
    You need to **split** your database into frontend and backend. See e.g. https://stackoverflow.com/questions/1672077/setting-up-an-ms-access-db-for-multi-user-access and https://stackoverflow.com/questions/30738918/how-to-continuously-develop-and-deploy-an-access-2010-database-application – Andre Jun 20 '18 at 13:31
  • I'm not 100% clear about your environment, so my suggestions may need to be altered. Are you using a front-end/back-end setup? Does each user get a copy of the front-end? Using a server? First step is to automate as many of your manual steps as you can. Create code that will import all data from the user into your 'new' database.If you create some type of 'version control' via code, you could distribute your 'new db' and have it set-up on the users end. Need more specifics to continue... – Wayne G. Dunn Jun 20 '18 at 13:38
  • I added more detail to my question, please read the EDIT section. – Michael Jun 20 '18 at 13:49
  • Explicit info needed: (1) Does "Currently I have all my data stored locally, attached to the same access file as the front end" mean (a) everyone has a FE/BE setup; (b) both FE/BE reside on SAME workstation?? (2) Does everyone share a SERVER? If so, the BE('s) go on the server. (3) Would your FE changes apply to ALL departments or not? No harm in giving each department their own back-end -- unless they have common/shared data. – Wayne G. Dunn Jun 20 '18 at 14:16
  • Backend(s) reside(s) on a _shared network folder_. Frontends are copied to each user's _local AppData folder_ (see my article). If users are in two groups with no common data at all, use two backends. – Gustav Jun 20 '18 at 14:56

1 Answers1

2

First thing is to split the database. There is a wizard for this.

Then you can maintain the frontend without touching the real data.

Next, consider using a script to distribute revised versions of the frontend. I once wrote an article on one proven method to handle this:

Deploy and update a Microsoft Access application in a Citrix environment

Gustav
  • 53,498
  • 7
  • 29
  • 55