Excel,VB,Access,Java,.Net - Where Next?

Factual Questions
1

I’m trying to determine the most productive path for my programming education.

Here’s the situation - I’m an engineer by degree, an analyst by experience, and now an application developer by sheer luck. I joined a very small company and have been developing custom business applications because I’m the “guy-who-knows-how-to-program-Excel-macros-in-VB”. That’s pretty much my job title after a year of not touching any analysis work because application development is more important.

Now Excel has been very kind to us. The 2007 version has huge storage capability (1 million rows by 16,000 column) so my table based apps can be large and are easily indexed. But even with multi-processors, large ram, and screaming clock speeds the apps are showing the burden of manipulating large data Excel files (40-100MB). I’ve actually divided much of the input data into multiple files in order to speed up the turn around time for these applications but its pretty obvious that its time for these applications to grow out of Excel.

It will probably be a year until I can hire in a “real” programmer or DBA and to tell you the truth, I have very little knowledge of what kind of an expert I need.

So DBA’s, programmers,etc - any advice you have for me to keep these applications from collapsing under their own weight is appreciated.

We work in a very competitive environment so I apologize for not disclosing more about the programs in question. They are very data-base intensive so the logical move is to offload the storage out of Excel into some form of data base structure,

That’s where I need help. MSAccess appears to be the ugly stepchild as far as Microsoft is shown with its 2007 upgrades. I’ve never learned Access nor have I worked with data base structures.

I’ve been very successful with procedural programming using VB in Excel but realize that I have a whole new world to start picking up object oriented programing.

I’ve purchased a Java tutorial book (Head’s Up Java) but I’ve since been advised that I may want to steer towards Visual Basic .Net.

Sorry about the long intro - here’s some of my basic questions

Will moving my large data storage from Excel 2007 to Access 2003 give me any speed advantage? Ex: I have 10 40MB excel data files holding approximately 300 million elements within them. I then use a “program” excel file to retrieve and operate on select elements within those files. Could an Access DB do this quicker?

Should I skip (learning) Access and go straight to learning Visual Studio?

If the learning curve is just too steep for this old engineer, who do I hire?
A java guy?, a DBA expert, Visual studio? The new guy would be responsible for business development as well as DBA

2

Skip Access altogether and get right into SQL.

I don’t know what you are talking about with Visual Studio, though…VS is not a database application or a data application. It’s a tool for helping you program applications that can be connected to a variety of data sources (or, none at all).

Get the free Visual Studio.Net Express and SQL Server 2005 Express from Microsoft (and the SQL Management Studio). Then get one or 12 of the hundreds of books that cover those programs. It’s so easy to follow, it’s almost confusing for someone with hand-coding and raw SQL experience (that’s good for you, bad for me).

3

From what you describe, you need to manipulate data quickly more than you need to write fancy GUIs or write complex applications. If that’s the case, I would concentrate on learning SQL, which is the language that all relational databases (Access, SQL Server, Oracle) use.

SQL will allow you to harness the power of a real relational DBMS. If you’re going to go through the trouble to convert to a DBMS, I wouldn’t bother with Access. Chances are it will be a bit faster than Excel, but SQL Server will be much faster and scalable up to just about anything. It’s a long term professional solution; Access is made for small databases where you also need to create fancy GUIs or do a lot of non-data programming.

SQL Server doesn’t have anything in the way of GUI designers, but it integrates really well with .NET. You can use a mix of SQL and whatever programming language you like (Visual Basic, C#) to write anything from really basic data manipulation routines to full fledges Enterprise level applications.

4

er… What ZipperJJ said.

Great minds think alike!

5

Access is a really great database tool, if that’s as far as you ever intend to go - I’ve written quite complex business applications entirely in Access - and for the small company in which they were used, they were far more than adequate.

But if you’re planning to progress onto Oracle, SQL Server, MYSQL or something like that, do what ZipperJJ says and skip Access entirely. It’s a good solution for what it does, but a poor stepping stone because of the habits you might pick up from using a non-standard implementation of SQL.

6

To expound on my (and the others’) suggestion of SQL - MS SQL used to be more cost-prohibitive than simply having Access as part of Office. And less user-friendly.

Now with this 2005 Express edition, it’s cake. And it’s robust too - you will never miss whatever might be missing in the free version. I set up one of our DB-intensive apps on someone else’s server that was running 2005 Express and was mighty pleased with its performance and the tools available.

If your DB keeps growing, you will eventually have no choice but to move to SQL. You will be beating yourself up then if you didn’t choose to do it now.

7

I appear to be guilty of underplaying the importance of database in all of this. I viewed the database as simply the box holding all of the elements and assumed that the effort needed was in properly connecting the front end of the application to the DB.

I appreciate your responses. I plan to get the express programs mentioned by Zipperjj and will turn my attention to SQL.

If you know of any books or on-line resources that are geared toward the greenest of SQL newbies, please let me know.

With some hard studying, I hope to get a couple of my more voluminous data sources converted to a faster DBMS and at least be able to trade some acronyms with whomever I can hire in about a year.

8

Think of what you’re working on as the interim solution that will cover you until the guy you hire in a year comes up with something else. It might actually just be a slight modification to your database, or it might be something completely different. Regardless, you don’t want him talking about it over here.

With that in mind, you’re going to want two books. The first will be something along the lines of SQL Server 2005 for Dummies and will cover working with the specific database engine that you’re working with. The second will be something covering database design principles, so that you can come up with something basically sound. I’ve heard good things about Database Design for Mere Mortals, but can’t vouch for it personally.

The real key with the database is making it relatively easy to change the table structure. As long as you’ve got everything appropriately normalized, that won’t be a problem.