ASP.net frontend intranet solution

[Blazemore]

Alright, I need some advice from the community of developers here.

I work for a law firm that has a very large client base that also handles many different cases. I do not want to get into specifics of what the law firm handles but trust that each case type is drastically different from the other with many, many complex data fields that must be accounted for. In this law farm you have a few databases.

You have a access 2003 database that handles alot of the entry work for a couple select case types that happen to be the biggest mass tort cases with about 10,000 clients. The DB is written by a serious office automation bad ass, but he is stuck on access 2003 and is a dinosaur that will not adapt to a fast growing number of concurrent users. There is no getting around this. This database must exist because it has existed for many years. It is held together by VBA bubblegum and rubberbands, but is relatively stable.

You have a Sybase ASE back end DB that is powered by licensed front end software by some company out in the middle of Bobsville, US that we hear from 1-2 times a year. This database is supposed to house all clients for all case types and handle more delicate matters such as financial numbers. This is the DB I work with most these days, and I almost never use the actual front end. I use Sybase central and a couple homemade whipped up .net apps to manipulate the back end tables. Most recently I had to distribute a big arse dollar settlement among 3000+ of the folks and was responsible for the mail merge of the numbers on the documents. Attorneys fees, case expenses, liens against the settlement etc. Also had to do the bank statement, and the general and detailed reports from the back end tables. The front end software simply was not capable of the complexity of detail that was required.

Then you have a couple other smaller access databases being developed by super access guru who is preparing for some cases upcoming. We are expanding fast. And new computers are coming in with office 2007 and the 2003 databases simply have so much code they just cannot work on the 2007 machines. They do not import right, and the work to make them work correctly appears from the surface to be ridiculously daunting. The workaround so far has been guru deleting Access '07 and replacing it with '03 on those machines. He is eventually going to have that come back and bite him in the ass when some of the higher ups find out.

Ok.

This is what I have walked into.

These are the stipulations for my help request:

1) I cannot quit and find better. This is me cutting my teeth. I need the experience.

2) Access guru cannot be changed and is for all intensive purposes, "The MAN". We get along famously, we love coding together. He seriously is a office automation bad ass. But the future of coding has left him behind about 5 years ago. I cannot get rid of access altogether.

The past two days I have begun designing a ASP.net front end to communicate with all of the databases(via ADO.net), and also perhaps be the tool we use for all future projects working on a SQL Server backend. Is this a relatively common solution? My thinking is that we will not have to deal with versions of anything since it is all accessible via IE/Firefox, security should be fine using windows authentication to keep it accessible to only those on the company domain. And I do not have to deploy a .exe application to all the employees everytime I update. I can just apply updates directly to the IIS server.

Also the ASP.net app can read from all the currently existing databases, and while it is not feasible to open up ALL the functionality that exists on all the front ends. I can open up a good amount of it.

One of my concerns is the topic of reporting services. Most of the devices we have currently have alot of merging functionality. Opening up Excel.workbook classes for spreadsheets, and merging data onto bookmarks in Word, etc. I know this is limited in a web app. I will have to research my options for this.

Anyway, any advice is great. What would you do in my place?

[Zippygoose]

http://office.microsoft.com/en-us/sh...492001033.aspx

[Tripamang]

I don't know how much data you're dealing with but I can recommend:

iCONECT® - iCONECT.com for a document repository with a oracle backend. Has a nice portal interface for the front end that has most features lawyers are looking for.

I personally think it's awesome! :P I'm slightly biased on the matter though feel free to PM if you've got further questions.

[Slide]

Set up an ODBC connection for your colleague, he can use Access as a client for SQL Server. (as can others)

[Niceshot23]

You 2 need to start developing a strategy to move to SQL, Oracle, etc...

My specialty is to automate workflow in businesses and simply put, access is fine and dandy for small apps with a few users but not at the scale you're talking about ... Many, many, times I've come in to clean up others people illogical programs, redundant code/processes, horrible architecture, and streamline businesses. You're right that right now there's no way of continuing to support the current model with a new backend in 1 shot ... so my advice to you is to hire someone else that is either a) more knowledgable then you and your access/vba guru to have him work on a new data model or b) someone who's more or less at your scale and you work on a new data model while he helps maintain the current model .... cause eventually the shit will hit the fan if you get more users or expand the capabilities of the software


besides, access is a security nightmare cause really, there's barely any but a password that could be hexedited