Access and RDBMS Backend: Functionalities & Extensions

[Banana]

After working with Access & MySQL, I discovered several functionalities that weren't possible with just Access, and I know that from my past search (may not be true anymore) while researching various RDBMSs, that there wasn't anything comprehensive discussing what functionalities became available/possible when using a given Access/RDBMS combination. So, I thought it would be interesting to have a list showing what Access could do with a given backend and hopefully this may benefit anyone who has luxury of choosing a RDBMS (which I assume is not true in most of cases??) in making a informed decision or at least provide food for thought (that'd be me!).

And if you're scratching head wondering what the heck 'functionality' Banana is talking about, let me cite two examples with MySQL.

One, I've already stated earlier that I can have much more control over transactions, especially across bound form/subforms, committing or rolling back *both* parent record and related child records as whole. This isn't possible with Access backend, AFAIK.

Two, MySQL has one pluggable storage engine, InnoDB which enables me to read records without actually requiring locks. This is possible using a form of version control so reads never ever locks the row unless explicitly requested by the client. This is a good thing to have if deadlock is a concern. (To be fair, I'm not sure if that is also the case with other RDBMS- do please correct me!)

So, I know some of you use Oracle, and more use MS SQL... I don't know anyone else here uses PostgreSQL, FireBird, or ??? Maybe they can chime in, too.

 

[Pat Hartman]

Locking is controlled by the database engine and Jet is very different from "real" RDBMS' and much less sophisticated in its stratagies and capabilities.

 

[Banana]

Locking is controlled by the database engine and Jet is very different from "real" RDBMS' and much less sophisticated in its stratagies and capabilities.

That is certainly true. I did some extra reading as I was genuinely curious about that few threads regarding SQL Server and locking issues. Apparently that "no locks required for reads" aren't available to SQL Server prior to 2005 version, so it's not certainly unique to MySQL (and am fairly certain Oracle has that functionality as well, given that InnoDB is developed by Oracle).

It's just nice to be appreciate all different ways that backends can be used to solve a problem.

 

[The_Doc_Man]

In ORACLE (and I believe in Sybase as well), you can specify no locks in a query that is otherwise read-only. Won't stop something else from locking the table, but it lets a bunch of simultaneous read-only actions get through with minumum overhead if they all use the no locks option. Darned if I remember the exact syntax, though.

 

[Banana]

The_Doc_Man,

I hope everything has settled down with that hurricane readiness.


Thanks for chiming in; I seem to keep forgetting about Sybase...

Just to be clear, I'm more interested in "undocumented features" that arises from a particular combination of Access front-end/ X back-end as I've since discovered with MySQL, and thought it'd be cool to at least know something about each RDBMS's features that can be employed with Access to do something not ordinarily possible.

 

[mike007]

Need Advice On Using Sap Bi To Complement Access Front To Sql Server Standard Edit 32 Bit Vers 2005 Backend. I Work At The Hq Of Retail Environment Where The Fin/accounting Gets Done. Is There Any Practical Way To Incorporate Sap Business Intelligence Into My Access-sql05 Design? What Value Could It Bring Vs The Trouble Of Doing It?