Question Prevent creation of new db's

[Rip]

Hello,

New to the forum so forgive me if I have posted this in the wrong place…..

I am in desperate need of a way to prevent creation of any new Access db’s on our network. We have way too many individual db’s out there that people have created and now expect us (IT) to support. As you can imagine, it is a true nightmare. So, we would like to prevent users from creating any new ones while also allowing them to continue to use the existing ones. Uninstalling Access and installing the runtime does not work because of its limited functionality.

Long story short, is there a way to keep the full version of Access installed on the machines but take away the ability to create any new db’s? We are currently on Access 2003.

Many thanks..

 

[spikepl]

LOL - you are asking how to prevent people to do what they find necessary. because they cannot get it from the IT-people? A very common nightmare indeed (and I have been at both ends of this game).

To be honest, in my opinion this is a management issue, concerning the obligations of the IT-department as perceived by the two parties, versus the needs of the business. How is a declaration of war, such as you are proposing, going to resolve this problem? Strangle the creative despoilers of database-peace?

I know the above is not what you asked for, but there is a world outside IT.

 

[Rip]

Yes, spike, I am well aware of the existence of the world outside of IT. Not everyone in IT is as close minded or as simple minded as you seem to portray (you say you have experienced both sides of this “game”?). We are a small shop and things have grown but, due to circumstances beyond our control, the IT staff has not grown along with it. So, you are preaching to the choir as far as your helpful advice. If it was up to me, we would provide the necessary support above and beyond what is required but, alas, it is not. None the less, I have to work with what I am given and find a way to make things work as best as I can.

All that aside, any helpful suggestions would be greatly appreciated and would allow me to provide the best support I can for the people who do the actual work in this company in spite of management.

 

[spikepl]

Normal means to deal with your problem are budgets and cost-codes. Those who feel they need support for their own creations could then put their money where their mouth is. I have been in environments where the whole IT-infrastucture was locked down tighter than (you don't wanna know), and that was oftentimes a pain - punish the few to prevent the folly of many.

I still believe that your problem is not of technical but of organizational nature. Fighting fire with fire just leaves ashes. But I do not know your business. I just have a bunch of experience of trench warfare between IT-departments and the rest, and without exception the entire business suffered each time.

 

[VilaRestal]

Perhaps users aren't fluent enough with Access to be able to create workable databases.

Access is, essentially, a high-level programming language. How many organisation would give all staff an installation of Visual Studio, let them write their own programs in that and then expect the IT department to support all the problems that would ensue?

If you don't want people making databases in Access that don't install Access on their computers. The runtime version is freely available. It would save license fees as well support costs.

 

[NigelShaw]

Hi

A good IT would prevent this on the server side. A good IT would probably get the users to use a runtime version of access and stop the full access being opened by creating a group?

Maybe I'm wrong

N

 

[Rip]

Ok, we have removed Access on the new PC’s we are deploying and installed the runtime but, the problem is, the runtime has limited functionality (no export function for example) so, until we can develop something to replace what they are currently using, the users have to be able to use the full version. Many of the db’s don’t have front ends so they are pretty much working with raw data (yeah, I know but you would have to understand how we got to were we are today to see why we are in the boat we are in now….another long story...)

So bottom line, is there a registry setting or something short of waving a magic wand over each PC, that we can do to prevent the creation of any new db’s while preserving the functionality of the full version? At this time, my….well…superiors, are holding up much needed hardware upgrades for these people because they refuse to allow the full version to be installed but, will not commit the resources needed to remedy the situation. If I can show them that we can install the full version but not allow any new db’s to be created, I can help these people out. Again we are on 2003.

Thanks guys.

 

[Xproterg]

I'm sure there's a tweak for this; however, it sounds like you're military, and so I'll state that I do not support restrictions that are this one-sided. Quite frankly, IT shops that run a heavy hand are just lame. Let people create what they want to create, and set up a policy that databases cannot be the responsibility of the IT shop. That way, you can be the assholes that you want to be without hindering the creative minds out there that want to automate boring processes.

 

[Beetle]

Rip:

Many of the db’s don’t have front ends so they are pretty much working with raw data (yeah, I know but you would have to understand how we got to were we are today to see why we are in the boat we are in now….another long story...

)

So bottom line, is there a registry setting or something short of waving a magic wand over each PC, that we can do to prevent the creation of any new db’s while preserving the functionality of the full version?

Sounds like you've got a mess on your hands there. I don't believe there is any way you could give the users the full version while preventing them from creating any new databases. In order to use the existing databases they need to have read/write/delete permissions on the folder, which means they will be able to create new db's as well. I agree with Spike that this is more of an organizational issue. The best you may be able to do from an IT standpoint (if you're going to leave the full version on the users PCs) would be to tell them that the network will scanned for .mdb files perioically and any files created after x date will be deleted, but that would come with a whole new set of headaches, not to mention increasing the tension between IT and the other departments. Again, as Spike said, this would ultimately have a negative effect on the business as a whole.

XProterg:

Let people create what they want to create, and set up a policy that databases cannot be the responsibility of the IT shop. That way, you can be the assholes that you want to be without hindering the creative minds out there that want to automate boring processes.

Is there something in this thread that gives you the idea that the OP, or their department, are intentinally trying to be a**holes? It sounds to me like, up to this point anyway, they have tried to give the other departments the flexibility to create Access applications on their own, but they've got users who have little or no database experience (evidenced by the fact that they are working directly in the tables with no user interface). Eventually, users like that will get in over their heads and, despite any stated policy that it's not the IT departments problem, it may still end up in their lap when some manager decides to put it there. It sounds to me like that is more the case here. The suits have told them to "fix it", but don't want to resource the tools necessary to do so.

 

[Lightwave]

Cut the power to the computers and give them abacuses...

They cannot possibly be responsible for their own actions.

No one except IT staff are allowed to be imaginative.

War Is Peace
Freedom Is Slavery
Ignorance Is Strength

and take personal out of PC

 

[boblarson]

It is a problem, especially in a large organization, where users will create their own databases and then in the course of time they become "mission critical." We had that problem when I worked for a large healthcare company. In fact, we had a manager in the warehouse who was good at creating his own databases but with no knowledge of best practices, so when I got there it was a pain to have to try to help them when they had a problem. I did my best to try to hint at some best practices but essentially I had to just let it go.

My coworker was also aghast when I came back up one time and told her that I told them to fix the problem they needed to put the frontend on EACH user's machine instead of them trying to have 100 users trying to use the same file on the server (which unsurprisingly kept corrupting). She said, "we can't be supporting this frontend and keep deploying to 100 users." So I told her we didn't have to and so that is when I built my Frontend Auto Updating Tool which I have on my website (for free). That allowed the department admin who was tasked with making the changes that the warehouse manager, or other managers, wanted and deploying the database to just make the changes, change the version number and then it automatically did the update the next time the user opened their frontend.

Anyway, I can understand the frustration and desire to reign it in. It isn't good to have bad databases become "mission critical." And it is inevitable that the person who created it leaves at some point and then who takes it over? Oh, we'll have IT do it they say. That's where it really needs to be clear communication between IT management and the rest of management that these cannot be supported by IT. If they want something to be supported by IT then it should go through the correct design process, etc. And maybe they will need to pay to have it re-done to proper standards. But that is an organizational level decision. It is something they have to work out together. And it has to be assisted by management that is even higher than the department heads so that a good compromise can be reached.

Anyway, that's just my take on it from past experience.

 

[spikepl]

Maybe you are already doing it, but if not, here is one suggestion:

One tool you could use in this fight is money. Because this is, ultimately, the one thing that the suits understand. While you are looking for other solutions, keep scrupulous accounts of the expenditure on the cottage-industry apps, in terms of manhours or money. To that, add the estimated opportunity costs and perhaps also direct expenses incurred by the business due to postponement of other work.

 

[gemma-the-husky]

and possibly also appreciate that the guys who are building databases are providing something that really is needed in the business. I don;t expect you get involved in controlling spreadsheets - databases aren;t so very different.

perhaps you could agree which ones are the most important, and support the developement of those.

Not do it yourselves, but agree the table structure and system architecture.

 

[Rip]

Finally, someone who sounds like they have some real world experience. Bob, you have hit the nail on the head. It sounds like you experienced pretty much exactly what we are going though. We have many db’s out there that have been developed by people that initially were developed just to fill a gap and have now become mission critical. The people that developed them have either moved on to other positions and have left their co-workers hanging and take the stance that it is not their problem or, have left the company. Our staff is very small and overloaded as it is so, we really don’t have the resources to cope with all the individual support request. The plan is to allow the continuned use of the existing db's while software is developed in house to encompass many of them into one. We just can't let people continue to put new db's out there or we will never solve the problem. The reasons we have ended up in this boat are the fault of poor management but it does not change the fact that I still have to do the best I can with what I have.

I am assuming there is not a solution but, I want to thank those that took the time to respond in a …well….rational way. I do believe that there are several on here who believe that they are much more experienced than they really are…some of the responses are actually laughable. It is obvious from the responses that some posters have not had to support a larger sized company and don’t understand how complex it can be and how hard it is to meet everyone’s needs while keeping the company running as smooth as possible for the good of all.

Again, thanks to Bob, Beetle and the others who can at least understand the situation.

 

[tonylpcs]

Hi,
Firstly since joining this forum may i say there have been some very helpful people on here who have gone out of there way and spent their own time helping me and other strangers and I can’t express how grateful i am to them for their help, so a big thank you to those people.
However I have noticed that some people are more interested in giving an opinion or criticise your question than answer it for you!
You asked a fairly simple question and explained why you needed this solution instead of any other, and what do you get? You get people coming on and having ago at you for asking!
I know how frustrating it is when you ask a question on here and someone posts a reply but does not even attempt to answer your question and the only reason i can think they do this is because they don’t know the answer!
Otherwise someone would have started with "Sorry but No there is no way of restricting this" or "Yes you do it this way!" before having a go at you for asking.
Anyway, what does it matter to other users of this forum the reasons for your needing a solution to a problem?
Are they saying I'm only willing to help you if I like your reasons behind doing this?
Come on people get a life! Either answer the questions asked or stay out of it!
Maybe some people have too much time on their hands and not enough knowledge or skill to help those of us that could benefit from it!
anyway just my view but this is the 5th search I’ve done today where the question was exactly what I needed an answer to, but the replies had nothing to do with a solution, so you can understand how frustrating it gets reading pages and pages of text with nothing constructive on it.
P.S. I've also noticed it seems to be the same names coming up with the unhelpful messages all the time but they never seem to be able to help anyone with solutions! Funny that!
Anyway to answer your question as I now know, There is no way to stop this happening! Sorry.

Tony

 

[boblarson]

The thing to remember Tony is that, while there are those who aren't all that much help, the members who truly help out here on the forum shouldn't answer the questions blindly. We ask questions and sometimes question the why someone is doing it the way they are doing (or asking to do) because we want to help them do something that uses best practices and teaches them at the same time.

A lot of time, people will come and have in mind a certain idea of how they are going to accomplish what they want to do. But that idea may be a very BAD idea and we like to spare them (and those who might have to follow after them in supporting the database) from uneccessary pain and suffering.

I kind of liken it to someone coming to you and renting a boat. They mention that they aren't the type to wear a life jacket. So you may still rent the boat to them but you might give them a strong suggestion to wear a life jacket. In the end, it is their decision. But you at least tried to get them to be safe (or in the case of Access - do it in a good way).

 

[tonylpcs]

Hi Bob,
You have made a very good point and I was not intending to discard all the extremely useful advice and the help that you experts are willing to provide to us novices with, after all without your help we would be stuck on sometime even the simplest of tasks. I am amazed and grateful to everyone who has helped me out on this site and can’t thank them enough.
I guess my frustration comes from when you find a question that someone else has posted and it is what you where needing an answer to, so you read it and it say something that is fairly open and closed like "does anyone know a way to do this..." or "does anyone know if its possible to stop this...." which in essence is a question many people might want an answer to, but instead of the guidance you and others so often provide, sometimes you just get replies of " what do you want to do that for?" and statements that take the whole conversation off in a different direction.
I spent a long time reading every log of this post and still don’t know if it is possible to do what the person asked in the beginning!
I have no problem with someone asking or advising you on a better way to do something but sometimes the help just is not there or as clear as you might expect. To use your analogy if i may about hiring a boat, its like me going to a boat hirer and asking him where i can hire a speed boat from that will take me from this side of the river to the other, and instead of the boat hirer just saying either “well boats from here don’t go to the other side!” or “ you can get a boat from Tim down the road”, him turning around and saying something like “so you want to hire a speed boat do you why would you want to hire a speed boat?” reply “ so I can get to the other side where my family live” his reply “No you don’t want to hire a speed boat, you get a much better experience on a sail boat I can sell you a sail boat, I’m not listening to the fact you can’t sail, I don't like speed boats so you need a sail boat! now if you want a sail boat I have lots of them to hire or buy and i would never use a speed boat so you shouldn't!”"so can I hire a speed boat if i want one even though you dont advise it?" "well i told you you dont want a speed boat! so why should I tell you if you can hire one?""because it would help me in what i want to do!" yes but a sail boat is so much better in the longterm and i dont like speed boats!" "but a speed boat will do what i want it to do! so can I hire one?" "I dont want you to know if you can, because i think you should buy a sail boat!"or a ship!

and you end up coming away with no more idea about where to hire a speed boat from than when you started!

Anyway you get the point i was trying to make i hope?

But once again I do not want to take away from all your help and will finish by saying if we have to read the ramblings of a few, to get the help of the experts it’s a small price to pay for free helpful advice.

Tony

 

[gemma-the-husky]

I will expand a little on my previous short response

the thing is - the real problem with Access is that is IS so damned good.

as I say, nobody would dream of trying to prevent users developing their own spreadsheets - and nobody would expect IT to support spreadsheets that do exist. (or maybe they do, if a user really gets into complex areas)

But when you get access it's different.

First, it's so powerful that actually we set out to restrict what users can do, because they don't know enough to use it properly. Hence we hide database windows, and protect code.

And so, gradually, access ends up being used as a system development architecture rather than the simple "enhanced" spreadsheet that was probably originally envisaged. It must even be a bit of a problem child for MS - as they have to keep on providing VBA even after they have moved to .net.

And then, because it is no longer something for novices, users find themselves out of their depth, and need support.

The difference being that people can build spreadsheets without needing to touch VBA. But you can't build a decent database witihout code.

So, as far as the IT department goes - if it is REALLY an issue - the only way out is to build and provide the databases yourself, and never install full versions of access.

Now, I have never worked in an organisation with a IT department - but I suspect that where this is one, there is an over-rigorous reliance on "systems" and "signing-off" projects, and "formal specifications" - which means that "lite footprint" projects don't happen, to the frustration of the users who need to get the job done - and maybe a bit of Empire-Building within the IT department.

all leading to "we can't possibly develop the important database you need for another 2 months ...."

And not enough "happy hacking". I assure you that knowledgeable developers can reliably "hack" databases (ie design data structures) without needing months of data analysis. I know I can.

So a better solution is maybe to embrace Access, realise that good Access development will ease the load on IT depts, and help the guys who need these "quick and dirty" databases.

After all, if a department can build something they need for themselves for a £100 /$100 or so, plus a few hours development - why should that be a problem for a company?

 

[gemma-the-husky]

Hi Bob,
You have made a very good point and I was not intending to discard all the extremely useful advice and the help that you experts are willing to provide to us novices with, after all without your help we would be stuck on sometime even the simplest of tasks. I am amazed and grateful to everyone who has helped me out on this site and can’t thank them enough.
I guess my frustration comes from when you find a question that someone else has posted and it is what you where needing an answer to, so you read it and it say something that is fairly open and closed like "does anyone know a way to do this..." or "does anyone know if its possible to stop this...." which in essence is a question many people might want an answer to, but instead of the guidance you and others so often provide, sometimes you just get replies of " what do you want to do that for?" and statements that take the whole conversation off in a different direction.
I spent a long time reading every log of this post and still don’t know if it is possible to do what the person asked in the beginning!
I have no problem with someone asking or advising you on a better way to do something but sometimes the help just is not there or as clear as you might expect. To use your analogy if i may about hiring a boat, its like me going to a boat hirer and asking him where i can hire a speed boat from that will take me from this side of the river to the other, and instead of the boat hirer just saying either “well boats from here don’t go to the other side!” or “ you can get a boat from Tim down the road”, him turning around and saying something like “so you want to hire a speed boat do you why would you want to hire a speed boat?” reply “ so I can get to the other side where my family live” his reply “No you don’t want to hire a speed boat, you get a much better experience on a sail boat I can sell you a sail boat, I’m not listening to the fact you can’t sail, I don't like speed boats so you need a sail boat! now if you want a sail boat I have lots of them to hire or buy and i would never use a speed boat so you shouldn't!”"so can I hire a speed boat if i want one even though you dont advise it?" "well i told you you dont want a speed boat! so why should I tell you if you can hire one?""because it would help me in what i want to do!" yes but a sail boat is so much better in the longterm and i dont like speed boats!" "but a speed boat will do what i want it to do! so can I hire one?" "I dont want you to know if you can, because i think you should buy a sail boat!"or a ship!

and you end up coming away with no more idea about where to hire a speed boat from than when you started!

Anyway you get the point i was trying to make i hope?

But once again I do not want to take away from all your help and will finish by saying if we have to read the ramblings of a few, to get the help of the experts it’s a small price to pay for free helpful advice.

Tony

tony

good points - but I think what happens is that people sometimes ask questions that reflect an imperfect knowledge of the problem, and that the answers attempt to steer the users back to good practice.

eg
"how should I design a lookup field in a table" will inevitable end up with advice not to do so.

and many questions will inevitable lead to discussions about normalisation.


eg - to use your analogy - the question "how do I cross the Channel in a rowing boat", will surely lead to recommendations not to try. An experienced rower would know if it was possible, and have phrased the question differently.

 

[Groundrush]

I will expand a little on my previous short response

the thing is - the real problem with Access is that is IS so damned good.

as I say, nobody would dream of trying to prevent users developing their own spreadsheets - and nobody would expect IT to support spreadsheets that do exist. (or maybe they do, if a user really gets into complex areas)

But when you get access it's different.

First, it's so powerful that actually we set out to restrict what users can do, because they don't know enough to use it properly. Hence we hide database windows, and protect code.

And so, gradually, access ends up being used as a system development architecture rather than the simple "enhanced" spreadsheet that was probably originally envisaged. It must even be a bit of a problem child for MS - as they have to keep on providing VBA even after they have moved to .net.

And then, because it is no longer something for novices, users find themselves out of their depth, and need support.

The difference being that people can build spreadsheets without needing to touch VBA. But you can't build a decent database witihout code.

So, as far as the IT department goes - if it is REALLY an issue - the only way out is to build and provide the databases yourself, and never install full versions of access.

Now, I have never worked in an organisation with a IT department - but I suspect that where this is one, there is an over-rigorous reliance on "systems" and "signing-off" projects, and "formal specifications" - which means that "lite footprint" projects don't happen, to the frustration of the users who need to get the job done - and maybe a bit of Empire-Building within the IT department.

all leading to "we can't possibly develop the important database you need for another 2 months ...."

And not enough "happy hacking". I assure you that knowledgeable developers can reliably "hack" databases (ie design data structures) without needing months of data analysis. I know I can.

So a better solution is maybe to embrace Access, realise that good Access development will ease the load on IT depts, and help the guys who need these "quick and dirty" databases.

After all, if a department can build something they need for themselves for a £100 /$100 or so, plus a few hours development - why should that be a problem for a company?

Nice one Gemma, hit the nail on the head there
Been using my so called tempory database for over 4 years whilst we wait for the 400k plus corportate system to be completed & implemented.
Because of the amazing things Access can do I have been able to accomplish things that would have costs thousands of pounds to get the so called Non Access developers to do for us.

Not only do we have the problem with IT restricting what we can do they are also slowing down the development of the coprparate system.

VIVA ACCESS!!