Question Backup.accdb & lost data

[LaurieW]

I have many databases on our network in Access 2007 that are used by multiple users. Most are fine, but one much-used database keeps making [databasename]_Backup.accdb files. This has been happening for sometime now. Today though there is ALOT of lost data in the database. The new database created by Access is 6meg in size and the backup.accdb file is 9meg. There are notes connected to records and many, many of the note records are now gone in the [databasename].accdb file. What is going on with this Access process that makes the backup file and how can I prevent it from occurring? The entire database (front/back ends) are in Access 2007.

 

[Pat Hartman]

Sounds like an unsplit database. This is a disaster waiting to happen. Access is most stable when the users each have their own local copy of the FE (forms/reports/code/queries) and they all link to a shared BE (tables only) on the server. With this configuration, the BE is open the minimum amount of time and if one user has a problem he won't lock the BE and is very unlikely to corrupt it. When everyone opens the same monolithic database, if one PC locks up while the app is open, everyone will be impacted and the database may be corrupted and loose data.

The Backup files are made when the database is improperly shut down. It is Access' method of self preservation but it is not always successful.

You will probably need to go back to yesterday's back up and reinput today's transactions. But, the first thing you should do is to split the app.

Updated to fix typo.

 

[LaurieW]

Thanks for the explanation. I am considering moving the data back end to SQL and keeping the front end in Access. Do you think this would stabilize the database?

 

[jdraw]

Note:

In Pat's post

....the users each have their own local copy of the BE...

, it should read

....the users each have their own local copy of the FE...

.

I don't think that SQL Server is the immediate solution, however depending on volumes and number of users that may be an option down the road.

Basic questions from Pat's post:

With these multi-user databases:
Are the databases split into FrontEnd/BackEnd?
Does each user have their own copy of the FrontEnd on his/her PC?

 

[LaurieW]

jdraw, I'm unsure if you're responding to me or Pat. My current database is not split at all. If I move to SQL, the BE would in SQL, FE in Access 2007. And I don't really want to put the FE on every PC this is used on. This database is used by multiple users (50+) in different locations around our city, it would be a nightmare to have the FE on all PCs.

 

[jdraw]

I was just trying to clarify the point made by Pat re BE/FE, and highlighting the questions she asked. I haven't used multi user Access with SQL Server BE so can not comment on the need for separate FE on each PC --- but that's how it works with Access FE and Access BE.

I did see an article (somewhat dated) re Access FE and SQL server BE by Armen Stein from JStreet. It's a powerpoint presentation that may be helpful.
The link is http://www.jstreettech.com/files/Best-of-Both-Worlds.zip

However, I suggest you send a PM to Pat who has lots of experience and can advise you specifically on Access FE and SQL Server BE.


Here's a link that suggests the FE goes on each PC even with SQL Server
http://www.hitechcoach.com/index.php?option=com_content&view=article&id=35
Good luck.

 

[Pat Hartman]

@jdraw - Thanks for fixing my typo.
@Laurie - Switching to SQL Server will require application changes if you haven't designed the app with client/server techniques in mind. Upsizing a database that uses forms bound to tables or queries with domain functions among other things will result in slower performance once the conversion is completed rather than faster. The immediate problem is the monolithic database. You should split it as soon as possible. There are several tools to help distribute updated FE's so having 50 users isn't a problem.

There are two reasons to upsize to SQL Server.
1. More concurrent users than Jet/ACE can support
2. More data than Jet/ACE can support
What you have told us so far doesn't indicate either problem

 

[LaurieW]

Pat, do you have more information on the tools to distribute FEs? Never having done this before (and having written many, many databases that do not have problems) I am clueless.

And also could you elaborate on these two comments:
There are two reasons to upsize to SQL Server.
1. More concurrent users than Jet/ACE can support
2. More data than Jet/ACE can support

How many users is Jet/ACE able to support and what are the data limits?

Thanks so much for your insights, your help is appreciate.
Laurie

 

[LaurieW]

We are going to try to implement the front end/back end, all in Access. In doing this, where is the best place on a Win7 PC to put the front end database file? Our "My Documents" folder is remapped to a network drive, so that is out. Should it go in C:\Users\[username]\... somewhere or should I just create my own directory on C:?

 

[CJ_London]

I'll just butt in if I may - I usually put it in the localapp directory for each user with a shortcut on the desktop.

This bit of code will tell you where it is and what it is called - it can vary from one windows OS to another - but is within the users directory

Set wshell = CreateObject("WScript.Shell")
msgbox wshell.expandenvironmentstrings("%LOCALAPP%") 
Set wshell = Nothing

The other useful directory to know is the local settings, application data which is where typically you might put temporary data or info unique to the user - such as preferences - since you probably don't have access to the registry

Set wshell = CreateObject("WScript.Shell")
msgbox wshell.expandenvironmentstrings("%LOCALAPPDATA%") 
Set wshell = Nothing

 

[LaurieW]

Thanks for butting in! I appreciate everyone's assistance, it is most helpful.

One more question ... I lock down my Access databases, turning off the Ribbon and making my own "blank" ribbon for forms and "report" ribbon for reports. When I split the database, now the ribbon is again showing up even though the Usysribbons table has my ribbons and the forms/reports are also still using my ribbons. I don't get it. I can't leave the Ribbon available to my users, they'll do bad things. Any ideas on that?

 

[CJ_London]

If you have created a new db for the front end, this will be the case, you'll need to hide them again

 

[LaurieW]

Thanks, I just figured out the problem with the Ribbon showing again. The database splitter had the USysRibbons table in the back end. I unlinked that and turned it on in the front end and now it works like a charm, my ribbons are being used.

 

[Pat Hartman]

Here's a link to one -- http://www.autofeupdater.com/
There are others. Just search for them.

1. Technically the concurrent user count limit for Jet/ACE is 255. The practical limit is closer to 50 but you may find the limit is higher or lower. it really depends on how data intensive your application is.
2. Jet/ACE can support tables with millions of rows. As you might expect, the more data your queries pull, the sooner, you might benefit from upsizing. The catch is - upsizing ONLY helps if you actually restrict the data your queries request. If your query requests 10,000 rows, it will actually be SLOWER with SQL Server than with Jet/ACE. You need to modify your app so that instead of using filters or allowing the user to scroll through thousands of records, your queries retrieve the specific record a user is looking for. Running 100 queries that each retrieve 1 row at a time over the course of a day will be more efficient than retrieving 10,000 rows and trying to work with all that data locally. It's a mindset thing and you may have to change your mindset to take advantage of SQL Server's ability to deliver small packets of data very quickly.

 

[LaurieW]

Thanks again!