2007 Shared vs Split Database

[MartyO]

I have always split the database, frontends and backend, when several users
need to access it and update it.
In 2007, I see under Access Options, Advanced Option, Shared vs. Exclusive.
What does this really mean? Do I not need to split the database now?
I'm having issues with Reports becoming corrupt in a couple of databases
where there are multiple users, and the db's are set to Shared, they are not
split. Could this be causing the problem?
Sometimes the reports kick out to the Microsoft "Send" Don't Send" message
when running the report, and sometimes they kick out to that when modifying
in design mode and clicking on save. I keep having to recreate the reports
from scratch to take care of the problem. Compacting and Repairing does not
clean up the issue.
Should these db's be split?

I'm also in the process of converting these db's to 2007 from 2003. Is it
possible that using the .mdb files with 2007 could be causing the report
corruption?

Thanks in advance for your response!
Marty

 

[MartyO]

I've just noticed on one of the reports in question, that the error was
triggered when I change a control in the detail section from Text Box to
Label then click on Save. I've made numerous other changes now on that report
that I thought was corrupt and now the error is not being triggered.
Is there a known bug with the "Change To" menu option for controls on a
report?

Thanks!

 

[Tom Wickerath]

Hi Marty,

I have always split the database, frontends and backend, when several users
need to access it and update it.

Good. In fact, you should split the application any time more than one user
needs simultaneous access. And, each user should be running the FE
application file from their local hard drive (so that no two users can ever
be running the same copy of the FE). Simply splitting, but sharing a common
FE really doesn't accomplish much.

In 2007, I see under Access Options, Advanced Option, Shared vs. Exclusive.
What does this really mean?

Shared vs. exclusive has been an option available in past versions of Access
as well, at least back to Access 97. I'm not sure about Access 95 and
earlier. Exclusive means that only one user can open the database for RW at a
time. This setting only affects the FE application in a split design. And, if
a copy of the FE is on each user's local hard drive, as it should be, then
this setting really doesn't matter one way or another.

One needs to open a database in exclusive mode in order to make any design
changes, or to compact the database. So, as a DBA, you will need to open the
common shared BE in exclusive mode to be able to make any design changes. (I
think that Access automatically promotes a shared opening to exclusive, if
this is needed and if it is able to do so--in other words, if no one else has
the file open.)

Do I not need to split the database now? Yes.

I'm having issues with Reports becoming corrupt in a couple of databases
where there are multiple users, and the db's are set to Shared, they are not
split. Could this be causing the problem?

Could be. It's hard to say with certainty. Are all users on a LAN (Local
Area Network)? If your LAN network is compromised, or if any of your users
are opening via a WAN (Wide Area Network), then they very well could be
causing corruption. Here are two very good sites with additional information:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ (Tony Toews)
http://www.granite.ab.ca/access/corruptmdbs.htm

Sometimes the reports kick out to the Microsoft "Send" Don't Send" message
when running the report, and sometimes they kick out to that when modifying
in design mode and clicking on save.

Have you tried using the undocumented /decompile switch on a *copy* of your
database (ie. back it up first before doing so)? If you have just one version
of Access installed, you can click on Start > Run, and enter the command:

msaccess /decompile

If you have more than one version of Access installed, then create a
shortcut where the target includes the full path to msaccess.exe (surrounded
by quotes) followed by a space and then the /decompile switch.

Then navigate to your database and open it with the shift key pressed down
the entire time it is opening (to prevent any startup code from running). Do
a compact and repair, and then re-compile your code. This very well may take
care of the problems. If it does not, then it is time to create a new blank
database, and import all objects from your source database. I give more
detailed instructions on the bottom half of page 3, of a Word document I call
"Access Links". You are welcome to download a zipped copy from my web site.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tom Wickerath]

You need to install service pack 2 for Office 2007.
Make sure all of your uses, no matter which version of Access that they are
using, also have the latest service packs installed. More information here:

http://allenbrowne.com/Access2007.html

See the section sub-titled: "What's broken (new bugs)"

The problem you describe is shown in this listing as the 24th entry.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[MartyO]

Thanks soooo much for your responses.

I do give the users their own frontend, but they are located on a server in
a folder for each user. I give them a shortcut that opens their frontend. It
makes it easier for me to give them a new frontend. That way I don't have to
go their building to deploy a new frontend on their local drive. I guess I
should rethink that.

Thank you for the link to your site... I'll read up.

Marty

 

[Peter Hibbs]

Marty,

To update the FE file with a new version you could try the Front End
Updater Utility at :-

http://www.rogersaccesslibrary.com/...a527ac41-22f9-a4fe741f-1677841b-89fzzb27.html

You would just email the client a new version of the front-end file
(zipped, of course) and tell them to unzip it into the appropriate
folder on their Server PC. The documentation provided explains how to
set it up.

If you should occasionally want to make changes to fields and/or
tables in the back-end file then have a look at the code in the Back
End Update Utility on the same site.

You may also need to re-link the front end to the back end at the
user's site automatically, the Back End Re-Linker code at this site
can do that.

HTH

Peter Hibbs.

 

[MartyO]

Wow! Awesome... I'll check it out.
Thanks for the info!