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

 

[lgvelez]

Yes, EricZ, I did misinterpret your earlier statement. I believe that what we are going to do is 1) convert to MS Access 2000, and then move the back end to SQL Server tables.
Every 3 or so days I have had to repair the back end tables; this has only been so since our network (denied vehemently by our IT dept) was inundated with NIMDA ... my workstation in particular! I had to do so today, and am wondering if the SQL back end would help with one of the problems that drives me crazy: end users leaving for lunch still attached to the back end, making repair impossible until "everyone is out of the pool." I paged the end user and she never did return my call; I paged our IT person across the street, and he had not answered me in 20 mins. I then had my manager call the IT person's cell phone, and we were able to delete the ldb file then.
Our contractor Access programmer has developer a "quitter" - timing device that will close the database after a period of time of forms disuse, but until we are sure that we are stable after the virus attack, I am not willing to implement it.
I want to thank each and every one of you for your opinions. This is a wonderful learning scenario for me. Laura Velez, MCP
lauravelez@home.com

 

[Paul7905]

Geeze, seems Access is taking a pounding when put up for a multi-user business application foundation.

Here's a story for you. As the IS manager for the Catalog Systems development team for a billion dollar US specialty retailer (won't name the company but it's owned by Spiegel), I had a requirement from the companies Catalog Planning division to design and implement a system for use in planning the companies catalogs out over the upcoming year to include circulation, forecasting by page and then track sales against each with reforcasting capability etc etc. The requirement was for 75 concurrent planners etc to be able to use the system.

After a long battle within the IS department re: Access not being a multi-user business application development platform and the Tech services department refusing to support Microsoft applications (being big blue orientated they felt pc's and pc apps were intended for "games" and "toy" or spreadsheet apps only), I was able to sell the idea on the basis that we would use Access as a "prototyping" platform then, later, develop the app using "acceptable business application development toolsets".

Eventually, this "prototype" ended up being a full blown production business application. During the same timeframe, the companies Retail systems development attempted to develop a similar application for the retail store side of the business using more acceptable non-microsoft tools (Db2 etc etc) and the project was eventually scrapped after a year and a half of frustration for being virtually un-doable with the traditional application toolsets.

Eventually, the back end was indeed migrated to SQL server, but not because it was necessarily troublesome; the main reason being that several of databases were encroaching on the 1gb limitation.

Currently, I have had some experience using rather robust Access 2000 applications running on a Citrix server (all the databases and the code etc runs on the server, no jet, no db's on the pc's) and this works flawlessly albeit only with 12 users currently.

Paul

 

[svanels]

That story of the access.Idb file. Sometimes we logged into the database and killed (crashed) the computer. The lock stayed on the server. S. van Els
SAvanEls@cq-link.sr

 

[nomus1]

Paul7905 is right. Access is taking a terrible beating here. A well designed program can be a very effictive solution. Here is another success story that I hope helps.

We have between 50 and 75 users.

We have developed Access to interface with our accounting program and make the screens more user friendly. This way we can allow each user to enter the information they need, and keep adding to it.

To keep the database small in size, we split the database into 4 databases. 3 of the databases are for tables. These tables are linked to the 4th database that holds the querries, forms, and reports. This 4th database also holds any tables that are created for temporary data only. We have some tables that we copy nightly from our accounting database with over 200,000 records and these are stored in SQL and linked to database #4.

This 4th database is saved as an MDE file and we used some basic code to check the date stamp on the MDE file on the network with the date stamp on the users current version if the network version is more recent than the local version we download the network version onto the users pc.

We had been over using Excel spreadsheets and needed a central repository for the information and a more controlled data entry mechanism.

We now use Access for several parts of our business. Our main objective was to type the information once and let everyone build on it. Here are some examples:
Customer Contact File.
Starts with marketing and grows as the client grows.
Is only sent to the accounting program when something
is sold. Also keeps all cooespondence from anyone in
the company in one central place so that all can view.
Vendor Contact File
Starts with estimating and grows as the vendor grows.
Is only sent to the accounting program when something
is purchased. Also keeps all cooespondence from
anyone in the company in one central place so that all
can view.
Product Code File
Starts with estimating and is only sent to accounting
program when we need to purchase. Product codes are
created here to maintain consistency and gather enough
information about the code so anyone will know what it
is.
Estimating
Allows us to pre-engineer and use units of measure in
denominations other that the way we inventory the item
Engineering
Refines the estimate and links the estimate
information to engineering so that after we have
engineered we can requote quickly to make sure that no
mistakes were made.
Material Requistion
Allowing us to roll up groups of engineering requests
and request purchasing to obtain the needed goods.
Production Planning
Allows us to schedule the production of goods, link to
material requisitions to make sure raw materials are
here before production of order starts.
Shipping Schedules
Allows us to link to production schedule to make sure
goods are finished before required to ship.
Installation Schedules
Allows us to track dock restrictions, and cooridate
with shipping schedule. A change in either place
updates the other.
Commission Information
Allows us to have multiple salesperson/customer
relationships with different terms by item sold.
Employee Attendance
Allowing us to track temporary as well as permanent
employees

We only have a two person IT department. One for hardware and one for software. We have been able to maintain they system and respond to user problems quickly. We have learned a lot since we started and are now in the process of trying to speed up some of the processes (boy did we make some mistakes in the beginning)

I think it works well for our business because there are rarely anymore than 3 or 4 users in a given application, but there can be 40 users on the database at any one time.
We have hired out some of the development. Because there are only 2 of us, our biggest hurdle has been the stigma of a home grown system and our the lack of ability to keep up with the training needs.

 

[lgvelez]

nomus1, what version of Access are you using? I certainly appreciate your wonderful input. Our IT Dept is rather large, since the company has laid off multitudes, it is amazing how large this department has remained, but they do not respond to problems on our locked down network as quickly as needed.

Laura Laura Velez, MCP
lauravelez@home.com

 

[Toga]

I'm not an expert by any means, but I have been using Access 97 for the last several months and have really been quite surprised at how powerful it can be......or frustrating if your not careful. Our IS department has also been encouraging me to get my tables into the business server we have which happens to be Sybase server.

Right , Wrong or Indifferent, Ill throw my 2 cents into the mix based upon my limited experience and opinions I've developed.

The application I have been developing is a shop floor app which handles order entry, scheduling, maintaining customer specs, production data, assignment to orders and shipping.
There's about 30 tables and about 15 users. I'm still doing quite abit of tweaking and fine tuning but in general, works pretty nicely with very few issues.

The majority of the tables are currently Access tables but there are some such as employee list in this database which are linked in from Sybase. Once there, you can interact with them just like an Access table linked in.

For me, while I know in my heart that a profesional database server is probably the right direction to go eventually.....and while maybe lucky, I really haven't had any issues using the Access database for now.....

The biggest downside I'v observed is when there's several access databases out there being used....things like employee names, customer addresses, etc all tend to get duplicated for each specific application if your not careful.

My earlier reference to it being frustrating at times is that in my opinion, Access makes it too darn easy to do things wrong sometimes. I guess my point goes back to some earlier references to application design. If your forms are linked to large datasets...and if they tend to be slow.....they'll be even slower once you store your tables in a database server.

I've been spending alot of my time lately undoing many of my ealier mistakes by replacing many of my linked forms with unlinked forms and using recordsets for the retrieving & updating of data tables. 9 times out of 10 I'm only updating one record anyway....why have the whole table linked in. This sped things up considerably! The next thing I need to learn though is how to use pass through querys.

Anyway, another issue I've had is with the use of autonumber ID fields and allowing users to delete records. Works fine except....I realize now that I'll have to replace all of those deleted fields before I port any tables to the server or my references to records in other tables via that ID number is going to be all messed up. You can't just transfer your existing autonumber fields into the server. (at least with Sybase) If you define such a field, it will be repopulated by the server.

Don't assume all of your existing query's will work once you port to your server as either... Shouldn't be much of a problem if you just link your tables into access....but then you loose out on the power of the server.

I've inherited a couple other Access databases written by other people where all of the forms are linked to the underlying querys & tables. It's going to be a nightmare to reverse engineer everything someone else did with existing code. I'm beginning to think here that my time would be better spent just redeveloping the apps from scratch and design my tables on the server from the get go.

I guess all I'm suggesting with this drawn out response is that if you do decide to switch to the server database, don't under estimate the associated effort that might be required with your existing application once you do. I'd push back hard on IS until ya feel real comfortable with how your app will fuction. Alot of this stuff is easier said than actually done!

Going forward forward for me, I can see the advantages and intend to use the server as my backend and either Access or VB as the front end for any new apps. But as long as my existing Access apps are working OK, I'm going to hold off just transporting existing databases into a server until I gain more practical experience than I have interacting with the server with an application. Mainly cause I know once IS helps me transport any of my existing data onto the server......they'll consider their task done and be gone!

Hope this gut spilling response was at least a little helpful!

Toga

 

[nomus1]

We are using Access97. I think the part I like the best is it's easy interaction with the other outlook products. When the users are doing their correspondence they are using word. We have places in our code where we add reminders to the users outlook calander, and we have numerous places for auto-emails to be sent.

 

[lameid]

I'm still using Access 97 with a front end and back ends but I plan to moving everything to Access 2000 and SQL Server 2000 backend. Everyone here has office 97 loaded... My rough plan is to...
Load Access 97 on my machine and convert databases (leaving Access 97 in tact) and look at converting all the DAO to ADO.
Once everything works, load office 2000 on everyone's machine and roll out the 2000 files.
At this point if I haven't already done so convert to an MSDE back end for testing and when the time comes roll into SQL Server.

I agree this is roughly the approach to be taken. I haven't used ACC2000 yet but from what I've read this is the correct approach. Additionaly I have read quite a bit on using Access as a frontend to DB server. The catch is to ensure you optimize otherwise some things may get slower. The trick is to have access use SQL pass through queries to retrieve the data you need or to perform updates. Access's biggest weakness for data is that the client reads all the data (possibly just appropriate indexes and then related records) and then does the calculating. The network is the bottlneck in almost all cases. If access just send the appropriate query and gets just the appropriate results from the server you have improved network performance. You also need less robust clients to handle the processing. In this way the IS dept gets stuck with the tab of having the adequate processing (I got the impression that each department has its own budget and your dept is likely stuck buying its own PC's).

One thing to consider is that your IS staff is right in their statement about SQL server being better. Knowing this, they may be frustrated in being called about Access problems. Consider that they will have more time when they help with fewer Access problems. Also realize that they may know SQL Server and not know Access. They may be able to support SQL Server well (even though it is very low maintainance). If you do what they want, they have ownership of the database and may be more likely to support it. You will still own the data. As the developer you need to also come to an understanding about how changes to the backend will be done. Especially tables. You should have the ability to create views (basically like access queries that can be linked into access as tables). However modifications to tables becomes a lot trickier. Hopefully you will never have to modify a table but this is the real world.

That said, don't forget that you may have some boneheads in your IS dept and no matter what happens you won't get adequate support.

If your lucky, your IS dept has a DB administrator and a network administrator. Sounds like you've been dealing with a bad network administrator and the DB administrator may be o.k (or the network admin doesn't want to deal with DB stuff <Access>).

The organization I last worked for had an excellent network administrator and a moron for DB administrator. I interned there as a programmer and left sooner than later because the DB admin got in the way. In short know the IS dept and who you can count on. In the case above, if I had an SQL server problem, I would research it and get the network admin to fix it.

For better or worse I am the IS dept where I work (jack of all trades and master of none, except Access 97).

I hope the above is good political insight for you. After all, politics is the real problem here (if nothing else the IS manager needs to replace some people).

 

[ErikZ]

Toga, thanks for all the information! I'm newish, so I like hearing what other people had to deal with. Have you taken a database design class? Also, I just have a question, where do I find more information on using recordsets for the retrieving & updating of data tables?

Also, I like reworking someone else’s work. The user has had time to work with the product, so they have a MUCH better idea of what they want. No matter how skilled the original writer was, you know he/she didn't think of something and had to add junk to get certain things to work. I can just sit down, map the whole thing out, and then design the new database! It will be clean and easy to read.

I also find it interesting that you see linking a form to a table as a mistake. This is only a problem in Access, because it tries to load everything. You are already working around the limitations of Access!

How big is your database? I was just wondering why you have it on three computers.

 

[Toga]

Erik,

Between the Access 97 Help files and right here, you can probably find all you need to get yourself going with recordsets. The use of transactions for record locking and updating sveral tables is another thing worth spending some time with.

I haven't taken any database design class. Was it that obvious? One thing I think I've learned here though is that enough can't be said for the time you should be taking take up front understanding the process before you even begin to touch your keyboard!

Yes, from the perspective of viewing someone else's work as a prototype to sort out what works really well and not so well, I agree 100%. It's an enormous help wrt your redesign. Supporting / Modifying what's there however can be painful depending upon the approach that may have been taken and how it compares to your own programming style.
Don't get me wrong...there's usually alot that can be learned getting to read someone elses code.

The biggest issue for me with the applications I was referencing above is that the forms are all bound to tables & querys through out and the programmer was using bookmarks all over the place in his code. I haven't used bookmarks that much and I just think it would be easier in the long run for me to redesign it vs trying to port the tables into a server and then strugling to figure out / adjust someone else code accordingly. Also, these apps aren't that big so I just feel like I'd be further ahead in the long run supporting my own code.

To your point, I have enpough trouble figuring out my own code after I've gone back in here & there to add this n that or make something work a little differently from what I originally intended.

I assume your last question was for nomus1 who has 3 databases he's using. I think he was doing this because of their size.

Toga

 

[Freckles]

I am in a very similar situation to what many of you are describing. The IT department bitching all the time about Access (currently 97 -- soon 2000), and saying that the answer to all of our problems is a SQL Server. We are networked to a single server.

BUT, none of our databases, even with all the queries, forms, tables, reports, etc., are approaching 1Gb. And the number of users can never exceed 9, because that is the number of people in our department. AND I have never seen everyone logged on to the same database at the same time.

What else could be causing the extreamly slow response sometimes. Network/LAN connections have been checked, and according to IT, running just fine.

Your comments are much apprecriated. : Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Gazonice]

I have developed several commercial applications with up to 40 concurrent users - using Access 2000, 1 backend, and up to 6 front ends, relevant to specific departments.
Apart from the 1GB size limit, I have had an extremely good reliablity record, with a more than acceptable network performance speed.
I think good design, coding and a stable network are the main criteria for a solid Application.

Garry.