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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
General Database Question 3
- Thread starter modmac
- Start date
-
1
- #2
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
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
-
1
- #3
SkipVought
Programmer
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,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
[red][/red]![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]- Thread starter
- #4
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
- Thread starter
- #5
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.
SkipVought
Programmer
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,
[sub]
[red][/red][/sub]- Thread starter
- #7
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.
SkipVought
Programmer
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,
[sub]
[red][/red][/sub]-
1
- #9
03Explorer
Technical User
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.
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.
- Thread starter
- #10
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
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."
"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.
- Thread starter
- #13
03Explorer
Technical User
Modmac,
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.
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.
"When the only tool you have is a hammer, every screw looks like a nail."
< M!ke >
Acupuncture Development: a jab well done.
"When the only tool you have is a hammer, every screw looks like a nail."
< M!ke >
Acupuncture Development: a jab well done.
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
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
03Explorer
Technical User
Lespaul,
What free db's do have developer environments (that you know of). You struck a curiosity with your last post.
What free db's do have developer environments (that you know of). You struck a curiosity with your last post.
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
[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
03Explorer
Technical User
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?
I think that is what lespaul was implying. Right lespaul?
- Status
Similar threads
- Locked
- Question