General Database Question
General Database Question
(OP)
I have a general question and am looking for input. The company I'm currently employed with is using several different MS Access databases for recording and reporting. There are several problems, 1)they are all seperate databases, 2)people are using different versions of Access, 3) we have no one person who know hows to rewrite/create new reoprts. Now I have been asked to see if there is a better way. I understand and can get around the different Access version issues. I know we could with some help combine all the databases into one. The purpose of the databases is to track jobs, sales quota, commission etc... We need to keep the data for historic/comparison etc.. I'm somewhat good with excel but wasn't sure about keeping history. So here is my question: What do think would be the best choice for us? excel access, sql, or something else.
RE: General Database Question
If you want the reporting built in (sort of) to your database, SQL Server is probably the way to go. Most of the others, you will need to create a separate front end for reporting (or if its' Oracle you go with, I think you can buy reporting add-ons for a hefty fee)
If you want to write reporting applications, you could probably get away with MySQL or one of the other free ones (although I know next to nothing about these platforms).
Hope this helps,
ALex
Ignorance of certain subjects is a great part of wisdom
RE: General Database Question
Hi,
"There are several problems"
The biggest is management. To have allowed this to happen; not created and enforced uniform policies and procedures, not hired a competent technical manager to oversee and manage the corporated resource of information, is a huge problem that a better database will not solve.
You must get in control of your processes before your try to solve your technical issues.
Skip,
![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
RE: General Database Question
RE: General Database Question
RE: General Database Question
Do you have the authority to set & enforce policy & procedure for this area of concern?
If you do have that authority and choose to exercise it, then I would set in motion establishing the necessary P & P required to protect and maintain your data. At the same time, I would do as Alex suggested to solve the technical issues of stability continuity, data integrity, etc.
That's kind of a bird's eye view.
Skip,
![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
RE: General Database Question
RE: General Database Question
Just keep in mind that you could get the finest database & software, and have a top-notch system, but if you do not have the correct policies & procedures that everyone uses without fail, you'll end up with phantom enclaves of data segments again.
Another issue to address in the process of migrating to new system is the conversion process. Depending on the scope of the effort, this project could be a significant cost. It includes designing the mapping, the design of the process of converting, the actual conversion and the testing & verification of the data.
Skip,
![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
RE: General Database Question
If I were in your position... I agree with SkipVought regarding P & P. But first thing I would consider is to get all Access db's using the same version. Build a central administration point (MDW) and lock down the versions you have out there. What I mean is make them so you 'have' to use the new central system mdw file. This allows you to manage the users and access rights from one source. Then build a reporting database (a new mdb) and link the tables from all the other databases in the one.
With the security in place you can administer users and what they can and can not do in all the databases (allow them to only do what they need to do and no more... ie delete records).
With the reporting mdb 'database' you can build reports using one database.
If you have the ability, I would even consider building a VBA script to 'IMPORT' the tables at the touch of a button rather than 'LINK' to the other databases. That way the data that you are 'reporting' or 'playing' with is only a copy and can not mess with the 'live' data.
I hope this helps.
RE: General Database Question
Thanks for responding. The real question is, is Access the best tool for what I want to do or is there someting else that would work better?
RE: General Database Question
I believe that the answer is yes. In light of the previous posts I can only agree that getting everything in one place is heaps better than all over the place. Since Access can do db, reporting, charting and number crunching for a small number of users, it seems ideal.
If this grows like Topsy, then use the Access upsizing tool and move over to SQL Server.
Regards
T
Grinding away at things Oracular
RE: General Database Question
"Excel is not a database."
"Excel is not a database."
"Excel is not a database."
...
Users find it comforting because they can grasp a single, two-dimensional table. But from the standpoint of a database, Excel is a straitjacket. Need a database? Forget Excel.
RE: General Database Question
RE: General Database Question
From what I know of your project... MS Access would be your best option. If you can, use at minimum version 2000 of MS Access.
This web site has been a life saver in my learning of Access. Ask questions as they come up... this is a great learning tool.
RE: General Database Question
http://dow
"When the only tool you have is a hammer, every screw looks like a nail."
< M!ke >
Acupuncture Development: a jab well done.
RE: General Database Question
FAQ333-6200: Free Database Servers/Engines
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
Leslie
Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual
Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins
RE: General Database Question
What free db's do have developer environments (that you know of). You struck a curiosity with your last post.
RE: General Database Question
That is very misleading. Almost no database server has a IDE for developing GUI applications. Can you please be much more detailed as to what you are getting at. Are you referring to BI tools and warehousing tools? If you are going there the SQL Server 2005 Express has them all plus a fully capable IDE environment
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
I think that is what lespaul was implying. Right lespaul?
RE: General Database Question
That is incorrect. I have Oracle Express running and the management tools are horrid compared to SSMS for sql server and the many management tool available for MySQL at absolutely no charge or license agreements
Where did you read that? Can you please post links to references when you state those things please. It helps discuss them
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
RE: General Database Question
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
Bye, Olaf.
RE: General Database Question
What it actually said about Included GUI Management Tool was that:
SQL 2005 Express: No, available separately
Oracle Database XE: Yes; Web based
MySQL 5.0: No; third party available
< M!ke >
Acupuncture Development: a jab well done.
RE: General Database Question
No other database has a Development environment
I don't know that the above is a true statement. That's why my original statement was ambiguous...As far as I know, Access is the only database that contains the development environment, but I don't KNOW that it really IS the only one.
Sorry for the flurry.
Leslie
RE: General Database Question
My take for what it's worth. Access has no place in a production process that there is a need to deliver critical business information. The OP pointed out some of the reasoning behind this.
My largest one is "people are using different versions of Access". You are in a completely hostile environment towards your development efforts with MS Access. Really this is not what the application was meant for. It's a user application not a fully qualified stable platform.
A comparison to SQL Server Express leaves no questions to why would even consider Access. If you look at GUI development for your data there are several points you must consider
1) n-Tier. Would you rather have your data on the same level as you GUI where the user is directly interacting and altering it? Umm...not me. If you can develop VBA code to create some forms in Access you can open up Business Intelligence that is part of the client tools installation for SQL Server (Or the Express IDE environments that are free to use by MS) and write a interface to the data. I've never seen a n-tier application architecture model where the data tier is on the same level as the GUI.
You can integrity, much better handling and layering and security. That being security of your data and security from your users breaking everything.
2) Versions and Upgrades. OK. How many times have you heard the story, "Someone opened my access database in a newer version and said yes to convert it and now it's broken and my backup is at the bottom of the Hudson cause the off-site transport avoided a car crash and went of the south bridge". Really, where is the disaster/recovery model with a desktop application?
It's critical for us as professionals in our respected fields to provide sound and stable products so we retain our image and keep going forward.
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
Thanks for setting it straight
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
Don't be Leslie. It's a good conversation. These are the ones that we learn the most from as long as they stay under the
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
>>>Access has no place in a production process that there is a need to deliver critical business information.
I have to disagree. Access is a perfectly acceptable front end (provided that the company's IT department keeps everyone up to date on versions!). Its' reporting tools are not THAT awful either, for what it is.
Any data that I NEED to have, I am sure as hell not going to store in access. But I have no problems interacting with it and displaying it through access.
Just my 2 cents...
Alex
Ignorance of certain subjects is a great part of wisdom
RE: General Database Question
< M!ke >
Acupuncture Development: a jab well done.
RE: General Database Question
____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood
RE: General Database Question
Ignorance of certain subjects is a great part of wisdom
RE: General Database Question
This project is way more then I want to take on without a professional programmer.
Thank you Again.
RE: General Database Question
If you do not like my post feel free to point out your opinion or my errors.
RE: General Database Question
Of course, you're looking at writing some code so that may be a problem if your organization doesn't have a developer which sounds the case since you've got multiple versions of Access. Either way, getting everyone on the same version of Access is probably a must....
RE: General Database Question
We had a very similar problem where I work, so I instigated some changes.
Now we have all the data tables in MySQL (free!) with access front ends (recognisable to users).
The access front ends also have password-protected forms that load new data when it is available so generally getting into the guts of the MySQL is not required.
It does, however, allow very competent users to start writing SQL directly in some cases, so gives us an obvious learning environment too.
If you want to know more - let me know; maybe I can email you directly and give some some more details. If this sounds like a good way to go to you of course...
Good luck!
Fee
The question should be Is it worth trying to do? not Can it be done?