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.

 

[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.

 

[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.

 

[LaurieW]

Thanks again!