Solved

NEVER put FrontEnd.mdb file into c:\program files\ if UAC is turned on in Windows 7 ????

Posted on 2010-09-21
17
1,119 Views
Last Modified: 2012-05-10
The following long winded post explains why I believe that people should NEVER put front end Access databases into c:\program files\

My main reason for posting is to ask if any expert disagrees with my belief.

The second reason for my post is to document the fairly painful process that led to my final resolution. There were two older EE questions which now reference this summary of this final resolution.
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/Windows_7/Q_26473654.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26486755.html


--------------So, here are the details ----------------------------------

For several years, my SBS 2003 network had a daily login script that copied \\server\FrontEnd.mdb to clientComputer C:\program files\myFrontEnds\*.*

Under XP this worked fine. Under Windows 7 with UAC on, it seemed to work (except for a minor problem which was solved in Q_26473654)

So, I was happily thinking that everything was working fine when I discovered that changes to vba module mdl99 in \\server\FrontEnd.mdb were not working on clientComputer.

While trouble shooting, I opened C:\program files\myFrontEnds\FrontEnd.mdb and found that it did not contain the mdl99 changes.

But, I then copy C:\program files\myFrontEnds\FrontEnd.mdb to c:\newFolder\FrontEnd.mdb, and suddenly the MDB did contain the mdl99 changes !!!! THAT IS WEIRD.

If then copied the MDB back to c:\program files\myFrontEnds\  and the mdl99 changes disappear again !!!! THAT IS WEIRDER.

A little more research revealed that double clicking on any mdb under c:\program files\ does not open the selected MDB. Instead, it opens a much older copy of the mdb which is stored in "C:\Users\rberke\AppData\Local\VirtualStore\Program Files\ myFrontEnds\FrontEnd.mdb"

I decided that Windows 7 simply does not want people to put there own programs into c:\program files\.

I now put my MDBs into c:\MyFrontEnds\ and things seem to work fine.

But, in an earlier question, another expert told me this was a bad -- here is what he said.
----------- from q_26486755
Root drives are protected in Vista and Win7, but of course you can change permissions/restrictions as needed.

Proper application deployment techniques in Vista forward suggest that you install Programs to the Program Files section (this is a WRITE ONLY section) and that you install Data files to one of the Data folders (depending on how much access is needed, and by whom). If your FE needs to write to local tables, or you store data directly in the FE, then you'll need to move it to the Data folders section.

You can store your apps on the root drive, but don't be surprised when a Win7 update comes out and breaks this. UAC is not going away, and it's only going to get more restrictive. The root drive was NEVER intended as a storage location for user-run programs. That's what Program Files are for (or the Data folders, depending).
0
Comment
Question by:rberke
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 165 total points
ID: 33728314
Most Access developers do not put database files in C:\program files anyway.

Most Devs leave this for "True" "Installable" applications:
(MS Office, PhotoShop, Quicken, AV software, ...et al)
MS Access "database files" (though called "Applications", are just "Files" (for lack of a simpler explanation))
...meaning that they typicality don't require instalation programs, or modify registry or system settings or require special drivers, ...etc)

A developer may put the "Runtime" version of Access in Program Files, thought...
But not necessarily the database files themselves.
Those might be put in a separate folder on the network:
ex: K:\MSAccessDB\BE_Files\

But this is an interesting observation nonetheless.

;-)

JeffCoachman
0
 
LVL 75
ID: 33728374
Actually, the two clients I still have do in fact install there commercial apps in Program Files (XP).

mx
0
 
LVL 75
ID: 33728421
"there " >> their.
And I mean the two clients that do sell commercial apps.

mx
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 165 total points
ID: 33728442
I tend to use:

C:\Users\firstName.lastname\AppData\Roaming

as the default location for my applications.  But I don't push updates to them, they pull.  Most of my applications check the server to see if a newer version is available, and if so, download that version to the specified location, which I get using the SpecialFolders API call.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 170 total points
ID: 33728528
When we go to Win 7 (from XP), my database loader will definitely be loading FE's into the equivalent of

C:\Documents and Settings\<LoggedInUser>

which I guess is C:\Users\ ... area.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33728533
MX,

I don't have Win7.
If you do,  can you verified/replicate the askers observations...

Jeff
0
 
LVL 75
ID: 33728620
Well, maybe at home if ... I have time, But that is a LOT to absorb.

mx
0
 
LVL 5

Author Comment

by:rberke
ID: 33730063
replication is hard because I cannot figure out how I orginally got the very old version of the program into virtualStore.

But, the following sequence can simulate the problem

1. Create testVer1.mdb and testVer2.mdb on your desktop.  Create mdlVer1 in the first and mdlVer2 in the second.
2. create test1.bat and test2.bat on your desktop and change them to refer to your user profile.
3. run test1.bat as administrator. It  will open Access and a Command Window
4. open c:\Users\yourName\AppData\Local\VirtualStore\Program Files\testFolder and verify that it contains test.ldb locking file.  If it test.ldb is not visible, close Access and rerun test.bat. repeat until test.ldb is visible.

5. with the test.ldb file visible, and Access still open, run test2.bat as administrator
When you are done you can verify my observations as follows
6. Double click on C:\program files\testfolder\test.mdb.  It will open a database that shows mdlVer1.
7. Close test.mdb the drag it from testfolder to your desktop.  Open it again and it will show mdlVer2.
8. drag it back to  ..\testfolder  and it will show mdlVer1 again. !!!


Test1.Bat ------------------------------
md "c:\program files\testFolder\"

copy "C:\Users\bob.berke\Desktop\testVer1.mdb" "c:\program files\testFolder\test.mdb" /y

"C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Office\Microsoft Office Access 2003.lnk" "c:\program files\testFolder\test.mdb"

Echo PLEASE OPEN C:\Users\bob.berke\AppData\Local\VirtualStore\Program Files\testFolder

test2.bat ----------------------------------
md "c:\program files\testFolder\"

Echo HIT ENTER THEN PLEASE OPEN   C:\Users\bob.berke\AppData\Local\VirtualStore\Program Files\testFolder

copy "C:\Users\bob.berke\Desktop\testVer1.mdb" "C:\Users\bob.berke\AppData\Local\VirtualStore\Program Files\testFolder\test.mdb"

ECHO PLEASE CLOSE ACCESS
PAUSE
copy "C:\Users\bob.berke\Desktop\testVer2.mdb" "c:\program files\testFolder\test.mdb" /y

Echo FILES HAVE NOW BEEN SET UP TO DEMONSTRATE PROBLEM

pause
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Author Comment

by:rberke
ID: 33730098
Step 5 simulates the problem that occured in the distant past where an old version got into virtualStore.

I wish I knew how it really happened, but I have not idea.  In the future, I am treating this as type of "corruption" that can be easily fixed by deleting  fixed by deleting  C:\Users\bob.berke\AppData\Local\VirtualStore\Program Files then rebooting.

But, my feeling is that this corruption will not occur as long as I keep my Front Ends some place other the c:\Program Files\
0
 
LVL 5

Author Comment

by:rberke
ID: 33730110
oops, the first two lines of Test2.bat were unneeded, so you can ignore them.  The following is the correct test2.bat

test2.bat ----------------------------------

copy "C:\Users\bob.berke\Desktop\testVer1.mdb" "C:\Users\bob.berke\AppData\Local\VirtualStore\Program Files\testFolder\test.mdb"

ECHO PLEASE CLOSE ACCESS NOW
PAUSE
copy "C:\Users\bob.berke\Desktop\testVer2.mdb" "c:\program files\testFolder\test.mdb" /y

Echo FILES HAVE NOW BEEN SET UP TO DEMONSTRATE PROBLEM

pause
0
 
LVL 5

Author Comment

by:rberke
ID: 33745753
I will close this problem since nobody seems to believe that frontend.mdb belongs in Program Files.

a final example of how VERY VERY DANGEROUS Program Files can be for front ends:

* When I first replaced my laptop with a new windows 7 computer, it had UAC turn on.  
* In the first few weeks, I ran c:\program files\frontend.mdb regularly, so UAC created a copy in VirtualStore.
* Due to several unrelated problems, I temporarily turned off UAC on 6/1/2010.

* For two months the 6/1/2010 frontend.mdb stayed in virtualStore like a ticking time bomb.
* I continued my normal development practice of making changes to myCPU c:\program files\ and manually copying them to \\server\CompanyPrograms
* The daily login script would then then copy server version to all clients c:\program files.

* in 9/1/2010 I turned UAC back on, so the 6/1/2010 version in VirtualStore program became operative. When I double clicked on the new version in C:\Program Files, the old version in virtualStore was being opened, so 3 months of program changes were being ignored with no warning message at all.

* As a result, several data base updates did not work properly and my backend data base became logically inconsistent !!
I got lucky because I noticed this in time to avoid any serious problems, but it could have been much worse.

I don't blame microsoft for these "features" of UAC, because there are very tough design issues involved.  

Nonethelss,  PEOPLE HAVE TO BE VERY CAREFUL WITH UAC -- IT CAN CAUSE SERIOUS PROBLEMS.
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 33745872
Look like the EE developers are now insisting on an additiona comment even though I don't have any.

Probably another bug in their problem close program.  
0
 
LVL 75
ID: 33746308
"since nobody seems to believe that frontend.mdb belongs in Program Files."

As I noted, two clients who sell commercial Access apps for years use Program Files, and have *never* had an issue.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33746385
Yeah, I never said "Nobody".
I said most.
;-)


I still would like to know if MX could replicate the issue in Win 7 though...
0
 
LVL 5

Author Comment

by:rberke
ID: 33746639
<<As I noted, two clients who sell commercial Access apps for years use Program Files, and have *never* had an issue>>

Actually, I also used Program Files for 9 years without problems.  It was intentional and I would have defended the decision as being the most natural location, and a clear best choice.  But, along comes UAC and now everthing is different.

You might want to point your developer friend to my 9/23 12:13 post.

As long as the commercial apps are XP only things will be fine.  But windows 7 with UAC might give them some subtle problems that might not show up for a while.  

By the way, I have used 9/26 6:13 post to replicate the problem on another Windows 7 computer, so it is not just my machine.  But, if MX wants to put the effort in to demonstrating it himself, that would be nice, but it is not too important because I am perfectly happy with putting my front end databases into c:\myFrontEnds.   %userprofile%\appData or \documents might also work, but they make me a little nervous.
0
 
LVL 5

Author Comment

by:rberke
ID: 33746702
By the way, an earlier suggestion of putting the front ends onto a shared server folder is a bad idea in multi user environments.  MS Access is prone to corruption several users open a front end at the same time.  Also, front ends will usually perform better when they are on the local hard drive.

0
 
LVL 5

Author Comment

by:rberke
ID: 33746755
fyed said: "Most of my applications check the server to see if a newer version is available, and if so, download that version to the specified location, which I get using the SpecialFolders API call."

It is my bet that if your SpecialFolders downloads to CSIDL_PROGRAM_FILES you will find the same problems that I have documented in this post.  
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

One of the features I've come to appreciate about Windows 7 and Windows Server 2008 R2 is the ability to pin applications to the task bar. As useful a feature as I've found this, it does have some quirks.  For example, have you ever tried pinning an…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now