The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS Access Equivelent

I need to create a big database application with multiple tables, joins, lookups, forms, reports, basically, I need to be able to do everything MS Access can do - however, I want to sell my app when I am done - as an EXE, that can be deployed and installed royalty free.

Anyone know of a good language for doing this?  I want to make it quickly, and I don't want to hand-code all these different reports, and forms, etc, I like how MS Access creates the templates and easily renders the reports.

Any thoughts?
DB Need
Friday, November 04, 2005
From what I can see, I think FoxPro might be able to do what you need.
Stephen Caldwell Send private email
Friday, November 04, 2005
First, I don't think there are any licensing restrictions for distributing access databases. You can't restribute the Access program itself, but the databases it produces are OK as far as I know. Then just use ODBC to connected to your DB.

Or ... if you want more functionality and power ...

"Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, redistributable version of SQL Server that’s ideal for client applications that require an embedded database, new developers learning how to build data-driven applications, and Web sites serving up to 25 concurrent users."
Developer #13
Friday, November 04, 2005
Access technically can ship a royalty-free runtime, so you can check that out.  The biggest problem with using the runtime version is actually getting it installed - I haven't tried it so I can't vouch for it, but I've heard of problems with a) installing custom DLLs and ActiveX controls, b) installing it on systems that already have some version of Access.

If you're willing to put up with the deployment hassle, this is definitely for you.
pds Send private email
Friday, November 04, 2005
Berislav Lopac Send private email
Friday, November 04, 2005
The old MS Office Developer editions (97/2000?) used to let you compile Access applications into a single EXE. I don't know whether they still do, but that's worth checking out.
Nick Hebb Send private email
Friday, November 04, 2005
I know SQLite, but I'm looking for the whole environment, report generation and all - kind of a no coding approach. 

FoxPro may be close.

Where can I get my hands on those earlier versions of Access???

Friday, November 04, 2005
==>The old MS Office Developer editions (97/2000?) used to let you compile Access applications into a single EXE.

Not quite true. They packaged up a runtime version of the Access .exe and related files, and included your .mdb in a distribution where you basically had:

Access (Runtime version) and

As far as I'm aware, it *never* has had a single .exe file .
Saturday, November 05, 2005
> Not quite true. They packaged up a runtime version of the Access .exe and related files ...

That's right. It's been a while.
Nick Hebb Send private email
Saturday, November 05, 2005
You can still distribute the runtime of Access for free with Access 2003, but you need to buy the Developer Extensions to do that.  There are some issues with digitally signing code with Access 2003, but nothing insurmountable.
Andy Noyon Send private email
Saturday, November 05, 2005
Most of my applications are delivered to clients, and I use the access 2003 runtime. The runtime is just that, a cut down version of ms-access that you include with your applications.

With the developers edition of office, you get a resulting MSI install, and it installs like any standard windows program. My clients do not need to have, or have purchased the runtime. Thus, this is a royalty free distribution method.

As for a single .exe file, well that idea has not been around for a good amount of time in windows…..

The runtime package is about 33 megs in size. I usually package it separately, and thus then updates to my software (bug fixes, addition of new features etc) is usually VERY small, since they are then only getting a new mde (front end). In addition, I have  a “update” button in my applications that downloads, and installs updates from the web when needed..

The change from the a2002 to the a2003 is that the package wizard has been simplified quite a bit (I like it a lot more). However, the new a2003 does NOT package any 3rd party activeX controls anymore (the old package and deployment did).

However, the additional activeX controls that you *might* use are included. (calendar, listview, treeview, and few more I can’t remember). However, most ms-access developers avoid 3rd party controls at great cost!!

Further, to be honest, the developers edition really only gets you the rights to do distribute, since if the user has existing versions of ms-access installed, then you can get a good deal of problems. So, if you are looking of a reliably install using ms-access, then in addition to the developers edition of ms-access, you want to drop my

The above scripts will give you a resulting reliable commercial install, and not break the target computer. They are excellent.

And, as a few others have mentioned, FoxPro would be another really good choice here.

And, there is VB6 too….

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Saturday, November 05, 2005
>edition of ms-access, you want to drop my

should read "drop by"

And, if you look a few posts down, my question about "drop down menu" has some screen shots of what a runtime application in ms-access often looks like....

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Saturday, November 05, 2005
Sunday, November 06, 2005
Delphi for development.  Very fast, easy to learn, great forms designer, and you end up with a single .EXE without a massive runtime.

MSDE for deployment.  Client/server performance and stability, and you can scale the app to SQL Server for larger installations.

Use ReportBuilder or another 3rd party report writer for easy-to-create reports.
Karl Perry Send private email
Sunday, November 06, 2005
" I know SQLite, but I'm looking for the whole environment, report generation and all - kind of a no coding approach."

FileMaker Pro?
Berislav Lopac Send private email
Sunday, November 06, 2005
Perl with MySQL?  Perl has got some nice report generating features.

As soon as you add a 'commercial' database, the 'stand-alone' exe file thing becomes unrealistic.  And do you want to be responsible for providing application support AND DBA support?
Monday, November 07, 2005
RealBASIC occurs to me too -- the 'Pro' version provides an ODBC interface to any ODBC compliant database -- Access or MySQL or beyond.

The report generation thing is difficult, I admit.
Monday, November 07, 2005
Yes, reporting is the toughest part.

Delphi is REALLY expensive for what I want to do.  I was thinking along the lines of $100 to $500 for the development environment.

I actually tried REALBasic, and it wasn't too bad, but again, reporting wasn't that quick.

Here's a program I found that almost does exactly what I need, except the database isn't powerful enought: Database designer.

It quickly creates tables, forms, and REPORTS. Then you can make your DB an applicatin for distribution!

However, the database just isn't powerful enough, as I said, no joins and lookups, etc.
DB Need
Monday, November 07, 2005
If you dont want to code too much, you've got to pay the price.
I second the filemaker suggestion.
Masiosare Send private email
Monday, November 07, 2005
Funny thing is that Filemaker was my first entrypoint into the programming, back there when it was still called FileMaker II (it had a strange versioning history: there was the original FileMaker, then FileMaker II, then FileMaker Pro and only then the versions started -- the latest is Filemaker Pro 8). Later I got out of software development, and when I returned it was on a completely different platform (Web), but I still fondly remember FMP, which was an excellent self-contained user-level database when I last looked at it (at version 5).
Berislav Lopac Send private email
Tuesday, November 08, 2005
" I want to sell my app when I am done - as an EXE, that can be deployed and installed royalty free."

A Delphi front end to a good quality embedded SQL database might seem a lot more expensive than something like Filemaker. 

But unless I'm mistaken (and I could be) Filemaker's royalty free runtime covers only single-user (i.e., non-networked) deployments.  If you're deploying an app that's going to be used on a network or by more than user at a time then they may need to purchase Filemaker Pro licenses.  And after that they'll still be left with a less capable, less robust db solution than something you would be able to build completely royalty free with Delphi.

I suspect Filemaker is a good solution if your app is going to be single-user only, but do some homework to make sure licensing fees aren't a problem if you're deploying a multi-user app.
Herbert Sitz Send private email
Wednesday, November 09, 2005
I'm guessing that you don't want to write code, but rather build applications (as much as possible) without creating programs in Visual BASIC, C, JAVA etc.) since you want an 'Access-like' or more appropriately, a programmable database environment as your development platform.

If you're developing relatively straight-forward business application systems for the small business market or a narrow niche product, a programmable database environment is certainly a reasonable approach. However, understand that the database and hence your applications can't scale easily when it is written in one of these products. When users or transactions rise your application may fail under load. Thus, it could result in a later rewrite, this time using a procedural language and industrial strength database to obtain a higher performance level from the underlying application.

You have several options, including Access itself, as there is indeed a way to create and distribute runtime-only versions of ACCESS applications. But be aware that you'll likely reach your performance limitions more quickly when using ACCESS. I've seen examples where ACCESS has not scaled well. Meaning you may forced to 'port' your application out of ACCESS into an SQL Server, mySQL or other similar, more robust database environment, sooner rather than later.

Then the question becomes must the Access front end be rewritten in a proramming langugage and the answer is generally speaking, yes. However, there is an exception: Continue using ACCESS as a front end to a server based database (there are books about this, one being Access and SQL Server Developers Handbook by Viescas, Gunderloy and Chipman, Sybex, 1996).

What about the other Access-like programmable database environments? Well, there are several choices to consider. First, there's Microsoft FoxPro (if you want to stick with a Microsoft Solution). There are examples of several strong commercial applications using FoxPro including TurningPoint Accounting Software from RedWing and pcMRP from Software Arts.

There's also FilemakerPro Advanced and Alpha. These, along with FoxPro have long track records (actually all pre-date Access as end-user databases). Filemaker runs on both Wintel and Apple Mac environments and has a sever backed version that you can seamlessly port your applications onto and will better support workgroup sized requirements. There are scores of commercial applications in FileMaker. Filemaker can even create Web-based applications. This option requires a dedicated, web attached server for purposes of running your application.

Be aware that the entry level versions of the products I noted here aren't suitable for development work for they are crippled versions, unable to product runtime only or distributable, standalone versions of your application. 

Now as for reports and report writing in general, this becomes another matter entirely. First, the programmable databases include WYSIWYG report writing as the way for the developer to create end user reports and out of the box, there are none -- you must create them. Alpha has a built-in report writing genie (the genie is akin to the Access-like wizard) which is a nice touch and perhaps that may be the deciding value proposition that you'll want to consider in your ultimate platform decision.

Indeed you can purchase report writers that  incorporate 'Access-like' user interfaces. Thus, they use Access style 'Wizards' to generate reports at the hands of an end user. These are products that are separately licensed and must be distributed as an integral part of your application. Crystal and Acutate dominate, but there are many others out there as well.

Hopefully this has been a thoughtful and balanced reply to your question.
r. w. starinsky Send private email
Thursday, November 10, 2005
I forgot to address another issue -- that of a single 'exe' file. There is a shareware product that works well for the purpose. I've seen it used in conjunction with Liberty BASIC programs which must be distributed as a code file along with several executables that in turn process the code.

While the name escapes me at this moment, if needed, I'll be happy to dig it up.
r. w. starinsky Send private email
Thursday, November 10, 2005
>But be aware that you'll likely reach your performance limitions more quickly when using ACCESS.

Compared to what? (I love these blow hards that make these statements, but leave the “what” part complete open!!).

Ms-access is no different then using c++, or VB when you use it with sql server.

Further, VB, or c++ is not going to be any faster them ms-access, (tabling about using JET or sql server for that matter). The question becomes what data engine you use, but that don’t change the fact that you can continue to use ms-access as the front end to sql server. If you out grow JET, then simply link the tables to sql server.

Further, when you suggest to move ms-access to sql server you sound very stupid and silly. You do realize that sql server does not let you build forms right? You do realize that sql server does not let you build reports…right? So, when you drop ms-access, exactly what will you build the application with? What will you use to write the UI and write code with? (it is just plain stupid to say drop ms-access, and use sql server). That is like saying you should drop VB, or c++, and use sql server (this just makes no sense at all). How does sql server replace VB?

Remember, ms-access is just a developers tool like VB, or c++.  It is a IDE that lets you write code, build forms etc. In fact, it shares the same code compiler as VB6). You write code, build a UI. You certainly have to choose a data engine with ms-access, but that choice can be Oracle, sql server, MySql, or of course JET. You have to make this same choice when you use VB, c++, or for that matter also. Ms-access is not a database, and is no more so then is VB. You can’t drop VB, and use sql server. And, you can’t drop ms-access and use sql server either (and, I can’t respect any developer that tells me otherwise either).

And, when you use ms-access with JET, or ms-access with sqls server, the performance is the SAME as VB, or c++. The only difference in scaling here is if you use a 3 tier system. Then, yes, a VB, or c++ solution can scale better because you build a set of middle ware.

However, at what point do you need a 3 tier solution?

Even a non optimal ms-access design with odbc to sql server is good for 100-150 users without sql sever even beginning to break out a sweat. So, 100 users on sql server is absolute nothing. So, I don’t know what you mean by not scale well here? 100 user set is going to cover a rather LARGE % of business to day.

Further, there are companies right now with ms-access applications in the 1000 user count right now (and, no special coding was done, and we are talking 2-teir here).

Further, for most small business, JET is rather adequate anyway.

The JET database engine was rated for 50 users by MS in their older white papers (that is before they started wanting you to use sql server!!). 

Certainly some ms-access developers have pushed the number beyond 100 users, but that really is stretching things, and even I would not recommend that many users.

But, =if you only got 5-15 users, and typical business application with say abut 50-60 highly related tables, and very small data sets of data (say some of the tables in the application are of very small size of only about 50,000-150,000 records, then that application will ABSOLUTE SCREAM in terms of performance on today’s hardware.  Retrieve of one record across a network only transfers the one record, and response time with only 15 users on the system will be instant.

It is really only when you have large tables, and have users counts of people (20+ users) will any issue of performance come up. The reason for choose sql server in most cases is security, reliability, and sharing the data with web based stuff (so, JET is fast enough, but other reasons come up)

It is not a speed issue, and in fact, in most cases, JET is considerably FASTER then sql server (In fact, in single user mode, JET is actually 40%-200% faster then sql server).

So, for a lot of shrink wrap type applications, then again JET is more then adequate here.

As always, if you out grow JET, then move the data to sql server, and continue to use the ms-access front end you built.

I am perplexed by the “not scale well” issue here.

Certainly if you plan for more then 150 users of the same application, then I would consider a 3 tier system, but that just not the bulk of the small business market anyway.

I am also perplexed by the idea that you can replace ms-access with sql server, as that also don’t make sense either. (you can replace JET with sql server, but who says you have to use JET with ms-access??).

sql server and ms-access are simply different things...

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Thursday, November 10, 2005
+1 Albert.

Scalability will also be impacted by how the solution is designed.

The largest user base I’ve had on a Jet solution was just over 130. The GUI was in VB using disconnected recordsets. From the usage log (appended on connection and disconnection) I found that there was an average of 6 to 8 users concurrently connected to the database with a max of 12. The average user was connected for only a few seconds: connect, execute a query or two, disconnect, done.

We eventually migrated this to SQL Server for both security requirements (corporate banking) and data volume ( > 500,000 records in some tables) not for the number of users. The data was migrated to SQL Server but the GUI still referenced Jet which in turn was linked to SQL Server.
Marcus from Melbourne
Thursday, November 10, 2005
Great example Marcus

Again another case of 100+ users on JET.

When you get up to that range, you do have to have everything just right…but it does show with good quality developers what can be done.

As you also mentioned, security, and a host of other issues (reliability, no worry of corruption of data etc) tends to be MORE important then the scale issue, and thus moving to sql is NOT always a issue of performance.

I did not want to “bite” someone here, but the idea that ms-access is interchangeable with sql server is just not right.

Further confusing this issue is of course ADP projects (access data projects). When you create a ADP in ms-access you are NATIVE using sql server (that is a oleDB connection from ms-access to sql server). That means when you create a table, or a field, that table gets created on sql sever. There is NO jet, and you CAN NOT even have local tables. 

Once again, you can’t use the term ms-access as a substitute with sql server, as they are just not the same tool at all. One is a developers IDE, and the other is a database engine, or database server product.

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Thursday, November 10, 2005
The product you want is clarion.

I went through the same churn pre-windows.  Clarion produces stand alone executables, and has without exception the best RAD out there. 

You can connect to lots of different databases.  If you plan do this on a regular basis, clarion is the way to go.  Prepare to spend $3k-4K though.

The most interesting thing about it is that it was template based (I believe Pro-C used to do a similar thing)

You see a lot of point of sale done in the DOS version of clarion (even new development)
Wednesday, November 16, 2005
You might want to take a look at the dbase application in OpenOffice 2, which is a free MS Access clone.
Monday, November 21, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz