When comparing Access to ORACLE/other "big boy" databases, the biggest differences come about when you are looking at what is added by the "big boy" DB package vs. Access.
First and foremost, the big boys (in general) automatically force you to work in a split-db concept vis-a-vis Access. That is, the big boy is always and only an SQL processing tool with regard to its data files. Any forms, reports, etc. come from a separate tool that you add-on to your big-boy purchase. "ORACLE Tools" is the package you would buy to implement a front-end for your ORACLE database, if you wanted to stay all-ORACLE.
Features that you either have to write yourself or that cannot even be done at all in Access:
1. Analytical change history a.k.a. journal a.k.a. forensic log - lists EVERY change made to EVERY field in EVERY table (if that was what you wanted) and the original value of the changed field - so that you could, say, roll back a committed transaction to a prior point in time.
In Access, the ONLY way to do this involves blocking EVERYONE from doing anything that is not based on a form, and then putting a bunch of analytical or comparison code behind the forms to test the before-and-after images of the record you are about to update.
In the big boy databases, audit journaling at this level is usually built in.
2. Data-centric triggers (as opposed to event-centric triggers). The common industry nomenclature appears to call the former "Trigger code" and the latter "Event code". But the truth is, they are really the same. They just occur under different circumstances. Trigger code runs when something specific happens. The difference is from where the code is called.
Data-centric triggers are "tripped" when a field has a value-trap defined and the specific value is detected. Suppose that you wanted to take special actions for a record in which a particular field contained a particular range of values. Maybe an extra validation or a different set of default values. Or an automatic e-mail.
In Access, you can only do this through a form or a macro that calls code based on an Update event of some flavor. If even one person can make a record change without going through your form, you are hosed. But in the big-boy databases, data-centric triggers are merely like "extra" event routines that you can define. They will work even in cases where there is no form or macro-equivalent running.
3. The big boys have the incredibly important ability that many times you can interchange data nigh unto automatically if you have more than one type of computer and your db vendor supports all the types you have. For instance, at this site we have several Windows, UNIX-like, and OpenVMS boxes that run ORACLE. At the ORACLE level, the host O/S makes no difference. They talk to each other peer-to-peer when we define the conditions that allow such interchanges. All we have to do is say, "Update table X on machine Y from table A on machine B." Then ORACLE does it. O/S? We don't need no steenkin' O/S!
4. This cannot be emphasized enough. Access bogs down any system on which it runs when there is a network involved. It also has limits that at first blush would appear quite big, but they aren't really. This is because Access by itself is not a really good client/server system. Access with another back-end, however, IS a good system.
The first limit is that native Access, being standalone in concept, has to do everything itself. Which means that, in effect, even for a shared database, your server that holds the database is only a file server, not a database server. All you do is read the heck out of the files. In this scenario, the combination of a large database and a lot of simultaneous users will crack your network's back.
When you use the big-boy databases, the server is no longer just a file server. It also runs the queries locally and returns a result set, NOT THE WHOLE TABLE. Your workstation no longer needs to do anything like a query because the server did that for you.
The second limit is that Access internally uses file-relative pointers to find everything. But the pointers are the moral equivalent of virtual addresses within the file. That is 32-bit addresses. So it doesn't matter whether you have a lot of short records or a fewer number of long records. When you run out of virtual address space inside that file, you're done. And if you have .JPG images, big memo fields, or internally-copied embedded objects, you could be in REAL size trouble fast.
The big-boy databases do not use that particular storage paradigm. Their internal pointers are consistent with the file-system pointers that can include both a block number and a byte-within-block, which is usually larger than the limits imposed by Access. On OpenVMS, which in my case is running on an Alpha (64-bit machine), my ORACLE can have as many records as I have disks to hold them. Access would have a 4 Gbyte limit on ANY system because of its shorter pointers. My ORACLE/Alpha system has terabyte limits, and not single-digit terabyte quantities, either.
In summary, the differences are not only in matters of scale but also in matters of ability. You can do more things with the big boys than with Access, and it is not just more records.
) that Triggers bound to the data means stuff like IIf or various "conditions.
