×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

General Database Question
3

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

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

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,

glasses
tongue

RE: General Database Question

(OP)
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

RE: General Database Question

(OP)
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.

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,

glasses
tongue

RE: General Database Question

(OP)
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.

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,

glasses
tongue

RE: General Database Question

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.

RE: General Database Question

(OP)
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?

RE: General Database Question

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

RE: General Database Question

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.

RE: General Database Question

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

RE: General Database Question

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.

RE: General Database Question

and

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

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

RE: General Database Question

Lespaul,

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

RE: General Database Question

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

RE: General Database Question

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?

RE: General Database Question

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

RE: General Database Question

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

RE: General Database Question

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.

RE: General Database Question

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.

RE: General Database Question

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

RE: General Database Question

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

RE: General Database Question

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

Thanks for setting it straight smile

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

RE: General Database Question

>> 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 flame level smile

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

RE: General Database Question

Hey "Jeff"  (I like your blog winky smile )

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

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

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

RE: General Database Question

Textpad all the way for scripting!!!!

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

RE: General Database Question

Wha hoppened?

Ignorance of certain subjects is a great part of wisdom

RE: General Database Question

(OP)
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.

RE: General Database Question

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.

RE: General Database Question

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

RE: General Database Question

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close