Big question: how comfortable are you with any of those platforms? If the data is in MS Access (and you think it will always be small enough to fit there), and you know VBA, that would probably be a very good choice. If you have zero experience with Perl, then it would not be a good choice. (My consulting rates are very reasonable, BTW ;-)

OTOH, if you think the app and data will grow large (multiple users, lots of data), Access has a very low ceiling, so you may want to consider SQL Server. Again, as for the application's UI, it depends very much on what you can deliver in time. If you are comfortable with browser-based apps already, then Perl is an excellent choice, in part because of the excellent tools that are available for it (templating systems in particular).

HTH

VBA might be a reasonable solution. Compiled Perl is generally not the greatest. Due to the way that Perl works, compiled Perl is generally forced to include the Perl executable in addition to the program. This forces the compiled program to be rather bloated and it doesn't run significantly faster than non-compiled Perl (from what I have heard). The only reasons I can think of, off hand, for compiled Perl would be an attempt to hide the source or to be able to distribute your application without requiring people to install Perl. Neither of these reasons seems to fit your needs.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Essentially true. I've used ActiveState and Indigo Perl compilers. Really only useful for

a) hiding your code
b) making it a bit easier to bundle

It should be noted that compiling will inline any modules you use as well, though any external libraries will be kept seperate. This can of course be a major pain if you are getting into moderate and heavy wizardry.

--
perl -p -e "s/(?:\w);([st])/'\$1/mg"

I think I agree with all of the responses here.  However, let me offer a different angle for you to consider:

What do you want to do?

Your posting has a comment in the signature section that reads, "I am a true geek".  If that's so, why would want to do this project in Access, considering the limitations and lack of lustre?  I would imagine that you registered for the Perl Monks site because you're attracted to Perl.  If you want to learn Perl and you're going to be the one solving this problem at work, and it's your decision, why not use this project as an opportunity to learn Perl?  Of course, if you have a tight dead-line and are comfortable putting the whole thing together in Access with a bunch of macros, well alright.  But if you want to have fun and learn Perl, then do it in Perl.

Another angle to consider, one that is less psychological, is what are your long-term data goals for this company?  Is this project going to be a small one and the only one?  Or is this a project that will be extensive over time and will need to tie into many other data manipulation related projects that you envision?  Basically, don't create something that you'll end up replacing later and learn nothing from now.

I'm an IS manager for an office of about 250 employees and four locations.  My vision for our data needs and requirements include inter-networkability and intertwining of all data through an intranet site.  Also, I require stability so that the users can depend upon the system and so that my tech. support is minimal.  I also need easy accessability through the internet in a text based, command mode--I carry a Handspring Visor with a modem to do tech. support when traveling.  These requirements are easily satisfied with a web-based front-end, using Perl for CGI and especially for many behind the scenes processes initiated daily by cron.  I've also filled my requirements with the mySQL database and Linux servers.  The result has been stability all around, easy to learn interfaces, and easy to access data from anywhere we have or open a branch office or whereever a traveling salesman might go.  It also has resulted in minimal maintenance work on my part and I don't have to leave my house (or hotel when I'm traveling) to fix things.

Okay, that's my old man and pop-psychology advice for the day.  I hope it was helpful.  Good luck.

Not having used the Perl GUI interfaces much, I can't speak to that aspect, but I have used Perl to work with an Access database and have had success with that (see Web Deployment Schemes). The Win32::ODBC module is very easy to use.

But if this running on one box and is only going to be accessed through that box, I would consider doing the programming in Access itself using VBA. By programming in access, you can easily incorporate the native forms, queries, reports, etc. into the application. I'd rather code just about anything in Perl than using VB/VBA, but this could be an instance where you could get away with creating a series of forms without having to do a lot of coding behind it.

Most biases aside, I'd be surprised if something basic like order workflow for a small business isn't already available off-the-shelf.

If you want or need to go the build-it-yourself route, you'll find that Perl is very flexible. You can get at Access from Perl via either OLE or ODBC. That said, for simple things VB can be flexible, and you'll have the advantage of a lot of Microsoft-centric books with lots of examples. I would not go with C++. You'll spend too much time fighting the language and environment, and not enough time fighting the problem.

I infer from your PWS reference that you are building a browser-based application. If you were to require a native looking windows UI, VB is probably the way to go. (You can get there in Perl, but the learning curve is steeper and the path isn't as well documented.) But the playing field is more level when it comes to HTML-based UIs.

I do some personal development with Perl CGIs on PWS, and have found no problems moving that code to IIS (and no appreciable problems moving to Apache).

One warning: Microsoft Access is not a multi-user database, and isn't suitable for heavy concurrent access. Don't expect something build using Access to scale well.

As I was reading your post I was in complete agreement until your penultimate sentence: One warning: Microsoft Access is not a multi-user database . . ."? Don't get me wrong, I'm not a disciple of Bill or anything but did you forget to qualify that in some way? Our Academic Computing Offices use it for our custom written HelpDesk and Campus IP Address database applications and they are quite stable and are used by as many as 15 people.

True, It doesn't scale too well, but if we get to the point where scalability is an issue, we'll migrate to MS SQL Server. Granted I'd love to have written them in a Perl/Web Server/RDBMS environment, but that decision wasn't mine to make.

Were you talking in terms of a Perl/MS Access/ODBC DBI environment? I'm not very knowledgeable in that area, so it would be handy to know.

--Jim

True. I should have qualified the statement (that Microsoft Access is not a multi-user database) by filling in some details.

My experience with Access -- or, more accurately, accessing the underlying JET engine through ODBC -- is that it didn't stand up under (simulated) heavy load, and that we could fairly reliably corrupt the repository. Under lighter load, things would seem fine for quite a while, then WHAM! Under single-user load, no problem at all. We suspected a concurrency problem, but with no source to read or debug, we were left at the "we suspect" level. Plus, conventional wisdom at the time was also that Access/JET was not suited for multi-user applications.

This was on NT4.0 in the SP5 timeframe. Something may have changed in the meantime to fix that (there have been at least two service packs, SP6 and SP6a) in the meantime, plus Win2K (and one? service pack there) and XP. I since moved on to relyling on heavier-duty RDMBSs, and haven't reevaluated Access. Nor do I plan to.

From memory (and i will stand corrected) doesnt access load a copy of the entire db onto the local machine (if on a LAN)? Given this, wouldnt there be problems with locking? - Hence the scalability issues

Has the original poster considered Delphi? Its damn easy to use, got a nice RAD env, heaps of DB connectivity modules, is completely compiled, and doesnt need those silly run time libraries. Having said that, there is always Kylix for a nicer environment in which to work.. :-)

As i've mentioned in other posts, I like Postgres and perl, coz its so damn easy to set up, maintain and write code for.

If a web interface is decided on, you have the nice flexibility of a remote access gui, and its easy to make some kind of redundency in your final solution. ie a simple (cheap intel) two machine setup, if one machine fails, just bring up the other machine with the database and webserver on one box.

If you wanted to get really tricky, use one machine as primary, one as secondary, both installed with a webserver and database, and have the primary roll its data across to the standby, so in the event of a failure all the data is upto n min's/seconds old. Easy, cheap, flexible, scalable and sensible for a SME whose data is their business.

ahhhh!, the feature creep!, the damn feature creep..

An excellent free database: Mysql
Look up DBI::MySql for database access. The mysqlMax distribution has support for transaction & non transaction based tables.

----
Zak

If your only reason for using Access is that you can get it for free, don't use it! Use MySql or Postgres. At work, we have several Access Databases that we use to track Trouble Tickets of various flavors. The DBs are constantly corrupting themselves and need frequent restores from tape backup. Don't lock yourself into this sort of mess.

Now that I've said that, what you are discussing is definitely possible to achieve with Perl. I can't really comment on writing traditional GUIs with Perl. But I will advocate for a browser based app.

Browser based apps written in Perl (or other P* language) have been dubbed LAMP, for Linux-Apache-MySql-P|(erl)|(ython)|(HP). They have some nice advantages:

• Price: All your tools are free.
• Scalability: You can build a system that can handle 1 or 1000 clients.
• Server Based: You don't have to install/maintain applications on multiple PCs.
• Rapid Development.
• Familiar interface: People are comfortable using their browsers and tend not to think of web pages as separate applications.
• Modest sytem requirements: I run Linux with Apache/mod_perl and Mason on my dev server - A PPro 200 w/ 96 MB RAM. I just wrote a forking webcrawler and that wimpy box can handle 3 hungry connections continually pulling dynamic content without excessive swapping, more RAM would make things better.

If you go with a browser interface, you should consider using some sort of templating system. Template Toolkit comes highly recommended, I like Mason, and embperl is popular. Some careful use of Super Search should turn up some sound info on your options. You may also want to look at tools like Zope or some of the PHP based site builders.

BTW, I never thought I'd see so many Perlmonks recommend using VB :). I haven't used it much, but it looks like it's got some great interface building tools. If one could hook up a VB interface to a perl app...


TGI says moo

In the process of attempting to install some of our web based intranet business applications (using Apache+CGI Perl+MySQL) in companies with very few, low end computers (typically 1-5 computers running Win9x), I found to my disappointment, that performance is way too bad.

After a lot of attempts to improve performance, I started evaluating the possibility of a Win32 native interface, using the same PERL back-end code. Luckily, I found some interesting tools that speeded up Win32::GUI development, and I am beginning to get much better performance due to the lowering of load on the server.

Here's what I use:

1. The GUI Loft
   by Johan Lindström is an excellent VB work-alike that for designing GUI forms. It creates a Dumped version of the GUI, which you can load up using couple of lines of PERL code (see above URL for details).
2. Johan has also designed another Win32::GUI app called Oasis which works as a neat code and POD browser.
3. I am still using HTML to create outputs (reports, listing etc), for its much easier to format these in HTML than in Win32::GUI. The easy way to do this is to save the HTML to disk and invoke the browser. A neater, but not as easy alternative is to use DDE.
4. PERL has several Template interpreting tools to help generate HTML outputs. I've tried HTML::Mason and Template-Toolkit and settled for the latter - its really neat.
5. Data is stored in MySQL on one of the machines, and called via DBI.

Hope this helps...

- Ashutosh

this whole project is a voluntary free service i am attempting to provide to a small family owned and run niche-market store

With all due respect to my esteemed brothers who suggested mysql or postgres, those both seem like overkill for this particular application.

This is Mom and Pop type business, which I interpret to mean that multiuser access and scalability are NOT serious issues here. Further, Lord Wrath's inclination is to go with VB and Access, which, while not my personal choice for such things (Never ever!), seem from a distance at least appropriate to the job.

So all things considered, before even discussing a possible Perl-based solution, I'd expect a reasonable impedance-match between these particular requirements and a VB/Access-based implementation.

That said, it's less clear why alternative strategies are even being considered. Perl? C++? What seems to be required here is a short development time (so forget about C++), and no development costs (other than time itself). Perhaps the motivation is personal, as in wanting to develop something in Perl?

Developing a solution in Perl could (broadly speaking) go one of four ways:

• Command-line driven (probably not an option, really)
• Local GUI such as Perl/Tk, Win32::GUI, et al (May be more work than it's worth if there's a learning curve involved)
• Web-based GUI, or CGI
• I'm sure there's another option, but it escapes me

While my own preference might be to go with a web-based approach, using Apache, CGI and some sort of simple database accessible from Perl (like, say JSprite, the standalone version of DBD::Sprite, which uses a CSV-like text-based file format--no need for the overkill of even a small RDBMS), I have to say, despite my own personal biases, that I think the VB/Access approach offers the greatest bang for the buck on a project like this.

To wit, If ever there comes a time when the business expands and they need to migrate to a (gasp!) purchased system, there seems to be a much greater likelihood of being able to find a VB programmer who knows Access to decipher what you've done (presuming you yourself are not available), than an Apache/Perl/CGI programmer who is comfortable enough (and willing!) to work with the PC as a platform.

While I am all too aware of the forum we are in (who here hasn't experienced the "When all you have is a hammer, every problem starts to look like a nail" Syndrome), I have to answer your question honestly, and this just strikes me a an obscure application for Perl.

dmm

Just call me the Anti-Gates

Nooooooo....not the free excuse!
mysql or postgres are much better choice and they are free as well.

If you are comfortable with it and that's why you're using it, then so be it, don't use it just because you have a free copy of it lying around.

BTW, Apache is a great webserver...guess what...free too.

Just my $0.02, you can "cat /dev/null > $0.02" if you like...

perl -e 'print reverse qw/o b n a e s/;'

Will they accept a web based application or one with a non-Win32 GUI (if you use Tk for example)?

Serving web-based applications on Win9X is fraught with peril. There are known memory leaks that will prevent high uptimes. An architecture where the Win9X box is both the server & client sounds too fragile to me. How complicated will the startup process be if the box freezes and the end-user has to reboot?

Also what exactly is so special about this business that it can't use something like Peachtree accounting? The basic accounting package costs around $90 and may be all you need.

Then again I have a web based background and I like to be able to reuse my favorite bits of code on other projects. If there is no need to migrate, and you like VB then by all means use it. I would recommend against using Tk or executable perl for a product like this. Perl executables are not at all faster than interpreted Perl. ActiveState Perl takes only minutes to setup on windows so no reason to worry about deploying on systems without perl. Tk is just frustrating to layout and kind of shoddy looking in my opinion, maybe the other GUIs metioned above are better looking, though they are not as well documented as tk.

Best regards,
perl -e "print a|r,p|d=>b|p=>chr 3**2 .7=>t and t"

--
perl -p -e "s/(?:\w);([st])/'\$1/mg"

If you want a quick windows like ap, vb it.

But I can tell you I built a timesheet app for a 30 person company based on Apache and Perl, and I did it because when they asked for it to be based on Access it wouldn't hold up. So I moved it to mysql on NT and then mysql on linux, last week. And new apps and functionalities get requested for this intranet system which can all use past code.

The worst case scenario, I met a sysadmin for a big bank who had programmed everything in access and when he wanted to get away from Microsoft everything and go open source, it just couldn't be done without rewriting all the logic and everything. DB-agnostic, platform-agnostic, and flexible, that's Perl.

I wouldn't use compiled Perl. Probably you should consider what the company is going to be doing in the future and especially if there seem to be things that Perl is good at coming into the project in a later part of the game. I just have had experiences getting painted into corners in VB (though it has gotten better since then) while I know it is very likely I'd be able to do what I need to know if it's in Perl. 'Course how about Java?.. Move SIG!

I think a key point is suggested by the question "Is Perl the right solution?" This implies a unique correct solution, and I agree with others that in this case (heck, in most cases) there probably is no such. Perl will probably give you a good solution. VB(A) could give you a perfectly fine solution as well. Personally, I'd probably use Smalltalk for this application, based on the little I know about the requirements. So as long as you have a clear idea how it should work, I doubt you should hesitate to go ahead and do it however you want.

Be aware that what you are talking about is no small task, take a look at OA and realize that it is the fruit of two+ years work.

-jackdied

Cheers