August 20, 2004 at 8:48 am
I have concerns regarding (non-IT) users developing Access Database/applications.
Case 1: The user who creates an Access database leaves the company. After the employee leaves, the department manager will call IT to figure out how to run the user's database. Now you’re stuck with an database schema that was not designed properly! -or- later they call you to perform some type of miracle work.
Case 2: A user develops a database. Another user develops a database that holds the same information. Now you have two users maintaining the same information. Had IT developed a database, an efficient design using existing data stores could have been used.
I am a developer/DBA. Can someone point me to any resources to implement policies to avoid these problem cases?
August 23, 2004 at 8:00 am
This was removed by the editor as SPAM
August 26, 2004 at 4:44 am
I share your concern, we have 100's of Access databases floating around.
However there's nothing wrong in letting end-users prototype their idea in Excel/Spreadsheets or Access/Small database as it saves a lot of time talking around a table, especially for end-user who cannot express their wishes correctly and completely in one sitting.
Just let management and end-user that you cannot be responsible for Mickey Mouse data base developments/storage, that data is crucial to any organisation's survival, that the final data must be sharable/visible by many/stored centrally (SQL Server?) and that prototyping is OK as long as everyone concerned knows what's happening (communications are the success and failure of IT Projects, not technology)
After that, you can use SQL Server DTS to upload Excel/Access or Access Upsizing tool (to SQL) to
move the data and structure to SQL Server or MSDE (=limited users but no SQL CAL @ £100 per user).
OK, if you don't have SQL, split your Access mdb into the front end (as a compiled mde which cannot be changed) and your data (mdb) in 2 different central folders with different security settings (with a third folder for development for IT personnel only)
Hope this help a bit
Alain
August 26, 2004 at 7:57 am
Thank you Alain. I agree with your analysis. You offered some excellent points.
I recently took over an Access database design from our Marketing department, and used it in a SQL Server database after making a few modifications. The new SQL Server database is updated from another existing database with a scheduled program. I developed a .net application for users to upload data from an Excel spreadsheet and display various Crystal reports. I also gave the Marketing department read/write rights to the tables and through Access (via ODBC).
By Marketing sharing their database design, it save me time in assessing their needs. I was able to centralize the data store and save the Marketing department time by updating their data from another source. I think the communication was good here aiding to the success of the project.
We have a help desk person who offers beginning and intermediate Access lessons to company employees. I would like to develop a statement (to be revised by my manager) that the Access instructor would read:
The Information Technology department cannot be responsible for Mickey Mouse database developments/storage made by non-IT employees. Information is crucial to our organization’s survival. Final production data must be sharable and stored centrally using a database management system such as SQL Server. Prototyping using Microsoft Access is OK as long as everyone concerned knows what's happening. Communications are the success and failure of IT Projects. Failure to communicate your database design to the Information Technology department can result in a lack of future IT support.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply