UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Split Database Without Admin Privileges, Access 2010    
 
   
dwilson
post Nov 28 2017, 11:37 AM
Post#1



Posts: 43
Joined: 10-October 17



It's clear that db stability is at its best and the risk for corruption at it lowest when the db is split and each user has a front end version on his or her desktop. However, in my environment, that's not possible for several reasons:

1) I do not have administrative privileges on our huge and highly restricted network
2) Local user policies do not allow anything to be added to the users' desktops or local computers except by an administrator, and they have unequivocally nixed putting individual FEs in either location
3) I have been allowed just a single network folder in which to store my db, including the front and back ends. I don't even have control over who has access to this folder: whenever I want a new user to have access to the db, my IT people have to give that user permission to reach the folder.
4) I have no budget with which to buy any db "management" tools that would automate updating front ends.
5) I can't convert the FE to an accde because IT will not allow me to mark the folder safe; if I do switch to an accde, then every user who opens it gets panicky modal warnings about malicious code every time they want to use the db.

So, at the moment--and I know this will cause shivers--all of my users share a single front end. It's been that way for 10 years, with only occasional problems, but recent changes in the network (over which, again, I have zero control) have slowed things so much that we are starting to have crashes. And because everyone has the same FE, every crash involves every user and requires me to run around and use the task manager on every computer to close things down so I can compact and repair.

Please don't tell me how bad this is, because I already know.

Here are my questions:
1) would it make sense for me to create a separate subfolder for each user and put a separate copy of the FE there? This would address the shared FE problem but the individual FEs would still be out on the network rather than local. I could probably (though not definitely) get a shortcut for each user that would send him or her to the correct subfolder.
2) If I did that, I would then be stuck managing some 25 copies of the FE without any automation. One thing that would help me would be to have each FE compact on close. Would that be a good idea or a bad one? I don't have that feature active on the shared FE because of the risk of corruption, but I'm willing to risk it if such corruption would be limited to one FE at a time. I would continue do the backend compaction manually.

Any other thoughts would be helpful, but please don't suggest that I try to change the operating environment, because that's an absolute nonstarter.
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 12:02 PM
Post#2


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



I'd definitely create a subfolder to house individual user FE copies. Even if it is on the network, it is still a step in the right direction.

Have you checked to see if you have access to the %appdata% local folder, this is quite often unrestricted and this would enable you to deploy the db locally. Just a thought.


In your scenario, I'd go to management and present things like http://www.devhut.net/2017/04/09/setting-u...ccess-database/ and listing problems that have occurred in the past. It is up to management to tell IT what they need, not the other way around. IT is there to support users, not dictate (with the exception of security - which this is not). This is a Management issue!


Normally compact on close is ill viewed. The real question is why do you need to compact this frequently? Why is your db bloating in such a manner?

QUOTE
I would then be stuck managing some 25 copies of the FE without any automation

Managing in what manner? You could create a bat file, vbscript to automatically compact, deploy, ... the FEs, etc.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
theDBguy
post Nov 28 2017, 12:07 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi,

As an alternative, how about the following approach?

1. Yes, create a separate subfolder for each user. For simplicity's sake, I would recommend naming the subfolders the same as the users' network login or usernames.
2. Place the FE on the root folder.
3. Create a batch file on the root folder to copy the FE to the subfolder matching the current user and then launches the copy.

As for your environmental reasons:

1. Admin rights are not required or necessary to manage a split Access database. You just need read/write/delete permissions.
2. No comment.
3. This is fine since it looks like you're saying you can at least create subfolders in it and also create other types of files. Correct?
4. There's plenty of "no cost" tools available in the Code Archive and other websites you could try using.
5. I'm not sure I follow this reason. When the user opens the ACCDB, how come they don't get the same warnings if the folder is not marked safe?

Just my 2 cents...

Edit: PS, looks like Daniel already mentioned some of the same things I mentioned above. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dwilson
post Nov 28 2017, 01:05 PM
Post#4



Posts: 43
Joined: 10-October 17



Daniel: As far as IT and who they report to--it's definitely not me. The administration does not want me using this db, which they see as a security issue and a maintenance burden (even though I do all of the maintenance). They would prefer that we use a commercially available product, but they won't buy one and the existing ones aren't adequate in any case.

DBGuy: yes, the warnings do occur the very first time one logs in using an accdb, but there is an option there to accept the folder as safe. If the user agrees, every subsequent use is free of any messages. With an accde, there is no opportunity to have the end user indicate that the folder is safe, so the message comes up every time. Also, it's a much more aggressive and scary message. I don't want to train people to just click past such warnings, even though it might make my lifer easier.

My concerns about management have to do with creating updates. If the FEs are all in a series of subfolders I can reach, then it's not too big a deal. However, if I somehow did manage to get them into user's personal files, I don't know how I would address changes. I know there are code samples that might help, but that's a level of effort that I don't want to make unless my original idea of subfolders on the server is unworkable.

Why does the FE bloat? I wish I knew. I saw a post suggesting that this can happen if "data is written back to the FE," but I'm not sure exactly what that means. Does it refer to unbound fields that store data while the FE is open?
Go to the top of the page
 
theDBguy
post Nov 28 2017, 01:12 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for the clarification. I can't even remember why you mentioned converting to ACCDE. What happens if you compiled the ACCDB to ACCDE and then rename the ACCDE to ACCDB? Do you still get the "scary" warnings? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 01:39 PM
Post#6


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



Look over https://www.devhut.net/2017/04/09/setting-u...ccess-database/ it should offer some idea on how you can easily manage multiple FE and deploying updates.

If the db contains sensitive infos, then the security concern can be valid, in which case IT should be able to provide you with an SQL Server instance that you could use to upsize your current db into. The Express version is FREE if they don't already have it (most companies do). Something to contemplate perhaps. You use SQL Server as your BE and continue to use Access as the FE.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
dwilson
post Nov 28 2017, 03:16 PM
Post#7



Posts: 43
Joined: 10-October 17



Daniel: As I said, IT will not allow ANY changes. Nothing. Period. No SQL server, no desktop links, No nothing. They won't even discuss it. They want this db gone and are only allowing it to exist because I don't ask for anything. I do have access to my own appdata folder, but only mine. Would that help me with my issue?

DBGuy: the idea behind an accde was that 1) it would not need to be compacted, and 2) it would not dump a user into VBA in the event of an unhandled error. Unfortunately, the warnings come up even if I compile to an accde and rename it to accdb. And the warning, as I said before, strikes fear into the users' hearts (see attached).

Attached File  warning.png ( 11.89K )Number of downloads: 0
Go to the top of the page
 
theDBguy
post Nov 28 2017, 03:17 PM
Post#8


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just FYI... If you meant to attach something, I don't see it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dwilson
post Nov 28 2017, 03:42 PM
Post#9



Posts: 43
Joined: 10-October 17



edited and image added
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 03:49 PM
Post#10


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



If you have access to your appdata, then you could create a script to deploy the FE to each user's appdata so they each get their own local copy (best setup).

So you create a script and place it on the server, and give each user a shortcut to the script.

The script simply copies the server master copie of the FE to their respective appdata folder and launches it. You can even get the script to setup the trusted location so they never see any messages about activating the content.

This is ideal, as they always get a fresh copy of the db. If you make any updates, you simply update the server copy and your users get the update the next time they launch the db. No more work for you!

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
theDBguy
post Nov 28 2017, 03:50 PM
Post#11


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Okay, thanks. Just an idea, try creating a small ACCDB with just one button to open a ACCDE file on the network folder and see if you will still get the warning.

By "open," I mean try using the OpenCurrentDatabase method.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dwilson
post Nov 28 2017, 04:29 PM
Post#12



Posts: 43
Joined: 10-October 17



Daniel: What sort of script are you talking about? Something in VBA or within the db itself? I can't get to a cmd prompt so it can't be a DOS-type batch file. Any code you can point me toward?

DBGuy: I can try what you've suggested, but I don't know how it would work any differently than what I'm doing to generate the message above, which is to navigate to the folder on the server where the accde file sits and open it manually. I don't have to pull it into a local drive to get the error.
Go to the top of the page
 
theDBguy
post Nov 28 2017, 04:33 PM
Post#13


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I was just curious, and I'm not sure we can say for certain it doesn't make any difference unless somebody tries it.

My idea was to avoid opening the ACCDE manually and was wondering if opening it via code will bypass the warnings.

Again, when I say "open" I am not talking about using Shell or a Hyperlink or even ShellExecute. I am specifically referring to using the OpenCurrentDatabase method.

Anyway, it's just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dwilson
post Nov 28 2017, 06:10 PM
Post#14



Posts: 43
Joined: 10-October 17



No joy using the OpenCurrentDatabase method. Same warning message...
Go to the top of the page
 
theDBguy
post Nov 28 2017, 06:17 PM
Post#15


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Okay, thanks for trying. It's good to know.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
dwilson
post Nov 28 2017, 07:30 PM
Post#16



Posts: 43
Joined: 10-October 17



Daniel: I followed your earlier link and found code there. Thanks.

So the premise is that I would get the FE file into the appdata folder. Now the question is which subfolder (or would it be the main appdata folder)? There are some 15 computers in the area and people frequently grab whichever one is closest to the spot where they are working at the moment. If a user follows the plan and puts a copy of the FE into his or her appdata folder on a specific computer, I assume that he or she would ONLY be able to access it on that one machine, correct?
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 08:00 PM
Post#17


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



The script will load the FE onto whatever computer they are logged into. It makes no difference to the script.

Yes, you should use the script to create an application folder within the appdata and copy the FE there (just to keep things tidy and manageable).

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
dwilson
post Nov 29 2017, 12:12 AM
Post#18



Posts: 43
Joined: 10-October 17



I didn't ask my question very clearly.

If a person uses two different computers and happens to be on computer A the day I send out the link that installs the FE in the appdata folder, then the FE will go onto computer A. Am I correct in my assumption that, if the user now goes to computer B, the FE will not be there unless the user again uses the link to install the FE on this second computer? In other words, won't each user need to separately install the FE on each computer that he or she uses?

Or, on the other hand, will the install on computer A be available to whomever happens to log onto computer A, and this original user have access to an FE that had previously been installed on computer B by another user?
Go to the top of the page
 
DanielPineault
post Nov 29 2017, 08:26 AM
Post#19


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



Each user, on each computer, will need a shortcut to the launching script.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
dwilson
post Nov 29 2017, 10:18 AM
Post#20



Posts: 43
Joined: 10-October 17



If whomever happens to be logged in on a given day can use the same single copy of the FE as the prior and next day's users, then I only need to as many copies of the front end as there are computers. If not, then the number of copies is equal to the number of users times the number of computers. At my site, that means either about 15 copies or about 300 (15 computers, 20 users). Given the challenges that I face as a stand-alone, unfunded, one-man maintenance crew (whose actual job has nothing to do with IT), 300 copies is too many to be responsible for. And I suspect that my IT group would go nuts if they found out I was doing it. Also, it seems to me that the way this would play out is that each time a user sat down at a new computer, he or she would have to go back through his or her emails to find the link so that his or her own personal FE could be loaded on that machine. Over time, this would not be an issue because every user would presumably get to every computer, but at first it would cause a great deal of frustration.

If that's what this means, then I'll just create a subfolder for each user on the server and put his or her FE copy there. It may not be ideal, but I can't see another way.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:37 PM