Alpha Five compared to Access

[Melvin Davidson]

RE: Alpha Five compared to Access

Brad,

It is easy to create a unique index on a simple column.
Using your fields as an example you can define an index with an expression:

Index Name Order Expression
===========================================================
NAME_UNIQUE NAME+STR(POLICY_NO,10,0)+CDATE(DATE)+RECEIPT_NO

Filter Expression Case Direction Include
===================================================
Insensitive Ascending Unique only

Note: presumes Policy No is numeric and Receipt No is Character

To prevent entry of duplicates, Edit the Field Rules for that database and in the Data Entry Tab for each field,
make them required.

Then in the Validations Tab, select
Uniqueness test: Value of expression must be unique
Unique txpression: NAME_UNIQUE NAME+STR(POLICY_NO,10,0)+CDATE(DATE)+RECEIPT_NO

Hope this helps.

Melvin Davidson

[Scott Rusoff]

RE: Alpha Five compared to Access

Hello,

I found this on Microsoft's web site

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_actips.asp

Ten tips for Microsoft Access Developers,

what I would like to see if anyone out here is interested in trying to accomplish what was done with Access and do the same thing in Alpha, to show how much faster its to develop with Alpha.

This is the only way we can prove to the world that Alpha is alot better than Access.

Thanks,
Scott

[Steven Spencer]

RE: Alpha Five compared to Access

What a neat thread...

Tom said
> An index can be defined which only shows unique values, and is very handy in this case. However, I don't believe it will actually prevent duplicates from being entered in the first place.
=====

Wait a minute, isn't the definition of a unique index one that only ALLOWS one entry to the key, and the OpSystem (or database language) gives an error msg on an attempt to add a record that would violate the unique key..

To allow the entry, and then hide it, sounds pretty dubious.

Can we get a readout as to how Alpha deals with this ?

[Melvin Davidson]

RE: Alpha Five compared to Access

Steven,

a unique INDEX in A5 does not prevent the entry of duplicates, it only shows unique entries.

In the V4.5 Help,
Building and Using Indexes
"If you choose Unique only, records with duplicate key values are filtered out. For example, if you have created an index which orders records by Firstname and Lastname, and you enter three records for "John Smith" only the first record will appear; the rest are filtered out."

IF you want to prevent duplicate entries, then you need to
define a Field Rule under Validation which makes the field or expression unique.
Field Rules
Validation Rules
Specifying That Only Unique Values Are Allowed
Value of field must be unique
"Select this setting to specify that any value you enter must not already exist in the same field in another record. If you try to save a record that has a duplicate value in a unique field, an error message appears listing the non-unique value."

[Steven Spencer]

RE: Alpha Five compared to Access

A lot depends on what you are trying to do..

As a developement environment Alpha has become very impressive, and if you put aside issues of Mac, installed base, existing apps and such, any programmer type would likely have to take it way over Filemaker or Access for a project.....

Once I have Alpha reasonably usable, I do hope to add to my PC language skills with either Clarion or Magic, (although Magic is more Enterprise oriented, I gather, and a steeper learning and much more $ to get into, so I will leave that aside).

Correction and notes on Clarion:
Clarion runs proprietary only for its native format, but has special drivers, and OBDC support as well .. I would like to know the exact .dbf level files that Alpha uses, to see if they can run in tandom without OBDC, which probably adds overhead, and then one could run Alpha and Clarion on the same data, which would be nice..... (hopeful)

They really seem to be the only other product that was designed from the ground up, integrated, really for the true programmer who doesn't like to muck in code more than necessary, personally I am hoping to work with both, and I don't think they qualify as expensive.. both products have sophisicated in integration, Alpha with the neat integration of scripts and code, Clarion with templates using embedded points for code insertion.. kudos to both, I am curious if any other companies compete with either one on their success in achieving these types of tools...

Understand that my experience has been that Alpha goes farther without code, is simpler and cleaner in many ways, but I can see them easily as complementary products as much as competitors.. Clarion gives the .exe code, The beautiful built-in Letters, Labels, Reports, Email, query, etc with even allowable end-user flexibility ... always seem best by test in Alpha..

[Scott Rusoff]

RE: Alpha Five compared to Access

Just being curious, what is difference in Alpha vs Access in speed performance.

Thanks,
Scott

[Jeremy Spurling]

RE: Alpha Five compared to Access

I find it rather humorous to see a thread to "gather the stones" against Access, yet within this thread a major difference in the data integrity methodology is presented. And to an experienced database programmer, I believe Access takes tops on that issue!

Let me start over with this statement: In order to truly compare Access and Alpha5, you have to set your foundation for making the comparison. First, who is the targeted person or group of people and their skill level in each database tool? Secondly, what exactly is trying to be accomplished, or what is the overall project to be compared? And, finally, how will the database tools be graded against each other?

Once those three primary questions are answered, then a true test of the differences in the two database tools can be made. Mr. Rabins has only specifically asked for people to tell him of examples where that individual or group has found something to be different and better in Alpha5 verses Access. I don't believe this to be a legitimate and realistic comparison, simply because there are too many differences to start with as in skill level and familiarity of the two applications. Does this make sense? I am not here to bash or even to really promote either one. I use both! I just want to make sure that this comparison documentation that is being put together truly demonstrates a logical and well-designed comparison. Just remember that the "People in WA" can put their own document together comparing the two as well.

I am really interested in seeing how this document works out. I believe that it is going to be a very big task in comparing the two because each offers so much. Best of luck!

- Jeremy Spurling -

[Scott Rusoff]

RE: Alpha Five compared to Access

Jeremy,

I like what you had to say, even though Microsoft can create their own comparision, most of the time when they do something like this, they do not tell the truth, in the past they been know for doing this to Linux, Novell and all their competition.

In truth, when this document is created, you have to give the facts, why Alpha is better, and with examples to back it up.

Thanks,
Scott

[Steven Spencer]

RE: Alpha Five compared to Access

>Jeremy
>a major difference in the data integrity methodology

And what is that difference ?

[Jeremy Spurling]

RE: Alpha Five compared to Access

The difference is in how Alpha5 handles indexing and "Key" values in a table verses how Access handles it. Creating unique indexes and/or uniqueness tests on fields (columns) is directly related to data integrity. I am not talking about referential integrity. It was clear that by creating a unique index on a table in Alpha5 would allow for duplicate records to be added to the database but would "filter" them out so they wouldn't be seen. That is really scary to me. I can understand where just a regular index is used for sorting and managing the data, but the key word is "unique". That word looses it's meaning if it allows for duplicate data. Am I making sense? Do you see where I am going with this. Access does not work like this. When you set a "unique" index or even a "key" field, Access does not allow duplicates. I think that one can set an index or key on a table easier than how Alpha5 does it. I will say that Alpha5 allows more control on managing the indexes set on a table. This is what I meant by "data integrity methodology". It is my opinion that Access is more clear in how the indexes work, it means what it says.

Was I clear in my explination? This is why it is very important to have a solid foundation set before comparing the two database tools, because they offer similar yet different methods of handling certain aspects of a database.

Thanks for asking me to be more specific. I am always open for opinions and sharing.

- Jeremy Spurling -

[Steven Spencer]

RE: Alpha Five compared to Access

Jeremy
>The difference is in how Alpha5 handles indexing and >"Key" values in a table verses how Access handles it.
>Creating unique indexes and/or uniqueness tests on fields
>(columns) is directly related to data integrity.

Steven
Yes, I have commented on the key issue a little bit, since I have a Sys/36 AS/400 background where the OS is smart on dupkeys...

I think Alpha may be hampered a bit by its .dbf heritage in trying to upgrade this ... really, a "Unique Key" should, imo, be defined to by truly unique (an error message on an attempt to add a record that would be a dupkey is the basic implementation).. other wise you should at least call it something else :-) And admit that you are not handling unique keys in the Alpha level, only at the code level.

Jeremy
>I think that one can set an index or key on a table easier >than how Alpha5 does it.

Steven
Dunno how much easier than can get..
Alpha builds indexes automatically even when you just define a set linkage, which I think is kewl .. Assume Access does the same ? .. Alpha does seem to be generally very plesant in buiding indexes, and in its general "Expression Builder" as well.. I have no kvetch there :-) (granted I dunno Access)

Jeremy
Was I clear in my explination?

Steven
Yep

Jeremy
This is why it is very important to have a solid foundation set before comparing the two database tools....

Steven
Agreed, an Alpha programmer throws in a couple of lines of code to check for "true uniqueness".. an Xbasic Exist thingy if I remember... nonetheless this is an extra step that really belies the concept of a "unique index" that is not truly unique..

In general there are tons of issues to be considered, we covered a few earlier in the thread, (or was it another thread).. e.g. how Filemaker (and maybe Access?) makes it extra steps to separate data from programs.. I feel that is a little tacky :-)

Jeremy
Thanks for asking me to be more specific. I am always open for opinions and sharing.

Steven
Welcome..

[russ Boehle]

RE: Alpha Five compared to Access

If an index is set to unique it will not allow duplicate keys unless the index is deleted. The only disadvantage I can see is that it dousn't tell you it's a duplicate until you save the record. You need xbasic to tell you it's a dupe when you leave the field.


Russ

[forskare]

RE: Alpha Five compared to Access

With Access, the unique index can be set at the table level at the bottom where Access field rules are located. You can choose to allow or not allow duplicates. No code or anything else is needed. All done right away from the git go. That's one thing (there are others) about Access that I do like. No muss, no fuss.

On the other hand there's not much more that can be done with the Access index, it doesn't have the depth of control that an Alpha index has. Alpha is similar in that when a table is created in Alpha, there's the option to select the field as an index. Perhaps all Alpha has to do is create an option so the index can either allow or not allow duplicates. Make it sound easy, don't I?

kenn

[Ray Fernandez]

RE: Alpha Five compared to Access

Sorry Russ,

You're 100 percent INCORRECT. There is a post that I posted about two weeks ago on the Alpha 5 newsgroup explaining this situation.

RF-ARS-Motorola

[CALocklin]

RE: Alpha Five compared to Access

Well, I have to throw in my 2 cents. From my viewpoint as an A5 developer that has learned something about Access, Jeremy and Steven seem to be 'stuck' in the Access definition of a "Unique" index.

I think the A5 version of Access's "Unique" index is to set the Validation field rule for the field to "Value of field must be unique". If you want a uniqueness test that is more complex, you can even define "Value of expression must be unique" which allows you to combine the values in two or more fields for the uniqueness test. (Can access combine fields? Just curious, I don't recall.)

The only purpose I can think of right now for a unique index in A5 is to use it for a lookup. (There may be more but I can't think of them right now.)

This may not be a great example but you might be running an election for a state office and have a list of contributors and want to select a city for some type of report. (After all, not every city in the state will have contributors.) You could show all 10,000 contributors by city and then scroll through the list of 50 in city A, 220 in city B, etc., etc., etc. until you find the city you want. OR, you could create a unique index so each city would only be listed once - much easier.