Split database slow when developing

[Dumferling]

I have a spit database FE on computers/ BE on shared server. The compiled version works at about the speed I would expect it to but when I am adding features to the FE (which also sometimes means I edit the BE and add new tables or fields) the FE is incredibly slow - opening forms, queries, editing a form - anything - it a 2 or 3 minute wait for something to open. It doesn't make a difference if I am in or out of office - the speed is about the same (out of office I am connected on a high speed fibre line). The BE is much quicker but isn't dealing with the heavy load of forms.

I am wondering if there is anything I can do. I appreciate that this is a network issue but I can't see our network people taking me seriously so I am wondering if there is any sensible procedure that I don't understand to work on a version and then deploy it again. Logically I think it is something like work on a version which is not split on my desktop, make sure I know what changes I have made to the BE (table changes) and then split the version on my computer into BE and FE, update the BE with the change and deploy the FE. It seems like a lot of work considering but is there a better way? The slowness is killing me.

 

[jdraw]

I suggest you tell us more about the application, the environment(s) (development/operations) and your experience with database.
Many will develop on local machine, then split - but some will have a separate development/testing networks and lots of discipline.
Front end and back end all Access or??
We really need more detail to help put your issue into context and offer more focused responses.

 

[Minty]

I agree with @jdraw we'd really need to know a bit more.
But, one thing does spring to mind, do you have any form timers running in the background, they can really affect design views becoming slow.

Also, forms with lots of sub-forms can be painful to load if they have complicated source queries being loaded.
Try only setting the source object in code once the main form has loaded, that way the subforms underlying queries won't be loading every time you switch to design view.

 

[Dumferling]

OK. Context. I am not a professional programmer. I am actually the head of a legal department and we use Access to handle legal data - contract records, product records, IP records. We don't store documents only links to documents. I do all the development work and everything is self taught - google and forums mainly.

The database is access front and backend. Changing over to another backend will mean I will lose my ability to make changes - I will have to work through one of out IT people. I don't really need a bigger backend - the BE is only about 8MB in size (I think it is 8MB but anyway way below the limit imposed by Access). There are about 150 tables and about 200 to 300 forms and reports.

I use UNC links between the FE and BE and it connects neatly for the different users - everyone has different mapping so UNC is the only way to go - found that out the hard way. The shared folder is just that - a shared folder on a network.

I do a lot of development work - at least one change a day - fixing errors and adding functionality and reports. Because this is not my full time function and I am a total amateur, I invariably have errors that creep in and also the database evolves to suit our needs so if I do a report I find a need new data, I insert that into the database and distribute an new FE.

Because the database is always live, the BE data is changing and I can't, in my understanding, redeploy a BE without losing data changes - I have to make the changes to the BE where it stands. The FE I can make changes to but the fact that I have a split database is causing an issue.

The funny thing is that the compiled version has continued to work at about the same speed but the moment I move into development everything slows down drastically - severely affecting my ability to get change done fast. I am wondering it there isn't some trick there that I don't understand about.

The only way I can think around this issue is to have a parallel version (FE and BE split but both on my machine) and carefully track the BE changes necessary so that I can make parallel changes on the live BE. I can then deploy the FE as and when I need to.

I am hoping there is a better way though. Hope this helps

 

[jdraw]

Changing an operational/production system on a daily basis may indicate design issues.
A few questions/comments:
How do you reconcile an "amateur/below the radar" multi user Access application in a law firm with the IT department?
Is this really skunk-works outside of any IT support?
How do you do backups?
What has been the biggest data loss issue to date?
What is the process to test new/updates, accept and place into production?
What exactly is the "... compiled version has..."?

Many will adopt some sort of version control. This is version1 - next will be version 2 that will address
- (shortcoming a,c, and c)
-new form design X,
-modified Report #6.
To do and anticipated in version 3 - redesign of input forms W and X, improved verification.....

Also, many have a "quasi-parallel" FE/BE on a test network or machine with some procedure for testing and accepting change.
Many will have an automated set up whereby the "official FE" is placed in a known location. When a user logs onto the system, a check is made to ensure his/her FE is the official version, if not, a copy will be placed onto that user's machine automatically. This removes the manual email/transfer and individual set up of revised FE on various machines.

I'm sure there are many here with more ideas and cross-checks to keep the code and performance quality to a high level.

 

[Dumferling]

It is not actually a law firm. I work for a large insurer. If I waited for IT support to design and do what I wanted, I would be retired. So I did it myself. So, yes, it really is outside of IT support.

I backup every morning to two different locations, both BE and FE.

I have yet to lose any data - There are only 2 full access (no pun intended) users and about 3 or 4 people with partial access to certain modules.

There is no process to test updates. I see the need, make the changes, check that it works, send my colleague a new version if needed.

I track each version by logging my changes in a table so I have a reasonable process to track changes and I have a wishlist/ development list of features that I would like to implement.

I realise that to a proper developer what I do is akin to heresy and probably I should be burnt at the stake. But there is nothing that can do what my database does for my department and nothing that can change as quickly as I can change my database. It doesn't contain mission critical business information and I am never more than 24 hours out of date. I don't have budget and my business isn't going to give me any so needs must where the devil drives.

 

[jdraw]

I hear you loud and clear. Just trying to emphasize that there are a lot of "dead pioneers with arrows in their back".
Doing so much that is so important with so little for so long and no budget can give a different perspective on things.
You seem to have things under sufficient control.
Others may comment on your speed issue. I would suggest a parallel FE/BE on your on machine for testing/enhancement. But you may have to adapt your transfer to production scheme.
Management must be aware of your "application" if there are ~6 people using it. Do they not appreciate what it does? Perhaps it's time to do a little marketing and possibly gets some resources to "formalize/stabilize" this key information utility for the company. You seem to be chief cook and bottle washer - read that as single point of potential failure- so getting some formal procedures for operations etc may be a prudent move (sooner than later).
Good luck.

See this FMS info re Split database

 

[CJ_London]

With that level of changes it is possible your development FE has become quagmire with redundant code.

suggest

1. Compact the back end - which should be done on a regular basis, perhaps as part of your backup routine

2. decompile and recompile the front end, then compact

on my phone at the moment but google ‘decompile access’ or similar

see if that makes a difference

 

[The_Doc_Man]

Reading through your posts, I have a question that might have been covered, but I don't see the answer. Do you have one FE per user or do your users share the FE?

As to HOW to privately develop, the way that I used ate a small amount of disk space but kept me from crunching everyone's toes. This is one man's success story. This method worked for me for at least seven or eight years before I retired.

I had four folders called DEV, TEST, STAGE, and PROD. I kept the master copy of the developing FE in the DEV folder along with a copy of the BE. The DEV copy of the FE mapped to the DEV copy of the BE. However, there was nothing magic about the BE unless I was in the process of changing table structure, which DID complicate matters a little. The DEV copy was also wide-open in that it didn't have any of the safeguards against folks seeing the infrastructure. It didn't have an opening form and it didn't do things to hide the ribbon and right-click object menus.

But... I mucked about in the FE and did whatever I needed to do with impunity behind the DEV folder. When it was time to test, I copied the FE over to the TEST folder, which also had a TEST copy of the BE. Testing neither bothered the DEV copy nor the production copy. Eventually the FE worked, so it was time to publish. Again as a matter of safety, I copied the FE to the STAGE folder. But this one didn't have its own BE. This is where I relinked everything to the production BE and I also turned on the features to isolate things, such as having a switchboard form that turned off the ribbon and controlled the context menus and other such features. I then ran the FE from the staging area to verify one last time that it would run with the 'live' BE file. If so, I copied the current PROD version of the FE to a BACKUP folder (and added a date-stamp to the name). Then I moved the STAGE FE to become the PROD FE.

Why, you say, did I do that? To make it more difficult for me to be a thumb-fingered idiot and muck the wrong copy. By forcing that separation, the resulting isolation protected the other copies.

The procedure for changing the BE was a LITTLE trickier. If I knew that one of my changes was going to hit the BE file, I made notes of EXACTLY what I was doing to the BE. The DEV copy was first. Then, when testing, I copied both the FE and BE to TEST, then relinked the (new) TEST copy of the FE to the (new) TEST copy of the BE. Again, that separation made it difficult for me to hit the wrong folder. But here was the tough part. When it became time to do something to the PROD copy, I had to issue a notice to users that due to an upgrade, the whole DB would be down for X hours from Y to Z o'clock. Then I moved the TEST copy to STAGE. I made a BACKUP copy of the PROD BE and then moved THAT to STAGE. When the BE is no longer in PROD, the FEs will not work, so nobody could do anything to it. I then consulted my detailed notes on what was done to the BE file so that I could repeat the process on the PROD copy. One last "viability" check, and then the new FE and BE would get moved to PROD.

Before you ask, I did this for the U.S. Navy and they said it was acceptable to their production standards.

 

[Dumferling]

I hear you loud and clear. Just trying to emphasize that there are a lot of "dead pioneers with arrows in their back".
Doing so much that is so important with so little for so long and no budget can give a different perspective on things.
You seem to have things under sufficient control.
Others may comment on your speed issue. I would suggest a parallel FE/BE on your on machine for testing/enhancement. But you may have to adapt your transfer to production scheme.
Management must be aware of your "application" if there are ~6 people using it. Do they not appreciate what it does? Perhaps it's time to do a little marketing and possibly gets some resources to "formalize/stabilize" this key information utility for the company. You seem to be chief cook and bottle washer - read that as single point of potential failure- so getting some formal procedures for operations etc may be a prudent move (sooner than later).
Good luck.

See this FMS info re Split database

Thanks. My executive is well aware of what I am doing but he is also the CFO so saving money.... When I have it stabilised and it does what I think it can, then I will probably push for funding and get it formalised. Hopefully I avoid the arrows!

 

[Dumferling]

With that level of changes it is possible your development FE has become quagmire with redundant code.

suggest

1. Compact the back end - which should be done on a regular basis, perhaps as part of your backup routine

2. decompile and recompile the front end, then compact

on my phone at the moment but google ‘decompile access’ or similar

see if that makes a difference

I do that on a regular basis to both BE and FE so sadly that will not change my development speed although it does keep my file size down and minimise the occasional code problem in production.

 

[Dumferling]

Reading through your posts, I have a question that might have been covered, but I don't see the answer. Do you have one FE per user or do your users share the FE?

As to HOW to privately develop, the way that I used ate a small amount of disk space but kept me from crunching everyone's toes. This is one man's success story. This method worked for me for at least seven or eight years before I retired.

I had four folders called DEV, TEST, STAGE, and PROD. I kept the master copy of the developing FE in the DEV folder along with a copy of the BE. The DEV copy of the FE mapped to the DEV copy of the BE. However, there was nothing magic about the BE unless I was in the process of changing table structure, which DID complicate matters a little. The DEV copy was also wide-open in that it didn't have any of the safeguards against folks seeing the infrastructure. It didn't have an opening form and it didn't do things to hide the ribbon and right-click object menus.

But... I mucked about in the FE and did whatever I needed to do with impunity behind the DEV folder. When it was time to test, I copied the FE over to the TEST folder, which also had a TEST copy of the BE. Testing neither bothered the DEV copy nor the production copy. Eventually the FE worked, so it was time to publish. Again as a matter of safety, I copied the FE to the STAGE folder. But this one didn't have its own BE. This is where I relinked everything to the production BE and I also turned on the features to isolate things, such as having a switchboard form that turned off the ribbon and controlled the context menus and other such features. I then ran the FE from the staging area to verify one last time that it would run with the 'live' BE file. If so, I copied the current PROD version of the FE to a BACKUP folder (and added a date-stamp to the name). Then I moved the STAGE FE to become the PROD FE.

Why, you say, did I do that? To make it more difficult for me to be a thumb-fingered idiot and muck the wrong copy. By forcing that separation, the resulting isolation protected the other copies.

The procedure for changing the BE was a LITTLE trickier. If I knew that one of my changes was going to hit the BE file, I made notes of EXACTLY what I was doing to the BE. The DEV copy was first. Then, when testing, I copied both the FE and BE to TEST, then relinked the (new) TEST copy of the FE to the (new) TEST copy of the BE. Again, that separation made it difficult for me to hit the wrong folder. But here was the tough part. When it became time to do something to the PROD copy, I had to issue a notice to users that due to an upgrade, the whole DB would be down for X hours from Y to Z o'clock. Then I moved the TEST copy to STAGE. I made a BACKUP copy of the PROD BE and then moved THAT to STAGE. When the BE is no longer in PROD, the FEs will not work, so nobody could do anything to it. I then consulted my detailed notes on what was done to the BE file so that I could repeat the process on the PROD copy. One last "viability" check, and then the new FE and BE would get moved to PROD.

Before you ask, I did this for the U.S. Navy and they said it was acceptable to their production standards.

Thanks for this. I was thinking of something like this although I hadn't thought to check it against the network BE before releasing. I like the idea of moving it through a stage or two. The detailed notes on the BE are the bit I was hoping to avoid but if thats what it takes, thats what it takes. Thanks for confirming that at least my thinking isn't ridiculous - I would hate to do this and then find out that I just need to click on this and that and it would all work more easily! Incidentally everyone has their own FE.

 

[theDBguy]

Thanks for this. I was thinking of something like this although I hadn't thought to check it against the network BE before releasing. I like the idea of moving it through a stage or two. The detailed notes on the BE are the bit I was hoping to avoid but if thats what it takes, thats what it takes. Thanks for confirming that at least my thinking isn't ridiculous - I would hate to do this and then find out that I just need to click on this and that and it would all work more easily! Incidentally everyone has their own FE.

Making changes to the BE is/should not be any different than making any changes to the FE - you do it all on a copy. When it comes time to applying those BE changes to the production/live BE, you can either do it manually or use/create a utility to do it all for you automatically. Let me see if I can find a link for you.

 

[theDBguy]

You could try this one...

Back End Update Utility

The purpose of the attached code is to allow Access developers to make changes to their clients back...

www.rogersaccesslibrary.com

 

[Isaac]

Make sure that when you do your development, that FE copy you're working on is currently on your local machine (i.e. desktop), not a network folder