Difficult problem 1

[noaaprogrammer]

I am trying to develop a solution for an insurance business with offices in 4 different cities. They use MS Access to store their data. They currently have 4 independent databases (exactly the same) running locally at their offices. They want to merge the databases into a single db, and store it at the central office. They will then need secure access to that database from each office to make updates.

What is the best way to do this?

 

[carmo]

How many concurrent users will there be throughout all the offices? I could tell you right now that you need (ideally) to have a web based solution. Your layout will involve using a backend database to store the data, and use the web as your front-end interface where the users could interact with the database. You could still keep MS - Access as your backend database. Create web pages that dynamically connect to the database. Give me more info...

Carmo

 

[noaaprogrammer]

Thanks for replying. If everyone was logged in at the same time, there could be as many as 25 - 30 concurrent users. Although, that probably won't be the case very often.

I kind of had the same idea. I have experience with developing PHP scripts to interact with a mySQL db. I thought it would be best to use ASP to connect over the net. However, they already have quite a few Access forms developed, so they would like to be able to run those. Can those be run over the web? Sorry I left that out.

Their plan is to have a setup like this:

1) Buy a Windows server running IIS
2) Have the Access db on that machine, where their employess can login and run the Access forms to update the db.
3) Have ASP pages on the IIS server for authorized public users to run queries on the db to view (not update) data in the db.

Hope this is enough information to help you. Feel free to ask more questions. My experience is in programming. Writing scripts and ASPs is not the problem. I just don't have any experience in setting all of this up from scratch. Thanks for any help.

 

[Elysium]

Can I ask why you aren't considering SQL Server or something of that scale? Access is fine, but to a point.

RS

 

[noaaprogrammer]

It's not my choice. They are already set up and using Access, and Access forms. If it were my choice, they would be using Apache, mySQL, and PHP on a Linux box. I am doing research, and it looks like Access forms can be converted to ASP's. If that is true, I will probably recommend that they convert to SQL Server. Is that what you would advise?

 

[Elysium]

As you know, we all have our own opinions. So, my thoughts are to move away from Access in an environment like that because of Access's frailties. It's good for small numbers of users, but when you are using it in a production environment with multiple hits and numerous users, I would be a little leary of it. I am not saying that you have to use SQL Server, but I would shoot for something on that scale. I work at a Fortune 50 company and we were quickly moved away from Access and over to Oracle and SQL Server. And really, some of the databases sitting on those servers are used only by a handful of people, maybe 25 max. Even in that scenario, we wouldn't trust Access.

However, we are limited to the hand that we are dealt so sometimes you just have to "improvise, adapt, and overcome".

That's my opinion

RS

 

[noaaprogrammer]

Okay. Say I can convince them to upgrade to SQL Server 2000... What do I need to do to get those Access Forms to run for that new db? Will they still work with it?

 

[MichaelRed]

hmmmmmmmmmm,

the contrarian in me screams to offer rebuttal,

I have reasonable used Ms. A. (ver 97) with up to 756 concurrent users and not suffered any problems at that scale. It was a relatively busy "call center" database with a few thousand records addded per month and each record was updated an average of three or four times, with >> 90% of the activity on any record occuring within the first 24 hours.

My overall experience in Ms. A. has convinced me that the huge majority of trouble with Ms. A. is the lack of professional 'practictioners'. Ms. made a somewhat grevious error in the release of the program. It was (and actually IS) to easy to 'just use it', so that is what has happened. Hordes of individuals with little or no programming exposure (much less experience or formal education) simply jump into the deep end. The same is true of the database aspects, and the universal outcry from the 'unvarnished' is to blame the messenger - or in this case the program which gives the message.

Ms. A. DOES have some limitations which make it un-suitable for truly 'entertprise' size interactive processes and HUGE scale applications, such as datamarts. On the other side, it is VERY well suited to generating large and complex reports, and can easily be managed to utilize the strengths oof the 'industrial strength' db gorillias (SQL Server), and in fact comes 'out of the box' with the SQL Server engine available to it (with a lmited number of user licenses).

I have implemented (even back w/ ver '97), reports of several thousand pages (although it DID take a while to crunch through the numbers and it was necessary to have a full time baby sitter for the printer).

In nearly ten years of using Ms. A. I have been forced to adopt numerous 'orphan' applications in the various releases of Ms. A., where the original directive was to simply learn enough about what is was supposed to do to be able to re-write in in a 'more suitable' language. In all but one of these, all that was necessary was to somply correct the sloppy neanderthal approach of the existing application.

I would generally attempt to take the approach of not fixing what ain't broke, and not buting new where is can reasonably be fixed. So, in contrast, I would suggest a reasonable amount of review of the existing application with the concept of building up, not tearing up.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Elysium]

Here's why we don't use Access on large-scale apps. We do use it for quick information, however.

"As per Microsoft technical documentation, Access databases should not be use on an enterprise level for web applications. MS Access mdb file size should not exceed 40 megabytes, total user base should not exceed 50 users, and the concurrent users (simultaneous users) should not exceed 5 users. Consequently, any single Access mdb file larger than 40 megabytes, user base greater than 50 and/or concurrent users greater than 5 is recommended to be converted to a SQL type database (Oracle or Microsoft SQL Server) provided by the application owner. Intranet Systems will not support the use of MS Access which exceeds these guidelines. Intranet Systems will also require any MS Access found in production to exceed these guidelines to be removed.

Microsoft does not support Microsoft Access databases using linked tables to another external database (Oracle, MS SQL of MS Access)."

Like with anything, some people can use a product and never have a problem either by chance or they are very skilled. Then there are those that will crash and burn no matter what their skill level is. I agree with Michael, it all depends on the user. Also, if Microsoft doesn't really trust this product, shouldn't that say something?

RS

 

[KornGeek]

Elysium,
Where did you get the information you quoted? I have been unable to find any Microsoft documentation that says anything remotely close to that. Do you have a link to a document that I could review? I want to know what the limits of the database I am create will really be.

MichaelRed,
How do you get 756 concurrent users? All of the documentation I have seen says that Access limits you to 255 concurrent users. Do you have a way around this?

Thanks for the information. Sleep is for people with no caffeine.

 

[Elysium]

KornGeek,

I got that information off of our corporate web site. I will do what I can to track down their source, but it will be a few days for that.

RS

 

[KornGeek]

Thanks,
Your effor is appreciated. If it's off your corporate web site, it's possible that it was written by somebody who wanted to "flavor" the information to support the conclusion they had already reached. However, if Microsoft is acknowledging limits, then I want to be aware of these.

One thing that struck me as odd, is it said that Microsoft does not support linking to other external database packages. Isn't that what they designed Access Project for, or am I misunderstanding something? Sleep is for people with no caffeine.

 

[MichaelRed]

756 concurrent is a type. shuld be 75

and, the whole point of ODBC is to be able to "cross connect" data sources (providers and consumers in Ms. Speak). Additional 'efforts' in this direction include ADO ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Elysium]

MichaelRed,

If you are referring to my comment, then the explanation would be that Microsoft isn't going to guarantee the ODBC Connections. Why? I don't know, but any good company always leaves an escape door open.

RS

 

[MichaelRed]

perhaps, but then MS doesn't even "Guarntee" that the "Jet" db enging will be in future releases of Ms. A. There is a great deal of speculation re inclusion of MSDE is simply a first step in the evoloution of Ms. A toward being ONLY a front end. Further, I have seen articles which cite MS as announcing that DAO will eventually be dropped. carried to it's (il)logical extreme, it is easy to reduce the future to Decartes soloution ("Cogito ergo sum&quot. A, perhaps, more viable approach is to simply attempt to stay within 'reaching distance' of the bleeding edge of the techno tools without severing the connection(s) to that which actually does work. I TRY to stay a release or two behind MS, relying on the historical evidence gathered by the many bleeders which generally agrees that MS takes three releases of a 'technology' to get is really working and a few addtional attempts to work out the bugs.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Elysium]

KornGeek,

Here's what our corporate IT group sent back to me about the MS Access figures:

"Thanks for clarifying your position xxxxxxx. We’ve frequently been challenged on the use of MS Access. The size and concurrent use figures are not available on MS site. We rcv’d that info from conf. calls to MS Engineers during troubleshooting."

So, there you have it. That's why you haven't been able to find the information on the web. MS wants to charge you at least $250/hr to tell you that MS Access is not everything most people think it is.

RS

 

[rorubin]

You guys have gone way off subject. The person who asked the question is confined to business requirements. As such a viable solution to your problem is to replicate the database. No you will not have one database you will have several but they will be the same database, and I don't know that will probably be an easier sell then buying SQL Server 2000. Microsoft has a white paper on this topic.

http://support.microsoft.com/default.aspx?scid=/support/access/content/repl/replintwp.asp.

And by the way Access was designed specially with the intention that anybody should be able to write a program, so take it easy. It's just a database!

I hope this helps you out.

 

[rorubin]

I noticed you said the (same) database are you already replicating the databases? I had posted and then I realized that you had said the (same) database.

 

[noaaprogrammer]

Thank you for getting back to the topic.

They are not replicating the db now. I guess I worded it wrong. What I meant was that they created the database and it's forms, and distributed it to the four different sites. They all input their individual records into the db. They want to merge them into a single, centralized database.