Split database during construction?

[twgonder]

I'm still in development and adding and modifying tables (and table names when needed) every day.
The test data for geography is starting to get big.
I've heard several people say that I need to split the database. I get it.
My question is, is this advisable while still in heavy development?
Will I add new tables, after the split, to the FE and then move them to the BE as they become more formalized in design,
or will I do all my work to the BE as far as tables go?
If I do create tables for design and testing in the FE, is it easy/safe to move them to the BE later (or do I want to avoid that)?

Any other gotchas I should be aware of during the heavy thrashing phase of development?

I'm also wondering about Access and what it might contaminate in certain conditions.
For example:
1) With nothing else running, I've added fields to a table, had Access pause, seemingly crash and want to do a backup before continuing.
2) While writing code in a module, with nothing else but the form sitting in design view, I've had Access do the crash scenario I just described.
3) I've been creating queries and had the same crash scenario.
4) The same with designing forms and reports.

After these crashes, data has been contaminated in some of the files, even ones that weren't related to the table or form being worked on.
Could this get better or worse after a split?

 

[June7]

I say split now. It's not difficult to move table to BE and if that's easier for you, fine, but I would just build it in backend.

I experienced several crashes and corruption of frontend during development when a lot of changes took place.

 

[theDBguy]

I also vote for splitting now. Do all table work in the BE and always keep a backup of both FE and BE while doing major design changes. Remember, RI cannot be enforced if the related tables are not stored in the same container. You just need to create a linked table in the FE every time you create a new table in the BE.

 

[The_Doc_Man]

Once you have done a split, you always add tables to the BE database. Making the split imperfect (i.e. some FE, some BE tables) leads to confusion and makes the FE larger than it should be. There IS a rare time where having a table in the FE is appropriate, but it is a limited situation and for general table data - particularly if it is to be shared among all users - you want all data in the BE.

Your other numbered comments - all four of them - are generally signs of corruption. Make a copy of the DB immediately as a backup. Then try to Compact & Repair (C&R). After the C&R, if that pause/crash scenario repeats itself, you have a corrupted DB that may require reconstruction. One thing to try before taking the drastic reconstruction step is to decompile and recompile. In the link below, the Decompile article isn't the first topic so scan down for it.

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

If neither a decompile nor a C&R help with the pause/crash scenario, you have to rebuild from scratch as follows:

1. Make a new empty DB.
2. Open the empty DB. (I'm belaboring a point here...)
3. Using the external data tab on the ribbon, tell Access you want to get external data.
4. IMPORT all of your tables, queries, forms, reports, macros, modules, relationships, and anything else that it lets you import - from the old DB to the new one.
5. Verify that you update the references in the new DB to match the old DB.
6. Compare the original against the imported copy. Any element that didn't make it was corrupted in the old file and couldn't be copied. You might have to try to export that table from the original file to a CSV file, then import it into the new file.

I do NOT suggest that you EXPORT from the old file to the new blank DB. I've had that fail more often than the "Import into a new file" method.

I also wish to point out that if you are in development, it isn't usually that easy to corrupt a DB that isn't yet being shared by others. The first thing to watch for is turning off the PC without closing things. The second thing to watch for is if you have the DB split AND it is on a different host system, watch for spot outages in the form of power loss or network loss. If you have a third-party software item involved, that can also do it if the item isn't well-behaved. But frequent crashes for a database that is not over 1 GB would be highly unusual. Recurring crashes even after C&R, decompilation, or reconstruction would be indicative of something wrong with the computer's physical environment or with the installation of Office or the installation of Windows.

 

[twgonder]

Thanks for the tips. I'll give the split a try tomorrow.
@ Doc, I did all the steps you outlined, three times. I wasn't too mindful of missing data as it's all just demo stuff, but text in fields had funny characters even after being imported. Still the crashes persist. I've been given a lot of advice on how to avoid crashes, but I seem to do something that Access has a problem with.

@ June7 and theDBguy, when you say "build it in the backend", do you mean shut down the front end, then open the the back end file and create the table there? Or do you mean create the table in the FE for the BE and do the actual field work in the FE? This part has me confused. It would be a pain to have to leave one db file to work in the other.

 

[June7]

We mean open the backend and create tables there. Don't have to close the frontend to open the backend and make edits but don't have frontend actually pulling from any tables that you want to edit.

Pain or not, that's the advice.

 

[gemma-the-husky]

Split sooner rather than later, especially if you intend the database to be a multi-user database. One point is that some things work differently in a local table, compared with a linked table. For instance you can't use "seek" on an indexed table, and it's better code to be developed in the way it will be used after release.

Chinese characters (is that what you mean) generally indicate some sort of corruption. This can be caused by data loss when working with split databases, especially hem users work with a wireless connection. As you are not working with a split database, then it's surprising you are getting corruptions. Therefore I would do a compact and repair. If it still keeps happening, create a new blank database, and import everything from the original so you have a clean environment.

if you have corrupt data you might be able to delete the bad data and start over. If you can't do that you might be able to select the good rows into a new table. Failing that, delete the whole table and start over.

Finally, check all your code modules, including form and report code modules, and make sure that the words "option explicit" are included at the top of all modules. Insert that statement if it's not there already. Then open any code module, and in the editor select tools/option at the top, and check "require variable declaration". This will include "option explicit" in every module you create from now on.

After having made sure that "option explicit" is at the top of all of your modules: while in any code module select "debug\compile" at the top. This will compile\check your database for various types of errors, and then the compile option will grey out, until you make changes again. Adding "option explicit" before compiling will detect errors caused by failing to declare all variables, (including typos) and will also check for syntax errors (an if without an end-if for example). There may not be any, but it's important. Typing variables is very important. You declare them as strings, numbers and so on, so you can't accidentally assign an "inappropriate" value to a variable. If you don't give a variable a type, then it becomes a variant, and can accept any type, including a null, which may in fact be a source of logic and run-time errors. Actually using a data type of variant is very unusual and should be avoided in most cases. It's worth getting used to good habits like this.

Because of errors, take regular back ups, so you can easily go back to a "working version"
That's another reason to split the database. Then you have a data-database (backend) with just the data, and a code-database (frontend) with no data, but all the forms and queries.

Now you only need to backup your data regularly.
You don't need to back up your code database because it doesn't change. All you need is an unused master copy of the code database for safe keeping. In fact most of us will have versioned copies of the code. So you currently use version v1.4, but you have safe copies in another folder of all old versions.

eg back up copies of
Code_v1.0
Code_v1.1
Code_v1.2
Code_v1.3

so you can always go back and find a good version of the code, or recover a working version of a form that you deleted in version 1.3, but now want to use again. Things like that.

You now also have an easy development environment,

You use your current code (a copy of v1.4) against a copy of the backend, as a development area, and when the changes are complete, you can release a new version, now v1.5 to use against the real live data. You just can't distribute updates easily like this without splitting the database.

 

[The_Doc_Man]

text in fields had funny characters even after being imported. Still the crashes persist.

OK, after 3 tries it is time to try something else. Usually with Office products there is such a thing as a "repair installation." You run the REPAIR option to tell Access to restore anything that is missing, and it does other checks as well. Try that.

If the REPAIR fails then some file in Access itself has become corrupted. The only thing left is to totally remove and then reinstall Access.

 

[twgonder]

OK, after 3 tries it is time to try something else. Usually with Office products there is such a thing as a "repair installation." You run the REPAIR option to tell Access to restore anything that is missing, and it does other checks as well. Try that.

If the REPAIR fails then some file in Access itself has become corrupted. The only thing left is to totally remove and then reinstall Access.

I've done the repair twice now (remember the Access "touching" Outlook thread)? Problem continues.

 

[twgonder]

Split sooner rather than later, especially if you intend the database to be a multi-user database. One point is that some things work differently in a local table, compared with a linked table. For instance you can't use "seek" on an indexed table, and it's better code to be developed in the way it will be used after release.

Chinese characters (is that what you mean) generally indicate some sort of corruption. This can be caused by data loss when working with split databases, especially hem users work with a wireless connection. ...

You now also have an easy development environment,

...

The characters weren't "chinese" but they were letters with weird didactics. So far, everything has been in one accdb file on one laptop. When I split, I want to start some basic testing with multi-user (two laptops to one BE), and the home has WIFI. The WIFI bogs down as other download apps and videos. Is this going to be a problem for Access?!!!

When you say "easy" development environment, what exactly are you referring to if I may ask?

@ June7 Several people have "advised" me against having two instances of Access open on the same laptop. They suggested it might be the source of some of my corruptions. How do you open the BE for editing while the FE stays open too?

 

[theDBguy]

When I split, I want to start some basic testing with multi-user (two laptops to one BE), and the home has WIFI. The WIFI bogs down as other download apps and videos. Is this going to be a problem for Access?!!!

Besides the advice of not using a Cloud storage (OneDrive, DropBox, etc.) to store the BE file, this is the other one. Do not use an Access database application (connected to an Access file BE) over a WiFi connection.

 

[gemma-the-husky]

If you connect 2 databases to the same backend, you need them both to establish a "persistent" connection, otherwise Access keeps having to negotiate a connection to the data every time, which can take a couple of seconds every time. This is most likely the cause of the delay. Access is multi user out of the box, and having multiple connections shouldn't cause an issue of itself, especially when you are just testing.

Just create a dummy table in the backend, add it as a linked table, and then open it, and keep it open until you close the database. You need something like that when you first open the database. I actually open a "dummy" recordset, as it's not visible. You can open and hide a form. Because you then have a connection, Access/Windows don't have to keep negotiating a new connection.

Research "persistent connection"

 

[gemma-the-husky]

EASY DEVELOPMENT

You have 2 sets of databases
LIVE DATA
Production Code Database (CP) --------> production Data (DP)

TEST DATA
Development Code Database (CD) --------> development Data (DD)

You have the above set up. CP and CD are the same to start initially.
DP and DD have the same structure, but not necessarily the same data.
You can take a copy of DP and use as a new DD. but you can't go the other way.

so to make changes you can edit database D, while testing it against DD
It doesn't matter what you do to either of them.
once you get CD working as you want, you replace CP with the new CD.
If you change the tables and fields in DD, you need to make the same changes in DP at some time, before you can safely use the new CD code.

You keep copies of old versions of CD
CD1
CD2
CD3
CD4 etc, so you can deal with any issues, and revert to an old version if necessary.

You back up the real production data DP regularly.

So that's why you split the data base as otherwise this becomes very difficult.
This way CD and DD can be offsite. The new CD can be issued to hundreds of users of your system, all of whom have their own private data.

 

[twgonder]

EASY DEVELOPMENT

You have 2 sets of databases
LIVE DATA
Production Code Database (CP) --------> production Data (DP)

TEST DATA
Development Code Database (CD) --------> development Data (DD)
...

So that's why you split the data base as otherwise this becomes very difficult.
This way CD and DD can be offsite. The new CD can be issued to hundreds of users of your system, all of whom have their own private data.

That sounds familiar to what we did, in theory, on a large mainframe that was a service bureau. Except we had dozens of DPs and a CD and DD for each programmer. I've got a video on that if anyone is interested.

The big problem was when changes are made to the structure of the DP. Fields have to be renamed or added or deleted, etc. New tables are added. Data has to be modified.

Has Access natively made tracking and implementing these kinds of changes to the DPs easy for the developer?
Have you seen any good tools for managing these changes if not native to Access?
I imagine it will be much worse when we have hundreds and not dozens of customers without a quality change tracking tool for the DPs.

 

[Pat Hartman]

Unlike the others, even when my BE is destined for SQL Server, I don't split until the schema is pretty solid. But, on the other hand, I actually do analysis and design before I start. It's a bad habit from my mainframe days. So, there are almost always changes later on but they tend to be a field here and a field there. Sometimes a new table because the user thought of new functionality he wants. I Almost never have object name changes. And relationship changes are even more rare. Again, probably from years of discipline with how I name things.

As long as all the tables are local, you can rely on Name Auto Correct (or Corrupt if you don't understand how the feature works) to make many kinds of changes. But the "feature" doesn't propagate all changes so you still have to change code manually and fields involved in calculations in queries. I'm not sure about form and report properties but you may also have to change them yourself. I'll attach two documents that I recommend you read if you are going to leave Name Auto Correct on. Once I'm done with the initial design, I turn it off to prevent accidents. Also, it is almost no use once the tables have been moved to a separate database.

The most important thing to understand about NAC is that it does not propagate changes immediately and that's how you get into trouble. SOOOOOO, Keep track of what you changed. Then before you exit the app, make sure you open every object you think will be affected by your change. If you have 2 forms and 5 queries and 1 report that use tblCustomer, you need to open all 8 objects in design view and then save them to ensure that your change got propagated. If you don't know what got affected, the picture below points to a feature that might help you. I also turn on the third option and if NAC is on, I ALWAYS log the changes so I can review what changes Access made for me.

There are find and replace addins. If you use one of them, be careful and take backups before making global changes. You'll thank me for this advice if you do something stupid. And if you use an addin, be sure to turn OFF NAC. You won't need it and it is best to not have it on just to be on.

Name Auto Correct does support one feature so I end up turning it back on occasionally.

 

[gemma-the-husky]

Stepwise refinement. I think the key is to really understand the data, and design the normalised table structure well in the first place. Then you hopefully don't need to change tables and field names. You might need to add some more, as @Pat Hartman just mentioned, but your basic structure ought to be pretty good. If it is the development of the database functionality ought to be more harmonious.

 

[twgonder]

Stepwise refinement. I think the key is to really understand the data, and design the normalised table structure well in the first place. Then you hopefully don't need to change tables and field names. You might need to add some more, as @Pat Hartman just mentioned, but your basic structure ought to be pretty good. If it is the development of the database functionality ought to be more harmonious.

I'm blessed that I don't now have thirty programmers, like before, all coming up with new ideas on a daily basis, and then backing out half of those "great ideas" that didn't work. Design by chaos. I'm trying to be disciplined in design, I just don't have 5 years to get up to speed on Access as was required in the old days.

 

[Pat Hartman]

If you still know how to design, you know how to design. Access isn't the problem. If you rush and do things out of order, you will be in a constant rework cycle. You don't save any time by building objects before you have tested the sanity of your schema with queries. One of my early managers had an embroidered wall hanging made by his wife. It said:

"If you don't have time to do it right, what makes you think you have time to do it twice?"

Good advice that has served me well for lots of years.

PS, you allowed the "Design by chaos". I never let my 30 programmers run the asylum because that is what it would have become. There is a fine line between thwarting their creativity and letting it ruin your projects. Change control is your friend on a large project. Luckily, most of my Access projects don't rise to the size or cost of my mainframe projects. I also don't have to progress through eight staging levels as I did with one of my clients who was part of the military industrial complex But, I still have three. Unit Test, System Test, Production. Those are the minimum. It's hard to get Access developers to even use two, let alone three. It's a lack of discipline. We all love instant gratification and the optimism gene runs exceptionally strong in programmers. I used to have one programmer who knew with absolute certainty that if her program compiled, it would work perfectly. Behind her back, the rest of the team called her Julie No-Test. I've just been burnt so many times that I've just learned to spend more time thinking than doing in the early stages of a project. Once I start delivering versions to the users for testing, I start a change control process so I don't loose sight of errors or change requests.

 

[NauticalGent]

"If you don't have time to do it right, what makes you think you have time to do it twice?"

My Chief used to tell me: "Clark, either TAKE time to do it right, or MAKE time to do it over...!" I hated that man, but he was right...

 

[twgonder]

The message on my screen for the programmers said, "If you don't have time to do it right, then when will you find time to fix it?"
It's truly a fine line. There's a learning curve for all, and even then, most development platforms add new features that need to be retrofitted into existing apps for better functionality or performance. One can leave hooks, but not fully anticipate all that are coming down the pipe.

For example, I started on another forum with a question about a "helper table" (it holds lots of little data, mostly descriptions, for lots of little data points) and the forum went nuts. "Silly idea, it violates the rules of normalization, a nightmare for programming, blah, blah." But I'd been using the basic concept for over 40 years. The question was how would Access handle it in a practical way with control objects that are new to my old designs, like combo boxes. Attached is the answer that went through several iterations over the past month (as I worked on lots of uses of it). If I was a genius, maybe I could have designed it this way right out of the gate with my limited experience in Access? I think not.

The attached image shows only six of the twenty-one fields in the table.

I have one menu program in my old db that went through twenty years of refinement. It's silly to think I "should" have designed it, in its entirety, "the right" way on day one. https://1drv.ms/u/s!AmMsCrz5d72TgZJvY4KrEoy1JvCByg?e=axSkXY

Now, back to the original question. Lots of different opinions. Who can say which is correct? Based on some testing and ideas from post #15, I'm delaying the split during development. Sure, I'll split and test along the way, but the day-to-day design and implementation will remain in the Di (see attachment). From my evolving standards document (which is now in its 40th year and being modified for Access), we find this attached entry, similar to the ideas in post #13 and demonstrated in the above video link (which one Access expert" person, in another thread, said could never work in Access or any other database system).