Need some Access success stories, please 1

[lgvelez]

We have a mid sized split MS Access 97 database that we are about to convert to MS Access 2000. There are fewer than 50 users, with fewer than 20 users attached at one time. Growth to the database's potential will not happen for a long time.

Our IT Dept is pushing for backend conversion to SQL server, and we are being told that MS Access is not professional enough to handle business applications. "Access not designed as a business application. The security model of Access is very simple and the stability is not very good when it exceeds a certain size" is an exact quote.

We would like to accumulate some MS Access success stories as applied in a professional environment, ie, number of users, number of tables, type of application, etc.

TIA, Laura Grant Velez Laura Velez, MCP
lauravelez@home.com

 

[dneufarth]

Just out of curiosity, why are you opposed to the IT suggestion? Are they willing to support the SQL Server and underlying hardware? Any cost to you?

Dave

 

[turtlemaster]

There are many stories of this nature but I wouldn't call them success stories. Access is not designed for a multiple user environment (not true multiuser) - especially business applications. You are just asking for trouble to try and save a couple of bucks. Why do you have an IS dept? I have had this happen many times where my employer was too cheap to buy a real dbms and let me tell you that it will cost you more in downtime, slow processing, and other headaches. Don't get me wrong, I'm sure many people have got away by doing this but you are taking a gamble.
With access there is no centralized database engine. Each user uses their own engine when they open the file. The only way these engines communinicate is by locked records (which can lead to even more problems). SQL Server (or even oracle or something) is an enterprise solution that will handle this business app. It runs on it's own server which manages all connections to the database. No actually connects to the file itself. It just makes sense.

Thank-you for letting me vent LOL
Nick

 

[lgvelez]

No, we do not want our IT Dept to support the SQL Server -- they are notorious for not responding in a timely manner, and this db, in particular, does not need to wait for them to act. We probably be alright after converting to Access 2000. Considering the massive influx of Nimda that we had, and they were neither proactive nor reactive, and were very inactive, we can handle it ourselves. None of us in this department are impressed. Laura Velez, MCP
lauravelez@home.com

 

[ErikZ]

The problem is, that when the growth DOES happen, you'll probably find yourself locked into Microsoft proprietary format. Are they insisting on MSSQL?

Also, I've been seeing a lot of questions dealing with Access's problems with handling multiple users. If you don't like your IT department, maybe you should make your own back-end? To be honest, if they install a professional DB system, they really won't be doing much to keep it running. So you shouldn't have to worry about their unresponsiveness.

 

[dneufarth]

If it ran successfully on 97 and you feel IT is poor service provider, try it on 2000. Whatever you do, convert, test on a TEST database. Don't convert your live production data expecting it to be ready to go the next morning. Especially if the 97 database contains event code.

You may not need it, but SQL Server will need transaction logs and backups. Not necessarily a DBA, but some expertise is needed to support over the long run.

Dave

 

[lgvelez]

Dave, your scenario is exactly where we are going. We may have a potential need for SQL Server, but our first step is to go to Access 2000 (we have our own servers to use that have SQL Server 7.0 already installed), and to shake the problems caused by corrupted software. My boss is showing a willingness to send me to SQL Server school (I am an MCP in the NT4 area), but with the economy the way it is, who knows if the training will happen. Laura Velez, MCP
lauravelez@home.com

 

[rcrothers]

Hi,

I have used Access 97 dozens of times for professional applications and would say that although it has limitations, for small scale apps it is fine. It does have a database size limit of 1Gb though and this will pose a major problem if you are storing large amounts of data. The argument for a server side dbms is quite valid. I have a number of sites using Access where there are more than 10 users and performance is slow particularly when queries returning large recordsets are executed. Access security is also pretty poor (unless you write your own security routines) and does not integrate well with network security like SQL server does. If your database is small to medium in size (i.e. 10 - 150mb) and has less than 15 users and none of them are on the end of a low bandwidth link, I would opt for SQL server.

These are my own views from using Access for a variety of applications over the last seven years. Hope they are of some use to you.

RogerC.

 

[Lightning]

What nobody seems to have mentioned is that there is a middle course that you could use, and that is to use Access as a front-end to a SQL Server back-end. That is, all the Access forms attached to SQL Server tables.

I've never worked in this environment myself, so I can't give much detail on it, but this type of set-up is quite common and may offer some advantages in your situation.

Perhaps some other forum members who have worked in this type of environment can offer some insights into the advantages and disadvantages?

Just a thought

Lightning

 

[lgvelez]

That is the second option, after running the split database in Access 2000. I would really appreciate the input on the Access front end and the SQL server back end.

Laura
Laura Velez, MCP
lauravelez@home.com

 

[GingerR]

We have problems with our IS group too--poor response, not enuf skill, not customer-oriented. we leave our stuff in Access, but have now acquired funding for sql server space, so we will learn the sql server dba stuff ourselves and put our backends there with Access front-ends. someone else in another part of the co. we work with has done this, with an up-front promise of support from IS. all he got was a headache. ended up figuring it all out himself, took much longer to finish the project. so as you can see i am wary of IS in general. this is not the only instance, just an example. if you can get a promise in writing of ongoing skilled support, i'd say go for it, sql server is better for multi-user. but unless you're confident in them, dont do. push to learn it yourself.

g

 

[svanels]

As long as you use access for a desktop system it is allright. We also had a wise guy trying to push everything in access and use it as a multi-user system.
Nothing is more anoying then access telling you 3:00 am that there is some jet-engine error and another user locked the tables, when you are the only one on the plant.
So everytime we had this, we called in the wise guy to repair his stuff. So he left. S. van Els
SAvanEls@cq-link.sr

 

[UncleJack]

I've used Access for several years in commercial applications that I package in a run-time environment. I have systems running on every continent except Antarctica, but including Australia and Greenland. BUT and this is a big BUT, they are single user systems only. My experimentation with using Access as a multiuser database has revealed it to be very poor in a network environment and I wouldn't recommend it. Even 5 users can cause it to run with glacial speed. Go to SQL Server, Oracle, or even MySQL, if you want anything but a prescription for disaster down the road.

Uncle Jack

 

[dneufarth]

Laura,

Not sure if you realize that SQL Server is just the database engine. Transact-SQL (SQL statements) are the primary interface. SQL Server provides GUI components to shield you from the SQL so you can manage the DB more easily. The pretty front end that provides forms and reports does not come with it. Access may be used as the front end (other ways are VB, PowerBuilder, ODBC, etc.) For the most part all tables from SQL Server are linked tables in Access. Processing still occurs on the client side.

SQL Server power comes from multi-user capabilities and server side processing. You'll need to use pass-thru SQL in Access to take advantage of server side processing.

Dave

 

[cmmrfrds]

I recently completed a project that uses Access 2000 as a front end to Sql Server 2000 and we are pleased with the total package. The database has about 70 tables and the access front end has about 100 forms and lots of vba code. Also, about 50 stored procedures. It started with about 20 concurrent users and now there are 40. Since all the users are remote and running broadband (cable, isdn, dsl), the access front end is running on a citrix server. We have a citrix server for every 20 users. The advantage was that we could develop with all the niceties of Access and retain all the power of the Sql Server. This is a mission critical system and we are developing more confidence all the time in this mix of software. This is definitely more robust than active server pages on a web browser. If you have developer ability, Go for it!!!

Jerry

 

[MichaelRed]

Just to offer the perspective you asked for, I have developed Ms. Access apps for several organizations and find that MOST of the issues which are cited here (and in general) are just the result of bad design / coding. Access security is quite robust. I have never had anyone 'crack' the system without resorting to professional 'tools'. I have had a system support multiple users (up to about 20 with no real degradation in performance and up to 40 without much complaint from the users. Above this, there were some rather 'vocal' complaints. This app was a call center database for equipment installation, which logged ~ 1000 calls per month, with each 'call' having at least four 'hits' in the processing, and averaging a few more than that. the app was under the control of the Q.A. department and considered mission critical for the company, so some very detailed statistics for the process were collected and maintained both by the app. It was (at the point I left the organization), available > 98% of the time and had not had a down time of more than 1 Hour (contiguious) in over six-months.

When I left the Org, "IS" was goiong to convert to VB/Oracle, with a planned implementation schedual of ' ... a few weeks ... ". After six months, the org called and asked if I would return and make some modifications to the original app, as "IS" was still not ready to release their version, and would not " ... would not take time out of their schedual ... " to do maintenance on the legacy app.

On the other side of the issue, Ms. Access DOES experience some problems under (heavy) multi-user loading, and it DOES have some issues dealing with large data sets - when using the Jet db. fortunatly, the 2K ver will let you 'have your cake and eat it too', as it can be set up to use the MSDE (?) engine - which is just a mini version of SQL Server. I believe that the liscense is just for five users, but you can purchase additional liscenses from Ms. So, moving to the 2K version will let you retain the current program and convert (a copy of) it to SQL Server in parallel. There are a few 'tricky' issues in this, but the only real problem will be the political firestorm. Once the conversion provess is complete, you can directly move the MSDE objects to SQL Server. They don't just share the format of SQL Server objects- they ARE SQL server objects!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ErikZ]

Michael, I agree with what you said. A well-designed database will give you far less problems. Now, give me a rough estimate on what percentage of Access users has the skills to design and implement such a database.

S. van Els, great story! Zealots bother me.

Uncle Jack, MySQL? Isn't it missing some important features, like foreign keys?

Finally, to Laura, I thought I suggested using Access for the front end, and using a real DB for the back-end, but I guess my communication skills are lacking.

Good luck! I'm very interested in seeing how your situation turns out.

 

[turtlemaster]

MySQL - has forieng keys but can't run sub queries (or sub selects) at least in the version that I used, but darn is it fast.

 

[JAXDBase]

Ahem, to put my two cents in, I write apps in both FoxPro (considered by many as a middle brother between MSSQL and Access) and Access 2K. I have found that Access allows you to create a nice front end to connect to MSSQL or FoxPro or the native Access tables, and I support the statements of the others B4 me. What I have learned though is that you could have a backend Access DB(that only houses the tables) or a FoxPro *.dbf file that the front end clients can connect to.
Then the client uses pass-through style queries (keyset vice dynamic) and extract only the data / recordset you need from the backend DB and do processing on the client side "in and out". Writing is similar. This will reduce the "locked record" error since the clients spend the least amount of time connected to the backend then come out. I suspect that the current apps use dynamic connections that unfortunately slows down the entire networked access by forcing Access to manage the individual client connections. I have experienced a similar but worse situation with Excel used by 3 or more clients. The FoxPro DB engine is quite fast and you just link to each Fox file and treat each as a table.

In fact I have an Access app that extracts records from Fox files > 7 million records, write into an Access table and do lots of SQL in loops for data mining. And the Access 2K has never bombed!! So... like EricZ it would be interesting to know what you develop. Best of fortunes!

 

[jgoodman00]

I agree with the MSDE approach. As for the licensing, a user license allows connection of up to 5 users to an MSDE database. What happens if ever user has office, & subsequently MSDE? Does this not then entitle them to connect to the database, as they have a license for it themselves??? James Goodman
j.goodman00@btinternet.com