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

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).
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually, the two clients I still have do in fact install there commercial apps in Program Files (XP).

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"there " >> their.
And I mean the two clients that do sell commercial apps.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Jeffrey CoachmanMIS LiasonCommented:
MX,

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

Jeff
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, maybe at home if ... I have time, But that is a LOT to absorb.

mx
0
 
rberkeConsultantAuthor Commented:
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
 
rberkeConsultantAuthor Commented:
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
 
rberkeConsultantAuthor Commented:
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
 
rberkeConsultantAuthor Commented:
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
 
rberkeConsultantAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
rberkeConsultantAuthor Commented:
<<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
 
rberkeConsultantAuthor Commented:
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
 
rberkeConsultantAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.