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

 

[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

 

[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.

 

[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.

 

[Mike375]

My only real contact with the big data bases are the insurance companies I deal with. I often have other insurance blokes say me things like...why can't the insurance company do what you do. Is Access better than what they use.....Obvioulsy the way of thinking by the people making the big DBs is very different.

Sometimes a client will require a letter outlining policy details and it will need to come from the insurance company. What always amazes me if the person has different policy numbers and perhaps his wife owns a policy on his life etc the insurance company can't seen to manage that in one letter. Yet it is so simple to do with a Word doc having linked queries in it.

 

[DCrake]

Banana

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.

Are we talking all version of Access or Access 2007?

Have yet to stick my toe in to the oceans of Access 2007. Have it on my home laptop but as I monitor the number of issues raised on this forum I am waiting for the tsumani of quesions to turn tide.

David

 

[Banana]

Mike,

I'd want to find out what they are using. It is possible that they have legacy system to support, and thus hampering their ability to introduce new functionality without breaking the system. But yes, that is definitely where Access shine; the ease of data presentation in contrast to low-level language at expense of reduced control and power. Even so, if there was just one or two requirements that was beyond Access's grasp, we could just use a COM library to complete the requirement and still take advantage of Access's built-in functionality without having to write pages after pages of code as one would have to if it was to be written in C or Java.

DCrake,

AFAIK, all versions will work, provided that the data is in an ODBC data source that support data storage greater than 2 GB. Of course, if we were using Jet, we're limited to only 2 GB. Another great thing about Access is its data access technology is not found anywhere else, even in ADO.NET, OLE, or whatever it is they're using those days. You see, in those environments, it is more common for programmers to request a disconnected dataset and perform a bulk update. That strategy certainly has its place, but only with Access, can you have a persistent connection and intelligence over the data retrieval; that is Access/Jet will retrieve only enough rows to paint the screen even if you linked to a 1 billion rows table, and will either lazily fill the recordset in background or get the requested rows when users move around and no more than that. This can't be done in other data access technology, as far as I know.

Furthermore the fact that Access comes with Jet provides us with extra flexibility in developing a client that can perform local processing where it is appropriate, without having to do it in programming language what would be easy to do in SQL. In such scenarios, Access could even help reduce the burden on the server and thus allowing for more concurrency than if we just used a C front-end client without local database engine (or have extra work in setting it up and providing the same functionality).

All of this comes at a cost of increased network traffic, chatter as some would call it, but to me, that cost is far more preferable than bulk requests and updates for most scenarios, and even where bulk requests or updates are appropriate, this can be done with ADO and/or using snapshot recordset.

As for 2007, I can't help but feel that it was a 'meh' update, being more eye candy than actual functionality so I don't believe we're missing that much.