What language is best suited for my situation?
July 24, 2009 5:14 AM   Subscribe

I have an Access db that I'd like to write into a stand-alone program, but I need help from the hive choosing the best language.

It's a bank account and budgeting db that some friends and family have expressed interest in, so after a while of testing, feedback, tweaking, etc, I might even want to try to market it for sale one day.
Here are my considerations:
A) I'll definitely want an attractive looking GUI. Wouldn't want anything "Windows" looking.
B) Ease of packaging for distribution, and installation, are of course very high priorities. Windows-only would be fine.
C) My training/experience level: some simple VBA (what's in the Access db), recently took a non-GUI C++ class, and could take an Intro to Java class this semester if I end up using Java for it.
D) I had thought I'd use some kind of a SQL database back-end (am I saying that right?) as a place for the data, because I have the working Access db to use to model it after, and I'm familiar with a little SQL.
E) I am aware that I need to be mindful of the libraries' licenses if I want to eventually be able to sell the program.
F) For now, for sure, I'm not interested in any web-based functionality or for it to run in a browser.

And I guess I ought to ask also, does it appear that I might be missing something important?
Thanks very much.

*There might be some who want to tell me I should leave it in Access, but that's not what I need, so please don't.
posted by atm to Computers & Internet (17 answers total) 8 users marked this as a favorite
VB 2008 Express is probably the obvious choice, particularly since you've used VBA and it's an Access database.

Access may be the best database choice if it's going to be a standalone app, and won't be networked. Otherwise you may have to install a database server (e.g. SQL Server Express), and that's not particularly desirable in this sort of product.
posted by le morte de bea arthur at 5:25 AM on July 24, 2009

For now, for sure, I'm not interested in any web-based functionality or for it to run in a browser.

Well, we've reached a point where it's a lot easier to write this kind of thing using a web framework than with any "form maker" or "4GL" thingie - modern web frameworks provides all the application scaffolding you'll ever need, and HTML/CSS with a sprinkling of JavaScript (preferrably using jQuery or a similar support library) makes the rest trivial.

That doesn't mean that you have to actually deploy it behind Apache or somesuch; just use a "fractional horse-power" HTTP server (e.g. the debug servers that comes with your framework of choice) and a standard browser as the front-end.
posted by effbot at 5:26 AM on July 24, 2009

Take a look at SQLite as a file-based DB backend. This is what Firefox, Safari, Adobe, and a bunch of other folks use. Then you can use whatever language you like. You could even make it a web-based application if you wanted, or just keep it stand-alone and use any one of a million open-source DB libraries for C/Java/Python/etc.
posted by Civil_Disobedient at 5:34 AM on July 24, 2009

Even though you don't want to leave everything in Access, you'll still need somewhere to store the data. If the data is stored in a Microsoft product (Access, SQL Server), a Microsoft technology would probably make it easier. Besides VB, there is also C#. Both use the .NET framework unlike Visual C++. Personally, I prefer C#.

You could also use a cross-platform framework, like Qt. Qt is written in C++, but there are bindings for other languages like Python. Qt apps will work on Linux, Mac, and Windows.

I don't think it's necessary for you to take the Java class if you want to use Java for this app. You probably won't get too detailed in your class and it'll be easier to learn it on your own sooner. There are also other languages that you'll probably be more comfortable in already.

I'm not sure what you mean by you don't want anything "Windows" looking. A GUI application in Windows will look like a Windows GUI application. What did you mean by this?
posted by mathlete at 7:18 AM on July 24, 2009

You can use SQLite with ADO.NET. There's a very pretty video on how easy it is. I prefer C# because it is pretty and there a ton of examples using C# and ADO.NET.
posted by geoff. at 7:21 AM on July 24, 2009

VB 2008 Express...

Yes. The Express edition (free download, not crippled, just lacking higher-end features) of Visual Basic is almost definitely the right choice for you given your needs and experience. It's powerful enough for anything you want to do, and the syntax, at least, will be familiar. Other Visual Studio Express packages exist, but C# and C++ will just give you efficiency and language features you don't need at the cost of more complex programming and the learning curve of an entirely new language.

The .net platform (is that what they still call it these days? I can't keep track) has WPF for making attractive GUIs.

SQLite can be easily used with VB.net.
posted by whatnotever at 7:36 AM on July 24, 2009 [1 favorite]

I have used Ruby and its Win32-OLE library to control Access from within windows, hooking it up so it talked to Quickbooks over its xml-rpc interface. Check this out - it may help (and ruby has a great SQLite library, in case you want to integrate some of the other suggestions here).
posted by amrangaye at 8:12 AM on July 24, 2009

Best answer: If you want to stick with the MS route, and it's a single user, single threaded application, you should look into MS SQL Compact Edition. Just be wary of growing your DB over 2GB or trying to use it in a multithreaded non-transactional environment. But for a general desktop app, it works just fine.

And as mentioned above, SQLLite works well. I've used it in the past and had good results.

If your data isn't relational, you could also look into BerkelyDB. It's extremely fast. And as free as SQLLite.

Even if it's in .Net, and you don't want it to look like a standard Windows app, there are a lot of frameworks out there to significantly change the look and feel. I don't know how many are open source, but we've used a few here where I work.

Also... WPF is worth looking into if you're on the .Net platform. It would be free, and entire thing seems to revolved around eye candy and data binding.
posted by krisak at 8:59 AM on July 24, 2009

Response by poster: Thanks for the replies everybody. Some thoughts, in no particular order.

-When I said I didn't want anything "Windows-looking" I meant I want a different, fresher looking GUI.

-Between VB.NET and C#, I think I'm leaning toward VB.NET. From what I've read, while it might not be the more "Professional's Choice" of the two, it might be a better fit for me and should produce a quality-comparable product, yes?

-This thread brings up something interesting. It would be nice for the user to have the option to also install the program on his/her second computer, so that he/she could run it from there against the same data. That might be a good feature, even if I'm not ready to tackle it yet. What is the name of that functionality? Can I build it as a one-seat only now but plan and prepare and code so that I could add multi-seat ability later? How would that be done?

-In light of the general opinion that Java is not necessary to meet my requirements, and its steeper learning curve vs VB.NET, I won't use Java and won't be taking the class. Thanks for the help.

-WPF looks good.

-What about obfuscation and VB.NET?

Thanks again everybody.
posted by atm at 12:47 PM on July 24, 2009

Best answer: It would be nice for the user to have the option to also install the program on his/her second computer, so that he/she could run it from there against the same data. That might be a good feature, even if I'm not ready to tackle it yet. What is the name of that functionality? Can I build it as a one-seat only now but plan and prepare and code so that I could add multi-seat ability later? How would that be done?

You're talking about having the database be accessible by multiple clients? You're going to run into some potential problems with that because your app needs to be written with concurrent modification in mind (i.e., more than one instantiation of the app is editing the database at the same time — one from computer A, one from computer B for example). This is a pretty thorny problem, and given your lack of formal training, probably doubly so for you. I'm not saying you can't do it, I'm just saying it's hard.

You'd also need the database to be accessible over the network (note: putting the database files on a shared/networked drive and running two copies of the database software, both pointed at the networked drive's files, is a Bad Idea™). This is generally not a capability you can expect home users to be able to provide (or troubleshoot effectively). Maybe if it were limited only to a LAN...
posted by axiom at 1:29 PM on July 24, 2009

-This thread brings up something interesting. It would be nice for the user to have the option to also install the program on his/her second computer, so that he/she could run it from there against the same data. That might be a good feature, even if I'm not ready to tackle it yet. What is the name of that functionality? Can I build it as a one-seat only now but plan and prepare and code so that I could add multi-seat ability later? How would that be done?

I take it you don't want to allow the user to save their data to a file and just open the file wherever they have the application installed?
posted by mathlete at 2:16 PM on July 24, 2009

Response by poster: Axiom,
Would it simplify the coding (or would it even be possible) to not put concurrent modification ability in the first version, but to write the program in such a way that an overhaul wouldn't be required to add concurrent modification ability later? As for sharing the data over the network, ideally the concurrent modification ability would work as soon as the user was able to successfully get File Sharing between the computers working (and while I might provide instructions on setting up File Sharing, I wouldn't troubleshoot it). What do you think?

Do you mean like opening a Word file launches Word automatically? If so, I hadn't thought of that. I had in mind that the user would open the program and it would be automatically connected to its data. In my comment that you referenced, I was talking about being interested (in the future) in being able to allow users to run the program from multiple computers while accessing the same database. Axiom used the term "concurrent modification", which seems like it describes what I meant.
posted by atm at 3:13 PM on July 24, 2009

Best answer: Do you mean like opening a Word file launches Word automatically?

Yes, that is what I meant.

I think what you want to do is difficult. You could have a central repository, like say on the internet. You could have a server application running that holds the data to which the clients can connect and access the data. But it doesn't sound like you want to do anything like that.

You certainly could leave out this feature and implement it later. You will probably want to create a class to encapsulate your operations on the data so that you could change the implementation later. Something like that; it's hard for me to say without knowing more and thinking about it longer.
posted by mathlete at 4:50 PM on July 24, 2009

Best answer: The Microsoft .Net languages are somewhat cross-platform thanks to the Mono project. I think it's only C# and .Net 2.0 but various bits and pieces are at various stages and several major projects and products have been released or are compatible with Mono running on both Windows, Linux, and to an extent, Mac thanks to a Cocoa Wrappers project. You don't get many of the new/cool/etc. features like LINQ, but you get the basic functionality and the ability to sell to growing markets.

If the above paragraph was gobbledygook to you, then spend some time reading the Wikipedia entries on anything I capitalized.

There's some important design considerations that you need to take into account in regards to the data storage for your program and a few of them were touched on above. MS Access abstracts most of these decisions away. I don't have much experience developing away from SQL Server, but in case it wasn't made clear in the above conversations, you have a few choices.
- Use an embedded SQL database that the program will create within it's file structure when it's initialized. You may be able to code logic in to start/choose different databases or to limit the user to viewing data for their login only, however, it's usually difficult to port information between computers because there usually isn't a single 'file' like an excel spreadsheet but instead there's a required export-and-import process. Additionally, the use would probably need to be limited to the single computer it's installed on and it could not be used by other people even on a network.
- Create and use a binary file format similar to the way double-clicking on an access database or a word file starts the program... while keeping in mind that two people editing the same file at the same time will cause a massive amount of fail in the form of data corruption.
- Use a centralized, small implementation of SQL Server or another network database, but then you may get into concurrency issues and record locking issues as people use the program from different places. You would need to check for and display updates to data constantly, or otherwise do the programming work to keep two people from changing the same record at once. You also need to make it easy to configure on an ad-hoc network, which may be difficult with a cross-platform implementation.

Most people who are buying applications these days that deal with personal finance want the ability to access their data remotely. Your competitors, no matter what platform they're on and how novel your user interface or personal finance strategies are, are already working on this problem. You WILL need to release a way to publish the information to a place that people can get to with their smartphones. I have no use for personal finance software that I can't access from the store when I need to know how much is in my checking account.

The reason software is so expensive to buy is that it's difficult to do. My suggestion would be to first get a good handle on the basics of software development in modern languages with databases and/or file formats, and THEN re-examine your idea. There are a few good books out there on porting projects that started in Access... can't think of one right now, but I read one about seven years ago, so I assume they're still around.

And really, even if you like it, you would be doing yourself a disservice to use Visual Basic much... it limits what you can do in the future, what you can integrate with, and permanently cripples your ability to learn other languages. C# is easy to learn and uses a much more 'standard' programming syntax shared by other languages -- if you're going to go through the trouble to learn to program, why would you want to form design habits that can only be used with one company's language or product line?
posted by SpecialK at 8:06 PM on July 24, 2009

Response by poster: What about this?
Instead of allowing multiple instances of the program to access the db at once, only allow one at a time? On launch, have the program check to see if another instance has the db open and don't allow a second instance to launch until the instance holding the db open has been closed. For example, Man tries to launch program on ComputerA. Gets message saying program cannot be launched on this computer because the program is open on another computer. Man has to go to ComputerB to close the program, then returns to ComputerA and is now able to launch the program.

Also, I've changed my mind on the language to use. I'm going to bite the bullet and use C# instead of VB.NET.
posted by atm at 10:03 PM on July 24, 2009

Best answer: atm, yeah, that will work and be much, much, much easier than letting two programs access the data at once. Basically, when dealing with concurrency, you have to create "locks," which let one program take control of some piece of the data for some period of time in which no other program can touch it. Otherwise, two programs try to change something at once and at least one of them gets confused when the data it just put in isn't there any more. The difficulty is in creating locks that are fine-grained enough that neither program is locked out for too long but coarse enough that you don't miss any cases where you should have a lock but don't because you made them too fine-grained.

What you've described is putting a lock on all of the data for the entire time one program is open. Simple and effective. That, along with user-configured file sharing, is definitely the easiest thing to program, and it is good enough for most home users. Later, if there's demand for it, you can add more fine-grained locks for actual concurrent access and/or networking code to handle sharing data automagically.

Watch out for stale lock data, though. If your program crashes without releasing the lock, any subsequent launches will tell you the data is in use. Or maybe are user puts one computer to sleep that has the program open and then is confused when running it on another machine tells him that the program is running elsewhere. You might have run into this with other applications; Firefox sometimes complains to me that my profile is already in use, and I have to go hunt down and delete a lock file. Not user-friendly, that.
posted by whatnotever at 7:18 PM on July 25, 2009

Response by poster: Thanks Whatnotever, that's good news.
I agree that for a home app, one person (computer) in at a time is perfectly acceptable.
posted by atm at 7:38 PM on July 25, 2009

« Older Nice Things to Do for a Stressed Partner?   |   Note Tab Newer »
This thread is closed to new comments.