Question Access versus Filemaker Pro Advanced

[genesis]

I have researched about Filemaker pro, thought I can say their IDE is not very user friendly and their script compared to vba is not very much powerful and their find compared to query is not very much extensive

However, I find that their database size limit is 7 terabyte and has simple good security feature compared to 2 gigabyte and database level password security of access.

Why cant microsoft provide such feature to access, giving access database up to 7 terabyte capacity and better security like Filemaker pro. I dont know why microsoft cant while FileMaker can.

If the reason is just because for profit, filemaker pro has sold many copies too of their product and also they have also created their own filemaker server edition.

Comments and opinions are welcome.

 

[HiTechCoach]

IMHO, Microsoft has already made Access without any limits to the size of the back end it can use. The default database format is is JET/ACE, but is not the only available format, which is probably perfect for 80+% of the Access database in use. I would bet the 2 GIG limit of ET/ACE will never be reached for most Access solutions. If you need more than 2 GIG, I would much rather use a SQL server that is best suited as the back end. I regularly use an SQL server as the back end.

If security is a real issue, I would probably not use Access or FileMaker.

 

[shadow9449]

The default database format is is JET/ACE, but is not the only available format, which is probably perfect for 80+% of the Access database in use. I would bet the 2 GIG limit of ET/ACE will never be reached for most Access solutions. If you need more than 2 GIG, I would much rather use a SQL server that is best suited as the back end. I regularly use an SQL server as the back end.

1) Do you know if ACE is any better than JET?

2) I think that the point raised here is that although you CAN use Access with any database you choose, the one that it interfaces with natively and most easily is JET with its limitations. Successfully upsizing the back end to SQL Server is beyond the knowledge level of most Access-only developers. For example, many queries are most efficient when executed on the server as a pass-through query. Writing T-SQL is a completely new skill for many people who have succeeded with Access for a long time. I think that many people find that simply linking an Access FE to a SQL Server BE does not improve performance and in many cases, degrades performance without further development.

SHADOW

 

[genesis]

1. as I can recall, jet was renamed to ace simply because they were handled exclusively now by access production team.

2. that is so true shadow. not all access programmers are in-depth knowledgable in sql nor much to vba and even to sql servers or mysql server for that matter.

sql servers and mysql are really good in terms for scalability, performance not to mention the size limit capability and security. However, if such desktop database could probably have features like those above, average programmers would definitely like and exploit it due to its simplicity and direct usage.

Using mysql and other type of server to meet the database size limitation would really need additional and new knowledge on them.

 

[Banana]

I've kind of liked FileMaker's UI and approach. It's a great solution as long I want a simple, one-user solution.

Now, FileMaker is capable of linking to different servers as Access is, but what really stops me is the fact that there's no VBA. It has a nice macro support but once you need anything that macros can't do, you have to buy an expensive plug-in to extend FileMaker, whereas Access provide you the option to roll out your own solution or pay for a third party solution.

Another thing that I kind of balked at was the structure. To be honest, I'm confused by what's actually required; do I need to have FileMaker server for multiple users? Can I skip it if I'm using other database server? With Access, it's both a server and client in one package and this is a convenient in that it provides much more seamless evolution path for database. Seldom, if ever, anybody knows what they need until it grows. Many times a mission critical database was born out from one worker's desktop and became useful.

Looking at things over long term, Access has more potential to provide long-term evolution, whereas Filemaker is definitely easier to get thing set up without any kind of programming.




Now, about the statement that linked table degrade performance. Let me emphasize this:
Jet is already very efficient at doing what it can do, even with a heterogeneous operation including ODBC linked tables. However, it is compelled to perform the bidding and if the developers requires Jet to do something that will kill the performance, Jet will do it because it's assumed that this is what they must have to fulfill the request.

This is why I wrote the Beginner's Guide to ODBC to discuss about some of those issues. Bottom line, if an Access application is designed for a server-client architecture from the start, it will be quite efficient whether it always remain a pure solution or eventually grows to employing a backend.

 

[genesis]

all that you have said banana were true. when I explored filemaker last night, it is far from access in terms of vba, creating tables, queries, sql, forms, report and macros. Access has much more tools to create the database.

However, I am really puzzled how the heck the database of filemaker accomodates 7 terabytes database size. The fact that they have also their server product.

With MS Access, yes it is true that their are free sql server express with 4 gigabyte size limitation. If you want to increase it, then go to other ms sql server.

Even with compare to ms sql server express, it is still far. I also know that it is remote to reach a 2 to 4 gig size for a small size to medium size business but what if it will reach beyond 4 gigabyte.

My point is, Access is far more sophisticated and advanced compared to FileMaker but why is it that Access limited the size growth of access to 2 gig. Why not engineer Access so it can also reach that terabyte size. Lets just take aside the fact that you can again create multiple backends. Lets focus in simplicity as "the simplier the better" approach.

If 2 gigabyte will take long time or transactions to reach it by small and medium business, then perhaps a terabyte size will definitely take not just long time but a very very long time and or transactions.

 

[Banana]

You know, if I knew the answer, I would easily make a business selling the answer to everyone who's asked the question of 'why are we limited to 2 GB' and retire a rich man.




Seriously, I'm inclined to say "so what?". The 2 GB is *plenty*. I have a sample database that anybody can download that contains about 4 million rows of fictional personnel records. It's only 270 MB. The only case where it can become a concern is if we decide to store binary objects in it, but the traditional answer has been to 'link it, not embed it' which also bypass the 2 GB restrictions. Thus, I'm hard pressed to believe that a well designed *desktop* database would need that much space. If it's all textual and numeric data and we need more space, then three things are possible:

1) Someone didn't compact & repair.
2) Someone didn't bother to archive the old records. **
3) RDBMS would already have been necessary because of concurrency and other considerations long before space was a factor.


** Archiving is beneficial even on any RDBMS and can improve efficiency by keep tables restricted to only "live" records, so that's not an Access-only optimization.

 

[genesis]

As I posted on number 6, I have these points:

1. Dont we have someone from Microsoft for us to contact or have forum with take he or she may answer the questions that only them can answer?

2. As I indicated there, it is true that perhaps 2 gigabyte will probably run long in time and transactions before it runs out of space even after compacting and repair

3. That it is also true that we can make many backend databases,
4. That it is also true that mysql or sql server express can be utilized as backend
5. That we can archive old records

Still, for "simple the better", 7 terabyte database would be simplier to use than setting up sql servers or mysql servers. I think, this way you are only focused in the administration of the database than also administrating servers which would definitely required additional knowledge.

Do we have contact or representative person from Microsoft that we can have forum in this forum to answer questions that they are more certain?

 

[Galaxiom]

Have you considered how a PC is going to run a 7TB database?

Aside from fitting it onto a drive it needs to get enough of it into memory to perform well. Stored on a file server and then opened it is going to take a long time to come across the network.

Long before this size, the perfomance will have degraded to miserable. Really big databases need to work through an SQL server to optimise the traffic required to query it remotely. IMHO contemplating a 7TB file based database is as ridiculous as promoting it as an advantage over Access.

Why bother learning a database with limited capabilities when Access can do so much straight out of the box. There is a lot of support available for Access. Put your time into learning how to work with SQL Server.

Access is encountered everywhere and "fluent in Access and VBA" on your CV will mean a lot more than "fluent in Filemaker ". So what if it is easy for a novice to get started? It isn't the boss who is going to be doing the setup. Nor will it be the guy promoting his Filemaker experience. It will the next applicant who finds it easy to set up Access and can be relatively easily replaced if he leaves rather than searching for someone to support FileMaker applications. And the guy who says "experienced in SQL databases" will get the job if that is what the business needs.

Microsoft is well aware of requests to lift the limit. AFAIK they have no plans to change it. I fully support their decision.

 

[Atomic Shrimp]

What sort of answer would you be hoping to get from Microsoft?

The 2GB limit on Access DBs and the 4GB limit on SQL Server Express are not technological limits, they are deliberately imposed by Microsoft. They chose to make it that way, probably for a couple of reasons:

--To make sure that peope don't end up using one of their solutions way into the territory where it will perform very poorly

--(probably most significantly) To encourage people to migrate from simple, cheap, small-scale solutions to larger, more robust solutions that they can charge more money for.

They have provided a pretty seamless upgrade path from Access to SQL Server - it's obvious they don't intend to change the limits on Access DBs

 

[Banana]

A car analogy!


Suppose you were shopping and the slick-haired dealer in his very nice Armani suit presents you with two very nice model, priced similarly. The smart buyer you are, you immediately request the dealer to hand you a specification listing both model's feature. Ever discerning, you immediately notice that one car outperform the other car in every category. It has higher top speed, more horsepower, more volume capacity in trunk and passenger space, has six cylinders over other's car four, comes with dual-head carburetor, set of all season tires, and my goodness so many more! It clearly has to be a better value, surely! You get excited, work out a acceptable price with the slick-haired dealer in his very nice Armani suit and as you happily hop in your brand-new (and powerful!) car, it starts up with a growl as if it's ready to go on the prowl. You gingerly drive out of the dealership and into the nice little country lane for a spin. The first thing you encounter is a sharp turn, almost 90 degree. No problem! You start to turn into the curve and ...


WHAM!

You find your nice little new car lovely wrapped around an oak tree. Thinking back, you merely had turned the steering wheel and it oversteered.

As you contemplate the last few seconds, you fill with horrible realizations. Not only you neglected to take it out on a test drive (and thus discover that it had awful handling) but also:

The specifications doesn't matter a damn because it never lists what you actually need out of the product.





Whew, that was quite a windy and circuitous way to make the point that Galaxiom drove home earlier; 7 TB is great on the paper but in practice, I'd bet you would be in a world of hurts. After all, it's the marketing's job to sell the product, and they like nice little numbers even though they represent the absolute limit but almost never the practical limit. By same token, Access's specifications claims to be capable of 255 concurrent users but it's seldom that anybody has a successful Access application capable of supporting 200+ users, let alone just 30 users.**


Next point to consider: Microsoft offers several products, while FileMaker just offers its own products, so in a sense, FileMaker isn't just competing with Access; it is also competing with SQL Server and Sharepoint and just about any other products that performs some degree of data storage & searching/filtering/reporting. On the other hand, Microsoft has to ensure that it doesn't put itself in unfortunate position of having Access competing with SQL Server or vice versa so it has to put down some kind of boundary. As pointed out, it's fairly seamless to upgrade/upsize/scale when the needs arise, and you're not even limited to SQL Server! Prefer Oracle, DB/2 or PostgreSQL? Sure, you can use them with Access if you want.

I do have one more advice: Go to FileMaker forum and talk to people there. See what they have to say.


**I've heard of reports of Access successfully supporting 100+ users, but I don't know whether it was a pure Access solution (which would be impressive) or using ODBC backend (which is very possible).

 

[Atomic Shrimp]

If I might also hijack your car analogy...

Different models have different features for different reasons - there's the nippy hatchback that is good for shopping trips and there's the big powerful 4WD SUV that is good for driving up mountains.

If you need to drive up a mountain, you don't plead with the car manufacturer to add features to the nippy hatchback, you buy the big 4WD SUV.

Yes, it costs more, but that's just tough - if you need to drive up the mountain, you need to accept the additional cost of doing so.

 

[shadow9449]

1. as I can recall, jet was renamed to ace simply because they were handled exclusively now by access production team.

Thanks...that's what I thought but I wanted to see if anyone else had further information

2. ...sql servers and mysql are really good in terms for scalability, performance not to mention the size limit capability and security. However, if such desktop database could probably have features like those above, average programmers would definitely like and exploit it due to its simplicity and direct usage.

Bingo. That was the point I was making. I hear again and again that there is no need for Access to have a native and easy to use back end because one can always use SQL Server or MySQL. I'm glad you see it the way I do.

SHADOW

 

[Banana]

Bingo. That was the point I was making. I hear again and again that there is no need for Access to have a native and easy to use back end because one can always use SQL Server or MySQL. I'm glad you see it the way I do.

TBQH, I'm hard pressed to envision a good case where we actually *need* 2GB+ without needing any other features.

That is, by time the data get that large, there may have been several concurrent users that a server would have been a better answer regardless. Or maybe the queries/reporting performance would have already degraded that it was already necessary to move up. The cases where more than 2 GB is needed but none of any scalability issues can arise seems to me to be quite few and far in between.

It's important to remember that there are architectural considerations, and Jet is a engine that doesn't come with a daemon, acts more like a file server and there are going to be consequences arising out of this. As Galaxiom pointed out, they may have already known that increasing the file size was just going to hurt more than help. I've already pointed out that 270 MB is good enough to hold 4 million rows worth of personnel records even with indices.

I went and re-read the PDF comparing Access to Filemaker and remembered why FileMaker seems to enable for bigger size; each of their table are stored as a single file. When you run FileMaker, you are actually opening several files! Thinking about it some more, Jet actually can do the same thing by storing only one table in each .mdb and have a front-end perform heterogeneous operations. But, it then begs the question of how FileMaker performs its queries, which the PDF doesn't really explain in technical terms. With Jet, all processing are done client-side (remember, no daemon here.), so it has to collect all pages it needs, and use the information from statistics & indices pages to retrieve the required pages to satisfy the request.

I'd wager to bet that if you or I sit and read the source code for Jet engine, we would find out that allowing 2 GB would be very bad idea, because of how it's implemented and to fix it would be a fundamental restructuring that it's not really worth the costs.

Who knows? Maybe Microsoft will one day change their mind or come up with a solution, but I'm not going to hold my breath.

 

[Banana]

If I might also hijack your car analogy...

Different models have different features for different reasons - there's the nippy hatchback that is good for shopping trips and there's the big powerful 4WD SUV that is good for driving up mountains.

If you need to drive up a mountain, you don't plead with the car manufacturer to add features to the nippy hatchback, you buy the big 4WD SUV.

Yes, it costs more, but that's just tough - if you need to drive up the mountain, you need to accept the additional cost of doing so.

Excellent point and decidedly less long-winded than mine!

 

[shadow9449]

TBQH, I'm hard pressed to envision a good case where we actually *need* 2GB+ without needing any other features

That is, by time the data get that large, there may have been several concurrent users that a server would have been a better answer regardless. Or maybe the queries/reporting performance would have already degraded that it was already necessary to move up. The cases where more than 2 GB is needed but none of any scalability issues can arise seems to me to be quite few and far in between.
..

Ok, I probably should have been more specific.

I agree about the 2GB limitation.

My beef is more the reliability with regard to running on a WAN and number of concurrent users. I realize that the 2GB limitation was the one that Genesis was contrasting with Filemaker so it's natural that you expected me to stay on topic!

We've had this discussion many times: I contend that if MS WANTED to, they could bring JET or ACE or whatver native backend up to par with more reliable and robust database formats. OR, to integrate SQL Server with Access as the "native" database format. That would mean that the developer would not have to worry about passthrough queries, writing T-SQL or ODBC or DSNs or going DSN-less and so on.

SHADOW

 

[Banana]

They already tried that with ADP, remember? You can deduct how well that went over.

But for the questions you're asking.... I'd suggest you to go to Office Watch site and take a look.

 

[shadow9449]

They already tried that with ADP, remember? You can deduct how well that went over.

I never tried using ADP myself but from what little I know about it, the front end part had some limitations compared to MDBs, it integrated with MSDE which was a pile of **** which made JET look good by comparison. I know that later ADPs were integrated with SQL Server Express but not without headaches. I actually don't know why they pulled ADP or why it failed.

But for the questions you're asking.... I'd suggest you to go to Office Watch site and take a look.

I've never heard of Office Watch but I'm sure I can find it in a few minutes by searching.

Cheers

SHADOW

PS I read a bunch of articles on Office Watch and it seems like a great resource. I like the style and tone of the posts there as well. I've subscribed to their newsletter. Thanks for the tip.

 

[genesis]

the difference between desktop database and server database is their ARCHITECTURE that they were made.

That is why I asked, WHY NOT engineer or make the architecture of Access database like sql server database in terms of Connection and Size Capacity.

Like in a Car analogy:

why not give the little cars features of the big cars so that it can do functions of the big cars and vice versa.

But ofcourse we are not talking about cars, we are talking about softwares.

 

[Banana]

Because when you try to fit big cars' feature in a little car, it's no longer a little car. It's now a big car. Or at least a weak big car.

It's not a simple matter of adding more feature or capability. Some design consideration require you to make sarifice or tradeoff.

Go and build a fast mousetrap car then build one for long distance. Now combine them.

I'd bet you will have no problem coming up with an excellent drag racer and long hauler but quite hard pressed to build one that's both great drag racer & can travel long distance.

There's also a reason why it's a common quips among engineers:

Fast, cheap, reliable. Pick two.