10 Super Easy Ways to Prevent MS Access Database Corruption

by guest blogger Max Jordan

From Scottgem: This is an article submitted to me by Max Jordan, self described as a passionate learner of technology. Besides working as an MS Access DBA, he loves exploring his knowledge on MS Access and sharing tech blogs.

Specifically, in the case of IT industry “The pain of the severe loss is quite unimaginable for those who had never suffered this situation. It kills several instances because its anguish can no longer be attempted again.” Similar is the situation if Microsoft Access database gets corrupted due to any reason. This is why system experts recommend users to take preventive measures, regularly. The question arises of how to prevent Microsoft Access database corruption? So, here in this article we are going to suggest 10 ways to prevent Access database corruption.

• Split MDB or ACCDB Files: It should be a practice of an user to split the database in two parts i.e., the front-end and the back-end. The front-end database should contain forms, reports, queries and code modules. The back-end contains just the tables with the user’s. The front-end is installed on the local drive of each machine. The back end is stored on a shared network drive. This separation of Access files proves itself beneficial when the application is to be used by multiple clients. This technique will make the database accessible more quickly and enable users to enter/edit data in front-end. And possibilities of MS Access database corruption issues decreases as back-end is secure. 

• Utilize Decent Network Cards: The unbranded motherboards are made up of plastic and silicon wafers. These unbranded materials lead to cause corruption in files stored on the hard drive by intermittent loss of network connections. Therefore, we suggest sticking with good quality branded network cards. It is suitable to match manufactures of all NICs with networking elements. If possible then, using same brand as the switches and hubs to inhibit corruption in MS Access databases or other applications. 

• Keep Updated with Latest Drivers: Not only device drives but, programs should also be updated with their recent editions. It is quite obvious that a system manufacture pays less attention on the device drivers. Till today’s research, 80% of crashes are caused due to lack of proper maintenance of device components. It should be the responsibility of each user (or the corporate IT department) to keep drivers updated for fixing bugs and problems. 

• Change the Suspected Network Element: If an individual stretches the cables tightly then, wires might get bent or disconnected because snagless material is used in them. Users are advised to use switches instead of hubs for networking. A switched network is a topology that benefits users with several advantages. The major advantage of using a switch is that the network speed increases by using collision domains concept. This concept includes breakdown of the LAN leading to occurrence of fewer collision. It means that fewer packets will be transferred over the network through cables. 

• Avoid MAC and Windows on Same Network: There is a good chance that availability of both Windows and Mac operating system users on one network leads to problem. Mac applications create large amount of traffic over the closed loop. The traffic rapidly increases when transferring of graphic files or their printing takes place. To speed up the working in a closed network, users are recommended to keep Mac and Windows users separate. As much possible, it will be by far better to keep database traffic different from graphics traffic. Permit the administrative workstations for connecting to the server along with a shared database through local switches.
• Regularly Utilize Compact and Repair Applications: Until and unless, users utilize the Compact & Repair feature, the Access file does not get smaller in size. Even though deletion of data is taking place, it will have no affect on overall file size. Keep one thing in mind, the larger the file, is the greater the target of packet loss. Therefore, set ‘Compact on Close’ feature on the database file, which is provided in Options menu. Compact the file regularly, if you are splitting data onto a separate back-end. Apart from all this, to prevent Access database corruption clients can also utilize visual basic code to set file compacting after certain time duration. 

• Defragment the Network Hard Drives: It is known that the Access database files are often big. The size increases with regular use of this file. This causes hand in hand problem, with hard drives. A disk with low or full storage space leads to fragmentation more rapidly. This will be having less than 25% of free space on drive. Therefore, clients should defragment their system drives to prevent any disaster. 

• Try to Avoid Leaving the Database Opened: When an Access database is opened the backup products create back up of the data. This operation starts executing on itself leading to chances of the MS Access database file corruption. This is the reason why it is suggested to keep the application properly closed when they are not in use. 

• Terminate the Connection When Not in Use: We hope that being a responsible technician, users might do this preventive measure regularly. This protective tip is important when users connect to ADO or DAO within an Access database. It is just one click to close connection but, without it the database will get corrupted twice or thrice.

• Be Attentive While Using Wireless Networks: A simple cable network might work fine and it is also safe to use. But, using WiFi, multiple clients could suddenly cut off the connections. This will lead to MS Access Database corruption when someone is writing to it simultaneously. When users are reading database then, this sudden interference is not a big issue but, in case of writing, it is! So, try to avoid using Wifi when not in need. 

Observational Verdict

10 ways to prevent Access database corruption are described with details in this article. Not only for Access but, these preventive measures should also be used for other programs. If a person keeps these tips in his / her mind then, there will be less chances of corruption in Access database or in any other database file.

 

Microsoft expands availability of Access

Microsoft announced yesterday (11/4) that Access will now be included in Office 365 Business and Business Premium packages. Office Blog In doing so, Microsoft is recognizing the importance of capturing and analyzing data to businesses. They are promoting Access as a viable tool for any business to deal with the value of the data they collect in the course of doing business.

Along with this announcement, MSFT also announced new data connections for Access. The ability to combine data from multiple sources has always been one of Access’ biggest strengths and it will now get stronger.

However, in doing this MSFT is setting up a tiered structure for Access. The ability to create applications that link to these new sources will only be in the E3, E5 and Pro Plus versions. But all versions will be able to use apps created in the higher versions.

This is all part of exciting news coming from the Access development team. We can expect more news in the coming months as Access continues to be developed.

Also recently announced is the opening of Power Apps. Power Apps can help you build WEB and Mobile apps fairly easily. MSFT sees this as new way to capture and present data. They are worth checking out.

The Forgotten

I’m going to depart from my usual Access oriented blogging to talk about a subject very close to me. As the 15^th Anniversary of 9/11 has approached I am, again, annoyed and chagrined at how one group of those affected is paid short shrift in the media coverage of the event. I refer to the ordinary people lucky enough to have survived the horrors and devastation of that day. You see I am one of that group.

I don’t want to take anything away from those who lost their lives in the Towers and especially not to the First Responders who bravely rushed in to do what they could after the planes hit the Towers. Or even those who lost loved ones on that day. But there are many of us who were lucky to have gotten out of the Towers and away from the collapse. Yet you hear little about those. I was inspired to write this by a column that appears in today’s (9/10) LI Newsday by Mike Vogel. Mr Vogel wrote about a 12 year old girl, Helaina Hovitz, who was evacuated from Intermediate School 89, near the World Trade Center. The column wrote how she begged the parents of a classmate to take her with them. How she and her classmates became sensitive to overhead planes and car backfires. How she struggled with post traumatic stress disorder through adolescence and young adulthood.  To her credit she emerged from this to become a journalist and author (After 9/11: One Girl’s Journey Through Darkness to a New Beginning).

My story is nowhere near as dramatic. I was in my office on the 50^th floor of the South Tower that morning. I heard the crash of the first plane and looked out my window to see smoke coming out of the North Tower and debris raining down. Our corporate security people came running through the office telling everyone to get out. I was in a stairwell on the 16^th floor when the second plane hit. The building shook and the lights went out briefly. When we reached the ground level the plaza looked like a war zone, flaming debris everywhere. The police ushered us down to the Concourse level telling us to head north.

From here my luck was tremendous. I managed to catch one of the last subway trains heading north and got to Penn Station in time to catch one of the last LIRR trains leaving Penn Station. I was home before noon unscathed. I don’t seem to have suffered any adverse affects of that day either physically or mentally. Though I was a bit jumpy about planes flying overhead for a little while afterwards.

I write this in the hope that people will remember that not all the victims of 9/11 were those who died in the attacks and those who survived them or those affected by the rescue efforts. But there is a third group that needs to be remembered.

I belong to a group that represents this group. The World Trade Center Survivor’s Network can be found at http://www.survivorsnet.org/. Please help remember those who survived on that day.

Access is Alive and Kicking

I am happy to report that is real evidence that Access is considered a viable product by Microsoft and that resources are being devoted to improving and enhancing Access. The first piece of evidence is that Access has been added to Official Office 365 Roadmap site. This site is about planned updates for Office 365 subscribers. For the first time Access is now included in the list of apps. Check out this site https://fasttrack.microsoft.com/roadmap, Click on the Filters tab and there is Access.

The second piece of evidence is that the development team listened to the feedback given at access.uservoice.com. And support for dbf files is being restored to Access 2016. This information is detailed in the Acess section of the Office Blogs found here: https://blogs.office.com/2016/09/07/back-by-popular-demand-dbase-file-support-in-access/.

There appears to be exciting times on the horizon for Access fans. Enhancement are being looked at for the desktop version that will extend is scope and reach. I hope to have more reports for you in the not too distant future. Keep tuned in.

What is Access?

As one can see, my blog concentrates on Microsoft Access. As a long time database developer who has worked on designing database applications on several platforms with several products (starting with dBase III, working through FoxPro on finally concentrating on Access) I have the knowledge and experience to give advice on Access development.

It occurred to me that I have never actually defined what Access is. As I start this I’m attending Microsoft’s annual MVP (Most Valuable Professional) Summit. This is chance for MVPs to gather, interact with their Product Groups and other MVPs. So we’ve been talking about Access a lot over the last few days. It occurred to me (though not for the first time) that Access is a much misunderstood product. So I decided to blog about what Access actually is.

Most people think of Access as a database. To do so shortchanges Access because it is much more. Sure a database is part of Access, but only a part. I like to describe Access as an Application Development platform. In that, Access is not much different from Visual Studio or other, comparable, development environments. Access comprises several components that all can be used to develop an application that people can use.

The first is a Forms Designer. Access has a Forms Designer that allows the developer to create powerful interactive forms that enter/edit data, view data, process data, do work flows and more. With the Forms Design Wizard, even a novice can create a nice looking form to enter or view data. With the use of process controls like command buttons, subforms, split forms and data controls like combo boxes, list boxes, option groups and text boxes, one can easily create powerful, interactive forms that can work with data or not.

The second component is a Report Writer. Access has one of the best Report Writers in the business. Many people use Access almost solely to create reports on data. The Report Writer is a banded tool that allows very powerful grouping, summarizing and analysis of data. As with the Forms Wizard, the Report Wizard, makes it easy for even the novice to create nice looking yet powerful reports that can be printed (both hardcopy and electronic) or even interacted with. Just the Report Writer alone is almost worth the price of admission.

The next component I want to discuss is the Query Designer. Generally used in conjunction with other components, the Query Designer provides a graphical interface to build SQL statements that can be saved and run directly or used in code modules. I will admit that the SQL generated through Query Design mode is not the best (too many parentheses, etc.) but it is workable. It also provides a way to learn SQL by seeing the code generated through the graphical interface. Queries are integral to getting data out of a relational database. At lot of activities in Access revolve around queries that pull the relational data together.

Another component to discuss is automation. Access contains two ways to provide automation for processing. First, let me state that automation is a more advanced feature of Access. People can build viable applications without using automation at all. But, with automation, the developer can provide end users with a full-fledged application that can does not need any knowledge of Access on the part of the user. But the macro language

Automation comes in two flavors; macros and VBA. The macro language was greatly improved in 2010. Prior to 2010, the macro language was rudimentary, not very powerful, and, in my humble opinion, not worth using. Since 2010, you have a language that is more powerful, more flexible and is usable in lots of situations. In addition, if you are designing a WEB App, your only choice for automation is macros. The other flavor is VBA. Visual Basic for Applications is a superset of the Visual Basic language. In addition to all the VB commands there are objects, methods, properties and classes specific to the application you are working in. With all that, VBA is a powerful language. There is very little that can’t be done in VBA. Many have extended Access greatly using custom VBA functions and procedures.

Finally, Access comes with a database component. This component has gone through lots of changes over the various versions. Previously called the Jet Engine it is now referred to as ACE (originally Access Connectivity Engine, now Access Database Engine). ACE is the native database store for Access. ACE is the weakest link in the family of components that comes with Access. But one of the strengths of Access is that it can connect to a variety of data stores. An Access application can connect to a variety of data stores. Anything from the freely distributable SQL Express, through text files, Excel or anything with an ODBC connection like SQL Server, Oracle, DB2 and more. And you can combine these connections to pull data from a variety of sources.

So Access was designed to work with relational databases. It was also designed to work in a multi-user environment. This makes Access very scalable in a variety of uses.

Because Access is a platform for developing applications it can be used on a variety of levels. The single end user can use it to develop simple applications for their personal use. The “knowledge worker” can “develop” multi-user applications to share data among a small workgroup. The experienced developer can create applications that can be used on a variety of levels in small to medium size businesses.

It has to be remembered that Access is not simply a database. If one looks on Access as a database one is missing the full breadth of features and capabilities that Access brings to the table. As an experienced Access Developer I am proud of the applications I’ve developed and how they have helped people with their jobs and hobbies.

I’ll close with a bit of a shameless plug. I’ve spent a significant portion of my life in helping people use Access to its fullest extent. I’ve done this as a volunteer on several Web forums like answers.microsoft.com, utteraccess.com, and more. I’ve done it through this blog. I’ve done it as teacher in a variety of venues (most recently for the Continuing Ed department of Hofstra University). I’ve also done this as a paid consultant for a variety of clients. So if I can help you make the most of Access, feel free to contact me through the various venues mentioned.

ICANN, Legalized blackmail?

The Internet Corporation for Assigned Names and Numbers (ICANN) is an international organization charged with maintaining the Domain Name System (DNS). If you are not aware, when you type a Web address into a browser there is are servers on the Internet that match up the domain to an IP address. The browser then connects to the computer that IP address is assigned to. The Web server on that computer then delivers web page back to your browser which renders the page into what you actually see,

ICANN was created to manage the Web addresses and maintain the servers that hold the databases on these DNS servers that translate a web address into an IP address. Originally web addresses fell into one of several top level domains (TLDs). These were:

COM: Commercial

ORG: Organization

NET: Network

GOV: Government

EDU: Education

MIL: Military

These stayed as the only TLDs for a long time. But people started asking for more. New ones like .BIZ and .INFO were added. Some of these have made sense. But what doesn’t make sense is not qualifying applicants for these domains. For example. One of the new TLDs called Generic Top Level Domains (gTLD) is .actor. But there is no verification that an applicant for such a domain is an actor.

But the real problem here is that corporations and individuals are now trying to protect their brand by registering these domains to protect their names. It recently hit the news that Taylor Swift registered TaylorSwift.porn and TaylorSwift.adult to prevent someone else from registering those domains. Similarly Microsoft registered office.porn and office.adult. They were offered the chance to do so before the June 1 opening period when it becomes first come, first served in registering such domains.

To me that smacks of legalized blackmail. Are all these domains really necessary? Why should individuals or organizations with recognizable names and trademarks even need to pay money to protect their names? Why shouldn’t there be a vetting process to prove that a) an applicant is entitled to use the names and b) their site fits with the gTLD.

See: http://www.foxnews.com/tech/2015/03/23/taylor-swift-porn-sites-not-if-swift-has-anything-to-do-with-it/

In Memoriam

Paul Louis Diamond

10-12-1943 to 8-21-2014

My brother died today after a long illness. He died the way he lived his life, on his own terms. He fought the deterioration of his body with every ounce of his waning strength.

I loved and respected my brother. Though we often butted heads and didn’t see eye to eye on a lot of things, he was my big brother. Growing up he was always protective of me (he was the only one allowed to rag me) but he was always there for me. Now he won’t be!

He was a very strong willed person with strong beliefs, sometimes to the point of pigheadedness. As such, he was sometimes hard to take. But his beliefs came from careful consideration of his interpretation of the facts. And he was an intelligent person, who could make most arguments sound logical.

We were brought up by our parents to be generous and giving. Paul very much adhered to those principles. This is evidenced even in death as he has had his body donated to a medical school to see if they can determine why he lived many years longer than his doctors thought he would. As a lifelong smoker, battling diabetes and obesity, it is somewhat surprising that he is cancer free and survived as long as he did.

My brother was something of a character. My mother taught us the value of a vocabulary and Paul took it to extremes. He would never use a match to light up, but rather an incendiary primer. One of our family stories is about embarrassing my girlfriend (who married me despite this). We were at a picnic and my mother had forgotten the catsup. Linda asked how we could eat hamburgers without catsup. Paul’s reply was you put it in your mouth and you masticate. Linda, being vocabularily challenged mistook the word. I have a lot of stories about his sense of humor and idiosyncrasies that represent fond memories.

Paul never had children of his own, but took a great deal of interest in my daughter and was her favorite uncle. He was great with children and beloved by many young nieces and nephews. He is survived by myself and my daughter and his wife, Colleen. Colleen deserves special mention here as she has been a rock in dealing with Paul’s health issues, moods and taking excellent care of him. I don’t think I can ever adequately express my appreciation for what this has meant to us.

I can’t imagine what Paul has had to go through with the deterioration of his body. I am amazed at how much he has maintained his good humor throughout, though I haven’t been in contact with him on a daily basis. Hopefully, he can now rest from the fight.

I know that there are several people who will miss him as I will. I will remember him for what he was during his life, not what he was during his final battle to hang on to life. For the joie de vivre that he had, for the generosity that he showed, for just being a unique personality. The picture below, of Paul and Colleen, is how I will remember him.

R.I.P my big brother

Family and friends gathered 8/23 At Paul and Coleen’s home for a balloon send off: Facebook Status

Memorial Balloon Takeoff

Comments are welcome,

Securing Back Ends

Ask any knowledgeable Access developer and they will tell you any Multi-User application or any application supported by someone other than the user NEEDS to be split into a back end (containing the tables only) and a front end (containing everything else). This is essential for stability and development needs. In most cases the back end is then stored on a shared network folder.

The users then need all but Full Control to that folder. This is because Access creates a locking file (ldb/laccdb) whenever any Access file is opened. So the user needs to have the rights to create this file, modify it and delete it.

But there is a way to hide this folder, though it’s not 100%. This article shows you how to do this through an extended permission called Traverse folder.

Basically what you need to do is create a folder to act as a container for your back end folders. Within this folder you create another folder for your back end files. You can either use a single folder or individual ones. My company has create a single folder so that’s what I will illustrate.
We have a shared network drive mapped as S:\ for all users. Within that share we have a folder I’ll call Miscellaneous for this example. Within that folder I have another folder called Back_ends where I store the back end files. It looks like this to a Domain Admin account:

However, to a Domain User account the Miscellaneous folder will not appear. This is because only Traverse rights to the Miscellaneous folder have been granted to Domain Users. Following shows the permissions assigned to Domain Users:

To set Traverse access, you would first uncheck all but the Write permission under the Allow column. Next you press the Advanced button to set Special Permissions. Select Domain Users under the Permissions tab and press the Change Permissions button:

Select to Edit permissions and set permissions as shown below:

You may not need the create and write permissions, but it shouldn’t hurt. The key is the Traverse Folder permission.
Once you do that, Domain Users will not be able to get to the Back_ends folder unless they know the exact name of the Traverse folder. So they can Type in S:\Miscellaneous\Back_ends and see that folder. As I said it’s not 100% but it will inhibit the average user.

Audit Trail using Data Macros

There are different ways to do audit trails in Access. Up until Access 2010, these had to be implemented on the Form level. But Access 2010 has Data macros which operate on the Table level, so they will capture if someone bypasses your security (see my blog on Login Security using VBA) and makes changes in the table itself.
Setting up such an AuditTrail is a little more cumbersome but does provide a greater level of protection.
The first step is creating a table to hold the Audit Trail information. The following shows the design of this table:

The fields are pretty obvious. This table records the table and field that was changed, the RecordID (PK Value) of the changed record, the old and new value and who changed the record and when.
The next step is to open the table you want to audit in Design Mode. From the Table Design ribbon pull down the list of Data Macro events and select After Update event as shown below:

This will open the Macro Design environment where you can build the macro.
The screen shot below shows the macro commands to cover one field:

The first command is an IF command that is triggered if the field value changes. It uses the Updated function with the field name as the argument. The next step is the Create a Record command which uses the argument of tblAuditTrail. This will be the name of your Audit Trail table.

From there you add a SetField command for each field in your Audit Trail table (except for the autonumber PK, which is filled in automatically). The first field is the Tablename which is hardcoded. The next is the RecordID which is filled in by a reference to the fieldname. Then the ChangeBy field is populated with the ID of the user. In this example I am using the fOSUsername() function found at http://www.mvps.org/access (see Note). Next, I populate the FieldName, which is again hardcoded. Following are the Old and New values. I use the Old command with the fieldname as the property to capture the previous value. Then reference the tablename.fieldname. Finally, I set the ChangeDate to Now().
To place an Audit Trail on other fields, you can collapse the If and then copy and paste to add another set. Change the fieldname information as needed.
Save the macro and close the editor.
You can test by making changes to the fields you have place an audit trail on and then checking tblAuditTrail to make sure the changes were recorded.
You can also capture Deletes similarly using the After Delete event and writing all the fields to a deleted records table. Again using Create A Record and SetField for each field.
I have a sample file (see link below) that contains a table with Audit Trail and the Audit trail table as well as a form to edit the audit trail table and modules with the needed functions. Note: Both Front end and back end need to be in the same folder.

Note: If you use my Login Security techniques or some other way of capturing the current user, you may need to create your own function to replace fOSUsername(). Following is an example:

Public Function fUser()
fUser = Forms!frmLogin!cboUser
End Function

You can assign to fUser whatever you use to capture the current logged in user.

Limitation: This does not work with certain data types. An error 20342 is thrown with description The Updated function is not supported for memo, rich text, hyperlink, OLE Object, multi-value, or attachment fields. If you have one of those data types, then I would recommend using a VBA alternative (see Comments). In the case of multi-value or attachment data types, you can replace then with a child table.

Audit Trail Sample

Access 2010 – Calculated Data Type

One of the long held principles of data base design has been to not store calculated values. In the past it has always made sense not to do this for a couple of reasons. Foremost is that, you have to ensure that you recalculate the value when the components of the calculation change. Second, when storage space was at a premium, it didn’t make sense to store a value that could be displayed with an expression in a control or a column in a query.

In today’s technology, storage space is not much of an issue. With storage space costing around $100 per Terabyte, developers do not have to scrimp to save space. So Microsoft provides for a new data type in Access 2010 that allows the user to store an expression that displays a value. This satisfies the primary reason against storing calculations.

With the Calculated data type, you store a formula or expression that is updated automatically when the components of the expression change. So there is no need for the developer to ensure that the value is updated. It is handled automatically by Access. Let us look at how the Calculated field data type works.

To illustrate Calculated fields I’m using the Order Details table in the Northwind Traders sample data base. I’m going to add a field to calculate the extended price and another to display a discounted price.  First open the table in Table Design mode. At the first blank line enter ExtendedPrice as the fieldname. And select Calculated as the data type (1).

Once you select Calculated as the data type, the expression builder opens  for you to build the expression to store in the field.

The expression builder opens with the table you are working in highlighted (1).  In the middle pane is a list of fields in that table (note: calculated fields can only use fields in the same table within its expression). So the first step is to select the first field in your expression. In this instance we will select the Quantity field.

The next step is to enter an operator to perform the calculation. First select Operators, from the Expression Elements (1). Then select Arithmetic from the Expression Categories (2). Finally, double click the asterisk (multiplication operator) from the Expression Values (3). The result is to add an asterisk to the expression (4).

You then go back to the Expression Elements and select the table again. Then double click the Unit Price field to add it to your expression.

Finally press OK and the expression will be added to the properties of the field (1).

Switch to Datasheet view of the table, saving the table if prompted. You will now see the Extended Price field added to the end of the table (1).

Notice that it is correctly calculating the extended price at 1400 (100*14). To test the field change the Quantity to 10 and you will see the field recalculated to 140 (1).

Similarly, you can add a discounted price by using the expression: [ExtendedPrice]*(1-[Discount]).

I must admit I am a bit ambivalent about this new Calculated data type. On the one hand, it certainly short cuts doing calculations. By putting the calculation in the table, you do not have to repeat the calculation wherever it is needed. Just add the field to your form, report or query and it is done for you.  Another advantage is compatibility with Sharepoint lists. But it will present problems, like some of the new data types introduced in Access 2007 (multi-value fields and Attachment fields) if you need to migrate your database to SQL Server, Oracle or the like. And it is an unnecessary shortcut. But, for the non developer, for the user making a database on their own for their own use, it does provide an advantage.