Access Limitations Vs SQL Server, Oracle

[Mike375]

I would like to clarify the postion of Access Vs The Big Ones and mainly so I answer any questions correctly.

At the moment my understanding (please correct any that are wrong)

1) Scale for both number of users and Access 2gb limit.

2) All changes for any individual user must be made on that computer for Access but not with SQL Server/Oracle.

3) SQL Server/Oracle have a built in roll back system for seeing previous entries such as phone number etc. and that would need to be made in Access.

If any of those are wrong and/or there are additional issues then I would greatly appraciate any elaboration.

In general how big an issue is it to move from Access to MS SQL Server. Ihave read a few times that the main selling point of FileMaker is it is much better if the DB needs to be expanded to a lot of users. This would only be an academic question from any client/prospect I would see but as you know people like to know different things. In general the Access DB I would sell will have a lot of Access to Word, Access to Outlook and SMTP and in particular inserting Word content/format into email body. A lot of file management done with VBA for back up, date/time stamping etc.

All the queries I use are simple and can all be made (and are made) in query design view.

 

[ByteMyzer]

2) All changes for any individual user must be made on that computer for Access but not with SQL Server/Oracle.

This is not entirely accurate. You can have a FE/BE MS Access application (Back-End on a shared location, copies of the Front-End), where changes made by any End-Users on the Front-Ends on their respective machines are automatically pooled in the Back-End in its one location.

 

[Mike375]

I don't know if I am on the same page here.

I am thinking mainly about someone on Compter A can only see records based on a selection criteria but the FE on Computer B would have different selection criteria.

 

[LPurvis]

OK... I tried but failed to stop myself.
At the risk of seeming both pedantic and endlessly repetative...

Access has no database engine.
SQL Server, Oracle, MySQL, DB2 etc are all RDBMS (or just database engines if you like).
Access is a development enviroment designed for working with databases and objects geared to towards that.
Access ships with (and has very tight integration with) the JET database engine (now ADBE, or ACE etc).
Classic Visual Basic also shipped with Jet - and yet nobody compares VB6 to SQL Server ;-)

IMNSHO when we state that we're comparing Access to the server RDBMS' then we only server to perpetuate the myth that it's a "toy". Small time and unable to cut it in the big league.
JET is actually very, very good at what it does. But it isn't a server database engine - it has limitations and they're acknowledged. (And mis-reported).
JET is a local engine, running on the client PC. There are inherent limitations in that model anyway. But it is a further requirement is that's what you're meaning by the above... (I'm really not sure what you're asking though).
JET can't perform on the same scale as the server database platforms. Nor should it.
That the server RDBMS' are there is a great thing - and that Access can work with them so easily and well is a real testimony of what it really is.

But I'm really not sure what you're asking in 2). lol

Cheers.

 

[Mike375]

2) All changes for any individual user must be made on that computer for Access but not with SQL Server/Oracle.

But I'm really not sure what you're asking in 2). lol

Let's say there are 5 computers on the network and on each front end there are different selection criteria. But nothing to do with "user permissions" at least as I understand that term. There might be a password on a form etc but that is only forming part of a selection criteria etc.

On Access, that is Access as the package we buy for things to be changed on a FE machince it must be done the the DB on that machine. I had been tild on the larger data bases that such changed could be made on the BE or "head office" machine.

Perhaps I can ask the question another way. If someone had Access on a FE/BE basis AND Access can handle the number of users withouta problem, what, if any, advantages or gains would the business get if they signed up with the man carrying the SQL Server or Oracle in his briefcase

 

[LPurvis]

>> for things to be changed on a FE machince it must be done the the DB on that machine

What "things"?
You mean tables? Obviously they would be changed in the shared BE MDB.
Queries? OK yeah - that could be server centralised if you used Views and SPs only for your database querying, whereas when querying Jet the query definitions are held on the local client PC. (The fact that both Access and Jet objects can inhabit the same MDB is often what blurs the line between the two... Access UI objects and Jet queries for example. That would be that "tight" integration mentioned lol).

I can't think what else you could be referring to if not tables or queries though.
The rest of the objects in an Access application are part of the application UI (forms, reports, VBA code).
There is no real equivalent in, say, SQL Server (not counting Reporting Services - how many end users have that?).
Oracle has "Oracle Forms" which I've never used myself.
But essentially - you wouldn't be comparing like for like.
Access is a front end tool - which makes use of backend databases (client or server side).
The database engines have nothing other to change other than data objects.

Do you want to narrow the focus down a little more?

Cheers.

 

[Mike375]

>> for things to be changed on a FE machince it must be done the the DB on that machine

What "things"?
You mean tables?

No selection criteria for record categories and similar. No changes to tables.

I think I get what you are saying. That is, Access is a user interfacer maker and as we but it comes supplied with a data base called JET.

Is the person who is involved with SQL Server or Oracle typically using a user interface maker that provides an advantage to the business.

I will try my other questioin again

If someone had Access on a FE/BE basis AND Access can handle the number of users without a problem, what, if any, advantages or gains would the business get if they signed up with the man carrying the SQL Server or Oracle in his briefcase

 

[Pat Hartman]

I can't stand it either so I'm going to chime in as well. People who compare Access to SQL Server and other RDBMS' don't know what Access is. I'm pretty sure you know because you have been working with Access for some time but you may not know what a RDBMS is and so you pass the questions along. As Leigh has already pointed out, Access is a front end development environment. The fact that it makes use of Jet (a RDBMS) to store its objects has merged the two in the minds of many people. In fact, the Jet engine is installed with the operating system so even if Access is not installed on a PC, Jet is available and many non-Access programs use Jet to store their data. One that comes to mind is the business version of TaxCut. Jet, NOT Access is the data store. You simply cannot compare Access to SQL Server or any other RDBMS because Access is a front end development environment. It is NOT a RDBMS!!!!! It makes more sense to compare Jet to SQL Server although even that is a stretch. It's like comparing your family car to an 18-wheeler. Yes they can both carry people and things from one place to another but there is really no comparison. One is not inherently better than the other but each is good at what it does.

What the people who don't know what Access is fail to understand is that Access and SQL Server are not competitors - they are complementary products so even the MS salesforce misses the point when they push SQL Server because it's "better" than Access. Almost all of the applications I develop do not use Jet to store the application data. The data is stored in SQL Server, DB2, Oracle, Sybase, etc. Whatever database the client uses. Access is limited only in the fact that it needs an ODBC driver to access a non-jet database so as long as a driver is available, Access can access it and that has been true since DAY 1. Including Jet in the box was an afterthought to make Access a complete desktop development application.
1. Scale - Jet is limited to 2gig, Access is limited by the RDBMS to which it is connected.
2. I also don't understand this question. If the point came from a sales person, it may relate to Triggers which are simplistic programs that can be attached to add/change/delete events at the table level. Jet does not support triggers and so needs to rely on coding in the class modules of forms to do this.
3. Yes relational databases can log transactions and Jet cannot but what does this have to do with Access? The answer is NOTHING!!!!

 

[Mike375]

I can't stand it either so I'm going to chime in as well. People who compare Access to SQL Server and other RDBMS' don't know what Access is. I'm pretty sure you know because you have been working with Access for some time but you may not know what a RDBMS is and so you pass the questions along.

I suppose what I am looking for is a "sales answer" and I simply don't know enough about SQL Server/Oracle to form my own story.

But I will try the one question I can't get an answer on:

How big a deal is it for someone to move from a data base that is all Access to whatever will be used if the number of users becomes to great.

I won't be getting involved in that area, I don't know enough to get involed and have no desire to get involved but I would like to be able to give someone and honest answer.

If I read the FileMaker BS then to move to what will deal with more users will be bigger than Ben Hur

 

[Pat Hartman]

As I said earlier, virtually all of my databases use non-Jet backends. I always use linked tables and bound forms so in theory, any Access application can be converted to SQL Server by using the upsizing wizard to transfer the tables and data and by adding a couple of arguments to DAO/ADO code when your tables use autonumbers. In practice, it usually takes more than that unless you followed good client/server practices to start. Read the article in the MSDN on optimizing Access for client/server to see what things will need to be changed in your application.

The biggest issue is that forms need to be bound to queries that select the absolute minimum amount of data. Access applications frequently have forms bound directly to tables or to queries with no selection criteria even though we constantly recommend against that. There are other issues with queries themselves if you use VBA or user defined functions in the select clause since those can't be translated to SQL server as these will force Access to request entire tables from the server rather than specific records.

 

[gemma-the-husky]

i am no expert in SQL server at all having hardly used it - I am trying to upsize an important dbs from access(jet) to sqlserver at the moment, but i do have a copious reference library, although much of it is a few years old

the clearest explanation i found of what upsizing was is given in

Micsosoft Access - Developers Guide to SQL Server - Chipman and Baron

As i understand it, just converting the back end to SQL server achieves little by itself and may actual lead to a deterioration in performance. What is needed is a reassessment of the entire project to take advantage of server-side processing by using features such as triggers and stored procedures, and presumably many other techniques that i know nothing about.

I know sql server does also add better features compared with such as improved data security, larger datasets, etc

I am sure the front ends still have to be kept completely up to date, but perhaps some of what Mike is getting at, is that if the back end is treated like a "black box" then perhaps changes can be achieved by modifying the stored procedures, without having to change the front end.

 

[Pat Hartman]

The problem with the Chipman and Baron book is that it is heavily invested in .adp technology which has been deprecated. It gives short shrift to using .mdbs with linked tables which is NOW the prefered methodology. Converting an Access .mdb to an .adp was almost as much trouble as rewriting the entire application which was one of the reasons that it wasn't widely adopted.

In the dozens of applications that I have created in the past 15 years using non-Jet backends, I can count on one hand the number of times I needed to create pass-through queries and I've never had to create a stored procedure. Jet does an excellent job of passing through queries to the back end server. You just need to understand the Access/Jet/VBA features that cannot be directly translated to SQL server or Oracle or DB2 or whatever. The article I referred you to explains some of the issues you will run into. Here's the top contenders:

1. Forms bound to tables or queries without selection criteria
2. Queries that use VBA or user-defined functions in the Where Clause. Problems can occur in the select clause but they are less likely.
3. Tables without primary keys. Jet requires that a linked ODBC table have a primary key in order to be updateable.
4. Use of DLookup() in queries.
5. Use of DLookup() when a table join should have been used.
6. Sloppy naming conventions which may require column renames when the tables are upsized.
7. Use of VBA code loops for updates when an update query should have been used.
8. Opening table level recordsets in code and using Find or Seek to get records. These should be replaced with queries that include selection criteria.

 

[Banana]

I am sure the front ends still have to be kept completely up to date, but perhaps some of what Mike is getting at, is that if the back end is treated like a "black box" then perhaps changes can be achieved by modifying the stored procedures, without having to change the front end.

This is the basic principle of n-tiered architecture. Access, using Jet as backend is strictly 2-tier architecture; not a bad thing necessarily mind you. That said, even if we were to implement a n-tier architecture:

a) Access still can be used as front-end, provided you maintain a logical separation between the presentation layer (e.g. the front-end client), the storage layer (e.g. the backend) and the business logic layer*.

* Normally, this would be encapsulated in a library such as DLL files, but there is no reason why it can't be just a bunch of stored procedures or VBA procedures, though this isn't a strict n-tiered architecture.

b) N-tiered architecture paradigm is primarily aimed at the problem of enabling us to swap in one layer for another layer, but does not address the problem of coupling and cohesiveness. Therefore, it is possible to find out that n-tiered architecture may result in unnecessary work when adding new functionality or improving an existing functionality because you have to work all three layers (or at least two layers) to implement this whereas 2-tiered architecture, you just write out some VBA procedure or a SQL query and you're done.


On a tangent, someone had mentioned to me that they would eschew stored procedures if they were implementing a n-tiered architecture as the logic is that it defeats the whole point of n-tiered architecture; one should be able to swap out in say, SQL Server and swap in DB/2 without having to rewrite the library to access the database. Of course, that would require a strict adherence to standard SQL and eschewing several of extensions various vendors added to their products and providing or duplicating the functionality using the middle-tier library.

OTOH, if we were less certain of what front-end we may use or face possibility of supporting more than one front-end client on different platform, stored procedures and custom extensions may make even more sense in the case; we're just doing it 2-tiered on the backend (e.g. business logic and data storage) with the front-end being nothing more than a dumb terminal.

Ultimately what development strategy works best will depend on the problem domain we're working with here.

 

[Mike375]

I have read what Pat and Banana posted, although I don't know what they are talking about

Perhaps if I outline what I make you could tell me if it would be likely to be a big deal or not too much for someone coming in to do the SQ Server deal.

1) My queries are always made in query design, so nothing fancy. I use a few functions in queries that I make and these invariably relate to extracting character, strings from text. A lot of IIFs, Left, Right, Mid, Len, InStr.

2) Lots of Access to Word bookmarks, saving the files as date/time, reopening then based on the file name being in Access texboxes.

3) I have a lot of VBA that relates to general file management but that would probably stay as it is usually a stand alone DB.

4) Pretty much whatever you can do from Access to OutLook and Access to SMTP

5) I have a lot of VBA on forms, but apart from Access to Word and Access/Email it is all simple stuff that can be done with macros. In fact it is only done as VBA because the MDE does not block out macros.

Again, it is extremely unlikely that I would ever be involved in a conversion and also unlikely that any business I sigh would need to convert. However, the question can arise...what if my business expands etc.....and I would like to be able to give an approximate answer that has is roughly correct.

 

[Banana]

Unfortunately, I can only answer those with a "Maybe" as this depends more on how you've designed it and how it is coded than what tools you used. If you started with server-client paradigm when developing the application and make sure that your code and queries act accordingly, then the move is simple. OTOH, certain things that works fine in Access will be totally horrible on a server-client application (for one example, doing filter using Access's built-in fitler on the form).

 

[Mike375]

server-client paradigm

I don't know what that means.

I have gained a general impression, which could well be wrong, that people doing the SQL Server deal use queries far more than I would.

 

[DCrake]

Well here's my tuppence worth.

The main issue I have between Access and SQL Server is the fact that I have been unable to create crosstab queries in SQL Server. Likewise I incoorporate custom functions within select queries, this again is not acheivable in SQL server. The referencing of dates is also different. Although I would like to use SQL the nature of the application tends to dictate the data source.

I have used SQL server to hold the data but used OBDC linked to the SQL server to use it. Although Access still uses Jet to interrogate the data. If someone can offer solutions to the above I would be interested in hearing them


David

 

[Banana]

I don't know what that means.

I have gained a general impression, which could well be wrong, that people doing the SQL Server deal use queries far more than I would.

At support.microsoft.com, there's few articles for Access, "Optimizing Server-Client application FAQ" for example, that could give you some idea of what is involved. It's not so much about having more queries, but writing it carefully. One good example would be avoiding using Jet function in the queries.

Well here's my tuppence worth.

The main issue I have between Access and SQL Server is the fact that I have been unable to create crosstab queries in SQL Server. Likewise I incoorporate custom functions within select queries, this again is not acheivable in SQL server. The referencing of dates is also different. Although I would like to use SQL the nature of the application tends to dictate the data source.

I have used SQL server to hold the data but used OBDC linked to the SQL server to use it. Although Access still uses Jet to interrogate the data. If someone can offer solutions to the above I would be interested in hearing them


David

I've seen some post a stored procedure which you can copy and paste to achieve a crosstab query in SQL Server, but looking at the procedure, it looks too much work that it's just simpler to query the SQL Server for the base data, and transform it within Access. I do find it interesting that out of all RDBMS (maybe with exception of Oracle?), only Access has native crosstab query.

For functions, you still could use PT queries and refer the stored procedures or functions, or in case where we need updatability, get away with a view. But as Pat alluded to, just sticking to vanilla SQL may be sufficient for our needs, supplementing the business logic within VBA.

 

[DCrake]

Banana

I tend to use VB 6 alot and using ADODB, by setting up a mdb and linking in OBDC SQL Server tables then referring to the Access mdb container seems illogical when you should be referring to SQL server direct. This why I detract from SQL as per my previous post.

When using Access to build an app it is dependant on the client needs and sizing issues.

David

 

[Banana]

Aha, in that case where Access is not used at all, then yes, you're pretty much stuck with that fugly stored procedure (I'm sure it's posted at SQLServerCentral or at least you can find it with a google search and copy'n'paste it in.

I'm not sure I understand the part "sizing issues" but want to clarify that if Access is a front-end client, then the size of data no longer matters; you could link to a data source that had 1 TB of data using Access. Otherwise, I do agree that it is dependent on clients' specific needs.