Simple, efficient SQL user interface

[BobbyPolo]

Hello. We are using an ERP system with a poor budgeting module. I am to decide how to best overcome it, but have hit a road block. Background:

The ERP system runs a daily routine to updates the data warehouse run on SQL Server.

On that same SQL Sever (for now anyway), we plan to have a DB seperate of the DB warehouse that will hold our budgeting projections (sales forecats for example).

What do I need:

I need a solution that will allow many users view (in a structured format) relevant data from the warehouse and update/add forecasts for future periods.

What I have done so far is create a locked down view of a particular salesperon's data in an Excel sheet. He/she then uses that prior year information to submit a forecast of future sales. A SQL DTS package runs, grabs the sheet with the forecast on it, validates it, and updates the DB daily. (It's more complicated than that, but that's the basic idea).

Now the problem I have is when the management wants to view the data and change it. They will need to see the data rolled up into totals by product category or employee, for example. Then, if a certain forecast need adjusted, management should be able to change the value and see it updated immediately.

My ideas have been to offer a web based solution or maybe a stand alone VB program that has access to the DB. I think the web based solution would be simpler and safer, but I was hoping there was another way of doing it. Creating a web application that provides multiple views of the data and drilling down seems rather complicated, though it might not be once I start.

I hope my description makes sense. Overall I just want a safe, effiecient, and innexpensive means to modify the data in my DB.

 

[shmiller]

Whether you go VB or ASP, using ADO for the data access is the same. So I would suggest using whatever you are most comfortable with? What kind of programming have you done?

 

[BobbyPolo]

I have done ASP programming before using ADOand also made extensive use of ADO in Excel and Access. I have also done some T-SQL as well as a good bit of VBScripting using ADO in SQL Packages.

So are those really the only ways to go if we develop a program in house (using ADO on web pages, in Office products, or in a stand alone VB program)? I am sure I could make any of my choices work one way or another, I just wanted to be positive that I explored all the options rather than just what options my experiences limit me to.

Thanks for your reply shmiller.

 

[shmiller]

I'm sure there are other options, depending on what tools you have access to. But in my opinion, if what you want is just a basic user interface for the db, a VB app would be easiest and fastest to implement. But that's just from my own experience, which has mainly been writing VB and ASP front-ends to SQLServer, Access, and Oracle. If you need to generate reports on the data, using Access as a front-end might be a good option too. I have done that two different ways. Using pass-through queries with DSN's set up for the db, and using ADO to populate local Access tables, then get the data for display and reports from those local tables. Hope this helped.

Shanti