Access skills - HOT or NOT??

[alastair69]

If you search Access help files under the following "Microsoft Access database specifications", it says Access can deal with 255 users at any time, i have used it on ten users and it goes slow.

Alastair

 

[Kodo]

Kodo. You have helped me a number of times particularly with the asp.net bits and therefore I do respect your knowledge but I think that like every application it should be reviewed in the correct situation.

I agree, but you didn't give me any limitations

 

[Kodo]

IMHO - Here is the biggest fundamental difference: Link

I had forgotten about that. I built an Access app about 2 years ago for a few users and the db grew huge really quick and I ran into this problem. I never did get a chance to move it to at least the MSDE.

 

[Uncle Gizmo]

I thought this Thread deserved to be resurrected ....

 

[Len Boorman]

Uncle Gizmo you are right to raise this thread again.

I am now retired and I think it is worthwhile relating what has happened to a couple of the applications i created.

I was made redundant at the end of 2007. Well some 3 years later I was contacted to talk about some modifications. This particular application had had absolutely no support for 3 years but was still running fine. Needed a compact but who doesn't after 3 years of neglect. The application is used by around 20 users, some daily, some weekly but I was surprised and pleased that the application was so stable. Have been in several times to add/modify features. Did get a call in september this year for a minor problem. Total fix time 10 minutes including finding the problem.

Another application is still being used on a daily/weekly basis and I have never been contacted about a problem.

A good Access Application properly created by a trained person can be
1) Very effective for a relatively small group
2) Can support a large number of records. I have had 5 million in a simple data recording system with very infrequent need to interrogate
3) Precisely targeted to manage specific data in a specific manner

It is always necessary to select the correct platform for any given situation.
Access, SQL, Oracle all have their niche situations

Cheers

Len B

Only the unknowing criticise Access .

 

[pbaldy]

This is like "the more things change the more they stay the same". Somebody or other has been saying Access is dead for as long as I can remember. It's a tool. Like any tool, it's not suited to some tasks/situations, but it's perfect for others. Like Len, I've had requests to add a feature to some app in daily use and noticed in the version table that I hadn't touched that app in years. Others I'm in all the time, but usually adding something new they've thought of, not fixing anything. Properly done, it's a solid, stable, flexible platform.

 

[The_Doc_Man]

The whole argument of "can't deal with 1000 web users at a time" is a chimera. If you truly had 1000 users slamming an ORACLE back-end at the same time, it would exhibit performance issues, too. Just not as bad as Access-to-Access traffic. Not to mention that Larry Ellison started switching his product to the "per thread" licensing scheme just before I retired...

Access and a web-based front-end with an ORACLE back end would have the same capacity of DB threads. The difference isn't anything more or less than having a connection protocol difference. That difference is that when you connect via a desktop running an Access front-end, you have licensing fees for the Access product - and I believe you could distribute an Access Run-Time engine for which there would be no such fees.

The next difference is whether you authenticate to a domain controller or a RADIUS server (for desktop Access) or whether the authentication is done within the host of the database because the web is just a pass-through connection.

Then you would wonder whether the database services are part of a cluster that does shared data locking cross-cluster in order to get high volume and high availability. That is a function of the connection design, not of Access or ORACLE or SQL Server.

Saying that Access doesn't work so well for big apps is in part due to forgetting that there IS such a thing as having a heterogeneous database with an Access FE and something else for the BE. All of the problems with concurrent users are governed by the BE's capacity, and if Access is only the FE, that 255-user limit ain't there no more.

The REAL reason that IT folks don't like Access FE is when they have Access BE, the SMB protocol used by Windows file sharing is a nightmare both for security and for the implied volume of traffic it engenders, since a query has to process the entire table on the FE and that means reading the entire table from the BE.

If you could set up that split FE/BE to be heterogenous and use pass-through queries to be processed on the BE and see only the results on the FE, Access is no worse than any other system.

 

[Galaxiom]

since a query has to process the entire table on the FE and that means reading the entire table from the BE.

I had always been under the impression that this is not the case in a properly indexed Access table. The file system can handle the data at page level.

 

[CJ_London]

I agree with Galaxiom.

I think what tends to happen is inexperienced developers set a recordsource to a form or report to the entire table or query without criteria or sorting and use filtering and form sorting, rather than sorting and applying the filter as criteria to limit the number of records (e.g. on docmd.openform). This does require the entire table to be loaded. Easy to see in datasheet view for larger datasets - how long before the record count at the bottom appears?

In part, it's because access makes it easy to do this - click on a table, click on the 'create form' button, job done - an inefficient form created efficiently.

What user really wants to draw up a list in a combo box of 50,000 clients to find the one they want?

If you look at how web pages are designed - 20 or 30 records brought through per page, comboboxes with a limited range of options, search is a textbox and click a button. Then designed access forms the same way you would have pretty much the same performance.

 

[The_Doc_Man]

In specifically tailored tables with proper indexing, Galaxiom, you are right. For queries put together by inexperienced designers, the load cause by SMB is horrendous. Dollars to donuts that the IT distaste for Access is because so many Excel users "step up" to Access because "it is so easy" - but still think in flat-file terms and have no clue to the meaning of the word "index." There is the cause of IT manager distaste for Access.

CJ, thanks for amplifying my point. If you use good design techniques and have an active rather than passive back end, Access and Web are just two sides of the coin depending on two different connection protocols. When you design in a way that ignores network throughput, you ASK ... nay, BEG for trouble.

 

[CJ_London]

another 'gripe' about poor design is the liberal use of the initial * in a 'like' criteria which negates the use of the index so requires a sequential search. ADO doesn't even allow it

I accept that there are occasions where it is required but for many searches the user is typing in the first few characters of a name anyway - who types in 'ith' when looking for 'smith'? I prefer to give the option to the user with an option box (exact, beginning, middle, end) defaulting to beginning or simply train them to use the wildcards. And if the issue is the names are A Smith, B Smith etc - that's down to poor initial discovery about how the data is to be used.