Graduating from MS Access?

April 26, 2009 8:09 PM

I have become comfortable with maintaining an inventory database using MS Access. I want something more powerful now. Where do I go?

Currently I track inventory for surgery centers. Customers fax me a record of what they've used in each separate surgery. I track everything they use and order when they run low. I want to use more complicated functions. Where do I go from here? VB? MySQL? PHP?

posted by wayofthedodo to computers & internet (19 comments total)

First, if it works don't fix it.

What more advanced functions do you want to do? What are some problems that you can't solve now, or are too hard to solve with MS Access? That will guide our advice.
posted by intermod at 8:18 PM on April 26

Are you feeling constrained by the amount of data you can store in an Access database, the operations you can perform on that data, or the user interface you use to interact with it?
posted by b1tr0t at 8:24 PM on April 26

Are you saying that you have a single-user application built in Microsoft Access? If you might ever have to transfer the responsibility for this to someone else, I would actually say that you should be extremely careful about the portability, documentation, and reliability of your application, which going fancier might jeopardize. Maybe just a source control solution - putting all your code and database files in cvs or Visual SourceSafe - and some automated deployment / disaster recovery measures - making something like an installer that will move the app between two systems - is the place to start before you get any fancier with your own code, to try to make sure you don't end up building too brittle or tightly-coupled an application.

But if you're more looking for some fun technologies to play around with, as the guys above say some input on what you'd want to accomplish or what you'd enjoy doing would give us a better place to start.
posted by XMLicious at 8:36 PM on April 26 [1 favorite]

I'd suggest learning a little Python and start using its sqlite3 module. I don't think you'd want to use it for production but it would be good for education. This will get your feet wet with a real SQL style database and inform your future decisions. And it's free.
posted by chairface at 8:42 PM on April 26

Grab a copy of OpenOffice.org and try porting your Access database app to its Base component. If you manage to make it work as well as your Access version (and this should be possible, even though Base is a bit rough around the edges) you will end up with a product you can charge for without imposing a Microsoft licencing requirement on your customers. Base uses the hSQL embedded database engine by default, but can also be hooked up to just about any SQL backend including PostgreSQL and MySQL.
posted by flabdablet at 8:52 PM on April 26

Forget Python, sqlite3 and OpenOffice. Those are all fun toys, but you are trying to run a business. What do you need to do that Access doesn't do?

Staying in the Microsoft universe is probably going to be the least painful way to go for you. Don't try to jump to MySQL or PostgreSQL.
posted by b1tr0t at 9:04 PM on April 26

My problem currently is that I can add products, but i have to do this case-by-case, updating my database after each case and then determining what I need to order, ordering it, and then resetting my count of what I do order before I can enter the next inventory log. This works fine for my centers which have only one case a day, but not so well for those with multiple cases per day.

After that, I need to setup remote access so I can update the database from any computer.

Eventually, I want to be able to package the code for my access database and all the forms etc. with it, and sell it. I want to have a standalone product.

Hope this provides a bit more guidance. Thanks for all your thoughts so far.
posted by wayofthedodo at 10:27 PM on April 26

Oh yes, and I have multiple vendors, and each center has its own account number. I have a table setup with all the relevant information. I already have a report splitting up the products by vendor, and each order is identified with a center. How can I get the report to display the account number as well?
posted by wayofthedodo at 10:32 PM on April 26

I don't fully understand why you need to add products on a case-by-case basis, but it sounds like you have a data modeling problem. You probably need to take a step back and think about all of the products you are likely to want to support in the next few years, and design your tables so that it is easy to add new products.

For remote access, there are at least two options. Moving up to MS SQL Server or MySQL will allow you to remotely connect to your database. You will need to get your access app out to all the remote locations though. MS SQL Server is expensive, but will be much easier for you to migrate to. MySQL is free, but there will be a very steep learning curve, particularly since you are coming from the Access world, and very little, if any, MySQL documentation is geared towards your background.

To package the code, you probably need a version of Visual Studio (probably just Visual Basic). There are a lot of design decisions to make here - will the distributed version connect to your central database, or will your customers set up their own databases? Or will they just run standalone instances that don't allow for multiuser access?

If time-to-market matters to you, stick with the Microsoft products. You could save money going the open source route, but there are a lot of technologies to learn if you go down that path.
posted by b1tr0t at 10:37 PM on April 26 [1 favorite]

How can I get the report to display the account number as well?

Think about how you model your data. Access can almost certainly handle whatever model you need, but you need to think it through carefully. Switching to a different DB system won't make this problem go away.
posted by b1tr0t at 10:39 PM on April 26 [1 favorite]

Microsoft Access by itself is a pretty full featured relational database, and you can go far learning the principles of relational databases with it. For something like you're describing, the forms and reports available are also tremendously convenient.

I worked at a manufacturer who's IT systems were entirely frontended by Access--with MS SQL Server on the backend. O'Reilly's book on T-SQL is an excellent book teaching you about SQL and relational database theory, and everything you learn can be applied to both Access and SQL Server.

You're next step should be to learn SQL Server, which integrates smoothly with Access and allows you to separate the data (and the structure of the database) from the single program that you're interacting with. This will teach you the basics of client-server architecture as well, and also engineering more robust systems. The risk you're running now is that your Access database will get corrupted and you'll lose everything. SQL Server is an enterprise level database, and you can learn redundancy and replication with it. Another benefit to this method is that you can use Excel as easily for generating reports that are in convenient spreadsheet format.

SQL Server has a free desktop edition, and a freely embeddable version as well, so in terms of productizing your work so far, you're not digging any holes for yourself by moving to it.

I would rethink the plan to sell an Access database. The security on Access is miserable, and you might be better off thinking about a hosted service, where your clients log on to a secure website that you manage. You could think about using PHP and MySQL for this, but there are plentiful Windows/VB hosting deals available as well.

So, regardless, learn 1) relational database theory so that your data is well-structured, 2) SQL Server or MySQL to understand enterprise level databases, and 3) VB.NET or PHP to be able to program the interface and reports to your system.
posted by fatbird at 11:24 PM on April 26 [1 favorite]

MS SQL Server is expensive

To reiterate: there are free versions of SQL Server available for development and embedding that are more than sufficient for your needs. Google "SQL Server Express".
posted by fatbird at 11:25 PM on April 26 [2 favorites]

While there are free versions available, that's a bit like a drug dealer giving you your first hit for free. Once you're hooked, it will seep into every aspect of your (business) life. That might not be a bad thing if you only hang out with other addicts. Personally I wouldn't try to bring anything to market that actually required a vendor to go out and buy a bunch of expensive, proprietary software.

But like b1tr0t said, your problem is not vendor-specific. The problems you need to solve can be handled irrespective of the flavor of database.
posted by Civil_Disobedient at 7:08 AM on April 27

Thanks guys, you've given me a lot of helpful ideas.
posted by wayofthedodo at 8:35 AM on April 27

Personally I wouldn't try to bring anything to market that actually required a vendor to go out and buy a bunch of expensive, proprietary software.

If you happen to have the skillset to build applications out of free software, then that is fine. If you don't already have that skillset and are older than 18, it probably makes more economic sense to pay for the commercial building blocks.

There are a lot of people out there with very limited development skills who have built businesses around the Microsoft platform.
posted by b1tr0t at 6:40 PM on April 27

I like Microsoft stuff and that's the platform most of my work is usually based around, and the outcome that b1tr0t happens more often than not, but I think it's only fair to note that it's not entirely rare for a person with very limited development skills to have completely screwed their business by basing something around the Microsoft platform, too.

I've personally witnessed exactly the catastrophe Civil_Disobedient is implying, when the client of the developer of a hodgepodge cobbled-together app is doing an IT audit and suddenly realizes that they need to pay for a SQL Server Enterprise license because the usage doesn't comply with the Express license, or realizes that a proposed architecture will require a hundred times their current volume of Active Directory licensing and so it kills every large sale.

So wayofthedodo, do make sure you understand the implications of basing your product around the "Lite" version of a database or other tool, especially if it would be something you'd essentially need to redistribute with your own product.
posted by XMLicious at 7:19 PM on April 27

That's why I mentioned that SQL Server is expensive. It isn't clear that the OP actually needs SQL Server. If they do, they should build their business model around the cost of the retail product.

On the flip side, MySQL is free, but it isn't really the kind of thing that you can learn in a weekend, particularly if you have little or nor formal database background. Your time costs money too.


Incidentally, I don't like Microsoft stuff very much at all. It happens to work very well for a lot of people, just not me. I would solve the problem with MySQL and either Java or Ruby on Linux, but I'm not the OP.
posted by b1tr0t at 7:25 PM on April 27

Hmm... you think that SQL Server is far easier to get a hold on than MySQL? I don't quite see why... I definitely didn't grasp the basics of MSSQL in a weekend myself, though admittedly I was coming at it from a strictly client-side web background rather than having experience with Access.

Have you used MySQL Workbench? They've only had it prominently up there on the site in the last couple of years, I think, but that's because it was previously an independent project which I forget the name of at the moment.

The clicky-clicky-drag-and-droppy development tools are out there everywhere in OSS these days. Granted, they are still usually slicker and often more repletely documented when they're coming from a commercial vendor, particularly Microsoft, but the average gap is much smaller these days than it was even three or four years ago. In some problem domains in my own specialty, web CMS, I would say that open source solutions like Drupal are the easy solution for the developer of limited skills, far more so than any of the commercial solutions of equivalent robustness I have experience with.

I guess all I'm reacting to is the notion that the commercial route is likely to be the easier or cheaper or more business-friendly way. I think that was true at one point in the past but that was quite some time ago. It's been years now that commercial outfits, even Microsoft itself, have been setting things up to imitate some aspects of open source projects, as well as open source developers have been coming down from their high horses and making features and entire products for what they'd previously seen as the unwashed masses.
posted by XMLicious at 7:59 PM on April 27

Microsoft makes some fine stuff, and most of their stuff works well with the rest of their stuff. I don't have a particular problem with Microsoft's stuff (except Windows, which I think is weak sauce) but with their licensing.

Microsoft stuff is expensive, and it tends not to play nice with anything but Microsoft stuff. Picking a Microsoft product will often cause you - and, worse, your customers - to become embroiled in a Microsoft licensing avalanche, because once you're using one Microsoft tool, the path of least resistance is always going to be using more of them. They hunt in packs.

Open source stuff, on the other hand, usually plays nice with everything except Microsoft stuff. This is because open source stuff, being inherently diverse, needs to use standards to get any kind of inter-operation happening, and needs its implementation of those standards to be fairly consistent and complete. Microsoft, in contrast, still has a fundamentally embrace-extend-extinguish approach to any standard that might threaten to let its users avoid its savage vendor-lock-in business model.

Open source stuff is also generally easier to learn in depth than Microsoft stuff, because although Microsoft's documentation is often more accessible and/or complete, with open source you get to read the source code and see exactly how the bits and pieces are doing their various jobs. It's my personal opinion that occasionally being forced to "waste" a bit of time digging through other people's source code in pursuit of some piece of behavioral obscurity is one of the most effective ways to broaden your own programming skills.

So if your aim is to knock out a quick app and make a buck off it as fast as possible, the Microsoft route will probably suit you better. But if you want to learn how to make something good, and you're willing to put in the time required to get good, then making it your policy to prefer open source tools will get you to that goal with much, much less pain and suffering overall.
posted by flabdablet at 5:52 AM on April 29