I have the task of reorganizing or reimplementing a system of databases to allow for efficient data mining of information in addition to tasks already being performed with by using the databases.

Currently:

All databases are of Jet 4.0 (what Microsoft Access usually uses) databases managed using Microsoft Access 2003. The database storage files are of "Microsoft Access Databases (*.mdb)" stored on a central location (server).

Over 1000 databases exist (rough guess is about 1,500 databases), each having many tables and forms.

On average, about 10 clients access the data at a time with twenty or less total clients that may access the data.

I am thinking an SQL database solution with an easy to use front-end (this would probably be Microsoft Access) may be what I need.

What database system would be recommended for this type of project?

Is it possible to query multiple databases for a given data at a time (this may be useful for the data mining)? Perhaps with SQL?

Is it possible to create a database of databases?

I am still new to database design and my experience has mostly been with using Microsoft Access (mostly Microsoft Access 2000) for table and form design. I am open to trying new, efficient systems and would appreciate any assistance I could get.

From the little you've mentioned I'd say you have a difficult task ahead of you. If all the databases are the same structure then I'd try to combine them into one single database. If they're not the same structure then I'd suggest looking around for another job. In either case I'd strongly consider getting someone on board who knows what they're doing.

This does sound like an almightly mess, which is probably typical of companies that are blighted with a legacy of monolithic desktop systems like Access. First and foremost I'd say you should look for some help. Hire someone if necessary. You need someone with experience in client-server database development and data integration.

As Mike says, your task will be easier if all databases are exactly the same. Sadly they probably won't be, even if you think they are.

This I intended and still intend to do.

I want to be sure I get a truly qualified person to work with me on this project. What are some good sources for well-skilled people for this task?

Any specific recommendations for the Chicago, Illinois, United States of America area?

No doubt you'll get loads of offers now.

Before you start though I'd be quite carefull to decide what it is you actually want from this new system, is it:

• just to clean things up
• just to standardise everyone making things easier to manage
• savings due to economies of scale
• to offer new features
• to combine the best of all your existiing systems

Will the benefits of this new system outweigh the costs of implementing it?How are you going to get existing users to make the switch? Will they want to switch? Can you force them? Your original email didn't mention any desired benefits so will your users just see this change as change for change's sake?

Would it be eaiser to comming up with a new system that offers more features etc and then get people to migrate accross? You could provide tools to help users migrate data in. Then do a few groups at a time. Get their feedback and improve the system. Before moving more groups across and closing down their old systems.

Mike

I'd echo a lot of that.
I think it would be interesting to know an approximate split of these 1500 databases. Are they are for the same thing, are they 1500 completely different databases, are there about 10 different models of about 150 databases each etc.

How many distinct business functions are being supported by these databases is going to be a very major factor in converting them. It may be that some are not worth the effort, whereas others are (depending on your thoughts on some of the above answers).

pootle flump ur codings are working excelent.

The benefit of having a new database system should allow cost savings and facilitate data mining (it is quite time consuming finding related data from different Access databases).

The benefits should exceed the costs. I have not yet fully been able to estimate the costs (Much of the cost may be dependant on time, and I am uncertain how long an implementation would take).

I do not know whether it would be better (I am not looking for easier) to incrementally migrate to a new system without being able to closely estimate the time for project completion.

New features may be desirable, especially if they can significantly reduce the costs (time) related to the performance of tasks.

Employees are professional and may be trained to use new features. They may welcome a new system that they perceive may benefit them (decreased task time would be the main benefit). If the new features are many and unfamiliar, it may be best to train in the use of the new features slowly.

I do not have the split information at this time.

I believe There are few models (maybe about 3).

Many of the databases are similar (for example, each project may have the same fields (each project starts with the same model)).

Most of the databases I believe are prject related databases.

Some logs are entered into Microsoft Excel 2003 spreadsheets.

I believe most of the databases are projects

I believe that no more than four business units use the databases (directly or indirectly).

All the different business units use the same databases. New data is also added frequently to the databases.

Reoprts from information from the databases are to be generated. A tool currently is Seagate Crystal Reports.

I wouldn't underestimate the issues of getting all your users to change over to a new system and away from something they're used to. I obviously don't know the structure of your company but from experience I'd say that everyone hates change, especially if the only people getting a benefit from the new system will be the people in the central office.

You could run a simple survey around the current users to see how the system could be improved while promoting the benefits of this new system. Then try and use any reasonable suggestions in the new design. In this way they might feel a little bit more behind the project.

Mike

I would also echo this. Stakeholder buy in is as big a factor in the success or failure of a system as the technical proficiency.

pootle flump ur codings are working excelent.

True.

One approach might be to migrate the data into a back-end server, but keep the access front end, (forms, reports) and link the front end to the back end with ODBC.

This what I thought would be best (although with more than Microsoft Access as a front-end).