I have several years in the IT industry as an applications programmer.
Currently, I am a programmer/analyst for a government institution and have been in my position for about 9 months. My responsibility is to maintain an Access/VB application that is used by 8 analysts in my department. For sake of privacy/security I will refer to this application as ‘COLORS’. Since being here, I have restored/repaired broken interfaces and learned how to resolve bugs in ‘COLORS’ and implement the required enhancements.

Now I am required to redesign and rewrite the COLORS application.The problem is getting started. In the past I have always worked in a team environment usually under some team leader or project manager. All the platform, software, database and security issues had already been decided and setup by someone else. All I had to do was create batch or online programs or scripts. The good news is that since I currently work alone, I can make all the decisions myself. That bad news is that I’m not sure what decisions to make and I am limited by cost. I may be able to obtain new software development products that’s not definite.

A little about my skills. I consider myself a pretty good analyst and usually can figure things out once I get started as long as I have the right resources. In this case, most of my knowledge will come from within or from textbooks or online. I am not surrounded by an abundance of knowledgeable colleagues. There MAY be 1 or 2 people that can help me, but I don’t know who they are yet. I understand relational databases, how to set up the tables and all the logical coding issues that will be involved. I have experience with MS SQL Server and Unix. Languages include C, SQL, Unix shell, Javascript, VBscript. None are current (although recallable), except for Visual Basic.

Some background on ‘COLORS’. The application is very small written in Access/VB. No table will have over 50,000 records. Although the system is small, it is very important and has high visibility. Integrity of data is of utmost importance. However it is not used on a daily basis by any analyst. The work is cyclical, but availability of the application is critical. The client platform is a desktop pc running with Windows XP Professional. Internet Explorer is our Browser. Current system has no graphics. The system is for internal use only and is run from the network.

Please help with the following:

Looking for general suggestions on selecting an RDBMS (SQL Server is available and probably will have to be used, but I still would like other suggestions). Another server that may be available is running UNIX and has Oracle loaded. I have no idea what the difference is between Oracle and SQL Server and which would be better for my purposes. Suggestions on connectivity software, security issues, backup utilities are needed. Also design suggestions (based on description above) I may not have thought of are welcome.

The new application will be called via a departmental intranet web site or it can have its own url. Any suggestions on which way is best. Advantages/Disadvantages of each.

I don’t want to use Access for the rewrite because I don’t find it to be particularly robust. I would also like to improve the look and feel of the screens. The current screens presentation and navigation leaves something to be desired. Any suggestions for windows development software. I was thinking Dreamweaver, Visual Studio or MS Interdev, but am open to other suggestions. ASP or .NET or ??????

The current system only has about 6 windows, but the new system will probably have closer to 10 with only 1 graphic on the login page. I would like to have up-to-date windows functions like drag/drop cascading menus, popup text when focusing on an object. Right now they don’t have any of that. My desire is to have instant response time since the volume of records is relatively small and the number of users is small. Records will have no more than 25 fields.

I also need suggestions for a scripting language or some other facility to bring mainframe data sets onto the server and update COLORS tables from them.

Kelly

This like like an ideal use for MSDE (Microsoft Desktop Engine.)

MSDE is SQL Server, Lite. It has limitations added by MS to cause multi-user performance to approximate that of Access.

However, it offers most of SQL Server's benefits, including good security, stored procedures and triggers. It's very stable. And, if the app ever grows to the point that you need to upgrade, it's a piece of cake to upgrade to a full-blown instance of SQL Server.

In addition, MS provides upsizing tools to create a database from your existing Access datafile.

And, since you're probably already familiar with vba in the current access app, I would use visual studio to create the new version. VB works quite well in data presentation & manipulation.

Note just re-read your post. If SQL Server is available, use it. The upsize tools would work just fine.

ANd, check out post #11 Here

Thanks for the reply. I need to find out if we can get Visual Studio. Any suggestions for: 'I also need suggestions for a scripting language or some other facility to bring mainframe data sets onto the server and update COLORS tables from them'. The server that has SQL Server lloaded on it is a Windows 2003 Server. Does this mean that I can only use DOS as the scripting language?