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.

I would go with something more stable if these are going to be multi-user db's.  Maintaining a multi-user access database is in my experience much more trouble than its' worth.  

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

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,

Alex, Thanks for replying, it all helps. Regarding the multiusers we will have maybe 3 people using the database. That is entering the data, running the reports, running queries it's not going to be an enterprise wide use database. I will look ay MYSQL. Thanks

Skip I was hoping you would respond to my inquiry, you have been a BIG help to me in the past. This data base is not going to be a enterprise wide data base just one department/division. I understand that management is a hugh issue. That is why I would like to keep it small and simple. I'm in middle of getting our processes under control when I came across the multiple databases and wasn't sure how to proceed.

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,

Skip, at this point I don't, but all I'm trying fiqure out what would work best for us ie: Excel Access, Sql, etc... Should I get a programmer involved now or later, should we manage it or a third party, is it feasible or not..  I did find some good references to look into.

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,

Hi MODMAC,

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.

03Explorer,
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?

modmac,

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

Repeat after me:

"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.

Excel is not a database O.K. What do you recommend?

Modmac,

Quote:

The real question is, is Access the best tool for what I want to do or is there someting else that would work better?

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.

You might want to consider other FREE databases in addition to MS Access.

http://downloads.techrepublic.com.com/5138-9592-6028761.html

"When the only tool you have is a hammer, every screw looks like a nail."

< M!ke >
Acupuncture Development:  a jab well done.

and

FAQ333-6200: Free Database Servers/Engines

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.  
Jeff Atwood}

Realizing that some of the FREE databases don't have an included development environment, you would also have to get a development environment to create the user interface to the database.

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

Lespaul,

What free db's do have developer environments (that you know of).  You struck a curiosity with your last post.

Quote:

Realizing that some of the FREE databases don't have an included development environment, you would also have to get a development environment to create the user interface to the database.

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}

I just read from TechRepublic that Oracle XE does have a GUI Management Tool where SQL 2005 Express and MySQL 5.0 do not.

I think that is what lespaul was implying.  Right lespaul?

>> SQL 2005 Express and MySQL 5.0 do not

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}

actually the link is from above...

Quote:

http://downloads.techrepublic.com.com/5138-9592-6028761.html by - LNBruno

Sorry, I really don't feel like registering especially if they stated out there allegations like that.  Surly if this is true the resource can be found at the dozens of respectful free viewing community sites or more importantly the companies themselves.

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.  
Jeff Atwood}

I don't think lespaul meant database management tools like SSMS is for SQL Server but IDEs to create the GUI to the database. Exactly there are very few of them. Access could also handle data from MS Sql or any database providing ODBC access plus it's already the GUI to those small local systems you want to pull together. And a userbase of 3 users is not hard to handle with a GUI like Access.

Bye, Olaf.

Sorry about that "Jeff."  I forget sometimes about which sites are registration required...

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.

I did not go into each link and didn't want to make a blanket statement:

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

No worries 03Explorer.  I just hope you do not take that article seriously because it really is wrong.  I work with all of them (MySQL in the past) and to be honest I can't believe it says that.

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}

ahh..I can see what they were getting at them LNBruno

Thanks for setting it straight

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.  
Jeff Atwood}

>> Sorry for the flurry

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 level

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.  
Jeff Atwood}

Hey "Jeff"  (I like your blog )

>>>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

What?  Nobody uses Notepad to write VBScript/JavaScript in ASP pages anymore?  

< M!ke >
Acupuncture Development:  a jab well done.

Textpad all the way for scripting!!!!

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.  
Jeff Atwood}

Wha hoppened?

Ignorance of certain subjects is a great part of wisdom

Thank you all, You have all giving me the insight I needed.

This project is way more then I want to take on without a professional programmer.

Thank you Again.

We sometimes use Excel as an alternate front end for database querries with ODBC using views.

If you do not like my post feel free to point out your opinion or my errors.

SQL Server comes in a workgroup edition now that sounds perfect for you.  Much less expensive than the standard edition and it functions well for the workgroup.

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....

ModMac,

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?