Take the 2-minute tour ×

I have evaluated several development tools for converting my informix SQL-based app. They are: Genero/4Js, FileMaker, Oracle APEX, VFP, Clarion and Access 2007. I have a CRUD pawnshop app (see video-demo www.frankcomputer.com) This app centers on customers who pawn, sell or buy merchandise. I need to have one CRUD multi-table form which displays one customer master on the top half with all of the customers associated items which they have pawned, sold or purchased on the bottom half. Can CRUD be accomplished from within one form in Access? The main reason I'm leaning towards Access is because of its integration with Excel, Word and other modules, plus many people have it and experienced using it, plus it's Microsoft. Can anyone who has developed apps with Access tell me if I can mimic my INFORMIX-SQL based app and what limitations does Access have? Also, can a touch-screen POS front-end like Microsoft Dynamics be used or are other POS application generators/rapid development systems available to re-write my current app?

share|improve this question
What problem are you trying to avoid by switching? –  JeffO Sep 7 '10 at 13:06
If you had started coding this back in March when you first asked this question, there's a good chance you would have finished by now. stackoverflow.com/questions/2360786/… –  Gary Myers Sep 10 '10 at 6:23
@Gary: Perhaps, but I first wanted to carefully evaluate each product and get opinions from their experienced developers before committing time, money and effort. I wouldn't like to invest all that to then discover a critical limitation! –  FrankComputerAtYmailDotCom Sep 11 '10 at 17:55
add comment

1 Answer

up vote 8 down vote accepted

I suspect if you ask Foxpro developer, they would tell you that's the best tool of choice.

And I'm sure if you ask a filemaker developer, they would tell you to choose their tool.

So much of the question is, for the most part if you ask an access developer, that developer would also answer yes.

I would be hard pressed to imagine that all of the above tools you mentioned above, all of them have the capability of displaying information from more than one table in a screen. That's pretty much a requirement for any development system today. So in a nutshell, you're really asking the wrong question here.

I don't think the question is do they have the capability of displaying information from more than one table. They all can do this. Perhaps a fair question would be how much work and how well does each product slice and dice together these multiple tables?

In access you place text boxes and controls on a form, and to display related data, you can you place a control called a sub-form control. This approach allows you to model this classic can typical master to child record table relationship, and do so without having to write one line of code.

And of course you're not limited to one to many, but you can actually insert two sub forms side by side, and have a one to many, and in turn have the 2nd sub form control display many more records from that second table.

Here's a screen shot of what I mean:

alt text

In above you have one main record at the top with information about donation date and event. On the left you have a list of people and their donation amount (one to many).

Then on the right side, for each person, you have the donation amount split out into multiple accounts. (and the green box shows red when the amounts don't balance).

So, the above creates that classic accounting problem that just about every accounting package from Quickbooks all the way up to hi end accounting packages have done from day one when splitting out funds to multiple accounts.

The above form has very little code in it, and most of the relationships and setups and filtering and displaying of the child records is all handled automatically by access.

So at the end of the day, I'm pretty much of the view that all of products you mention above are capable of modeling and developing these types of screens. And, they all will result in an screen and user experience that would be relatively similar to what you have now.

Now course I'm a biased towards access, and I believe that I can build screens like the above quicker, faster, and with less hassle less code and effort than most of the other products you mentioned .

However, at the end of the day what platform and tools you use and find as appropriate is certainly not going to be centered around the ONE QUESTION and ONE CONCEPT that you have need to display multiple pieces of information on a form for multiple tables. As mentioned, this is gonna be given for any modern development system, including web based development systems.

Other considerations and factors is what type of reports, and outputs to you need? Do you need his column are reports, or do you need to send an invoice style forms type report to a printer that's preprinted invoice forms. I think these are bigger questions then your current question.

The real question here's not can any modern development system display multiple pieces of data on a form, they all can. The REAL factors and issues here are what platforms, hardware requirements, and systems do you need the software to run on?

So the issue is will some of the locations have multiple users? Will some of the locations need secure backup or some type of encryption? How do you plan to issue bug fixes, and updates to the next great version of the software?

Other issues are how many developers will you have working on this. What kind of distribution method will you use for the software. What kind of support infrastructure will you have to give customers support and installing the software. So, this list goes on and on and all these issues dwarf the question about the ability to display multiple pieces of information on one form.

In addition to all of the above issues, you need to consider your own training and skill set in development of software. To really master any software development system, you need to invest a considerable amount of your own time to learn. While I think the access is a very good RAD (rapid application development) tool, I will actually say that access has a considerably larger learning curve then say that of VB6 for example.

Choosing a platform is very much like a marriage, you have to invest enormous amounts of your time (months, and even years) to really learn and become proficient at developing software with such a system .

If you're jumping into a new set of tools, then the following list of skill sets needs to be taken into consideration **:

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)  

Stage 6 Master (has internalized X, knows when to break the rules)  

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)

One should NEVER attempt a project with a team consisting with Stage 3 or lower people. (** Page-Jones, Meilir. "The Seven Stages of Expertise in Software Engineering", American Programmer, July-Aug 1990).

So you just can't jump into a new tool and expect to be proficient at developing complex applications. I have an article here about converting a legacy application into ms access .

There are some great lessons in this article:

Notes on Conversion of a Pick (Multi-Value database) Application to a Relational database system. http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000003.html

Good luck in whichever platform you choose.

share|improve this answer
very nice answer –  CodeSlave Sep 7 '10 at 14:06
Yes, opinions from experienced developers of each product I have evaled are that their product is the best. However, I first wanted to carefully evaluate each product and get opinions from their experienced developers before committing time, money and effort. I wouldn't like to invest all that to then discover a critical limitation! OK, so your telling me that I can have multiple sub-forms belonging to different tables on the same form. Now, when one of those tables is active, can I have controls for: Querying, Next, Previous, Adding, Updating and Deleting rows on the current list? –  FrankComputerAtYmailDotCom Sep 11 '10 at 18:01
Yes, multi-table views on one form is tip of iceberg. Things like event/field triggers, multi-threading aware and other abilites come into play. My CRUD app basically records customer pawns, buys, sales and repair transactions. Tickets for these transactions are printed on blank, 3-part, half-page continuous forms. With pawns, customers periodically pay intrest or redeem the merchandise. The app calcs intrest due and redeem amounts. The transaction is then updated. Totals for each type of transaction are updated and cash register can be reconciled. Several gov't reports too. –  FrankComputerAtYmailDotCom Sep 12 '10 at 0:00
Yes you can add/delete/filter records in those sub-forms. (with the built in UI, or your own code). Access by default filters and allows adding of records and setups the column for relationship without needing any code in sub-forms. Most products in the marketplace allow for the filtering and editing you ask and differences center around how much code is required to build such an UI (other systems don't have sub-forms). Cascade deletes are nice since deleting the one batch record in above form will automatic cascade delete data two levels deep without any additional coding on your part. –  Albert D. Kallal Sep 12 '10 at 10:08
OK then consider me a step 2 "Aware" Access skill set. I've had Office Enterprise 2007 on my system for some time now and tinkered with it. Should I start out with Jet or SQL engine? I'm experienced with Informix's SQL implementation. What's the best source for learning how to put together a prototype for my app?.. I liked your article about the Pick to Access app conversion. –  FrankComputerAtYmailDotCom Sep 12 '10 at 14:06
add comment

Your Answer


