The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Design question - help

Suggestions appreciated.

I have a client who runs a small business from home. He and two others use a MS Access database very heavily throughout the day to process bookings and display big search lists.

He now has two pressing needs.
1) Speed up response time in the office.
2) Enable customers to see their bookings and other details over the internet.

For (1) I can upgrade to MSDE to speed it up. This will be fine.

So the problem is:
"To enable a web site to read/write a sub-set of the same data as his Access front end program."

--Rewriting the access front end would be to costly.
--Mysql would not suit.

He has a fairly reliable broadband connection at home.

Help
Thanks
Kim
Tuesday, October 11, 2005
 
 
I'm fairly sure Access can publish forms directly to the web, using IIS. It'll be in the Access help. It won't be at all robust, but for just *viewing* information (as opposed to doing anything transactional) it should suffice.
Larry Lard Send private email
Tuesday, October 11, 2005
 
 
Access web pages are only good for intranet use as they require activex office components and IE.

A couple of options I've pondered are:
1) Have a web hosting company host his web site with sql server hosting included and replicate the data to his office pc.

2) Host the web site at his office on a spare computer.

3) Host the web site at a hosting company but use web services to get the data from his home computer.
Kim
Tuesday, October 11, 2005
 
 
I've been using MSDE with some very large clients who don't require that many user licenses - but posts close to 1000 transactions per week.  It works great.  I highly recommend it.
How does the current login of the clients work?  Do they log into his database to check their bookings?

What works well is if you write a small login control that uses a web address stored somewhere in the registry.  And set the website up as a sort of a remote host.
Initial development might take a while.  But if you write it as a control with connection dll’s you would be able to use it with a number of other projects as well  (key is re-usability).
Celeste L Send private email
Tuesday, October 11, 2005
 
 
Kim, please don't assume that moving from Access to MSDE will automatically speed things up.

It depends a lot on what you want to do, and Access can be vastly faster than SQL Server, due to the shortcuts it takes.

Just a heads up really, so you don't promise the earth, then fail to deliver.
Distruptor Send private email
Tuesday, October 11, 2005
 
 
I agree with Distruptor. If the existing Access app is too slow, I'd look at the database design before doing anything else. If it's like a lot of Access databases that I run into, you might very well find that there are no primary or foreign keys and no indexes. Adding a few well-chosen indexes could do wonders for performance.

As for #2, there is no cheap way out of that situation. A client/server DBMS is required. Your customer will need to pay for that, for the web application to be developed, and for the Access app to be overhauled so as to use the new database. If, as you say, "rewriting the access front end would be to costly", he probably can't afford all that.

Good luck with this one.
comp.lang.c refugee
Tuesday, October 11, 2005
 
 
Actually if you setup MySQL and install the ODBC driver on the desktop, you can map Access to the backend and treat it the same.

I did this with some clients a few years back.  This way I was able to keep all their data synchronized according to their budget.
KC Send private email
Tuesday, October 11, 2005
 
 
I second refugee's suggestion.  Start with indexes.  You'd be amazed at the speed difference they make.  Imagine looking up a word in a dictionary that's not in alphabetical order!
Kyralessa Send private email
Tuesday, October 11, 2005
 
 
Thanks for your suggestions/comments. The indexes and database design is actually quite good. There are some queries that would be better done in a stored procedure though where the performance would be improved.

Anyhow, performance aside as it is the lesser issue:
The main problem I face is having a web site on the internet and a rich client application on a LAN both connected to the same data source.
The rich client needs to be responsive and show lots of data in tables, the web site will do much less data accessing.

Can he just host the web site or data accessing web services from his home office?
Kim
Tuesday, October 11, 2005
 
 
Yes, he can.

I would have some ASP/JSP pages on a Web server, which he can host, and which accesses the Database, MSDE in this case.

Alongside, I would implement some thick, or "rich" client, also with access to the database.

Should'nt be much of a problem.

Regards and Good luck!
Uwe Hehn Send private email
Wednesday, October 12, 2005
 
 
Haven't used Access for about 4 years...

The speed issue is possibly caused by Access dragging the entire mdb to the local client (unless this has changed in the last couple of years).

To remove this issue, use the upsizing Wizard and migrate the database to Sql Server (or MSDE). Then point the Access front end at the new SQL back end, which should work over an internet connection, assuming permissions etc have been correctly set up.

You should now have an a solution to the admin side of things.

Treat the "customer access via internet" as a separate issue an create web pages to access the appropriate data. [a separate issue because you have to deal with authentication, new design work, etc as well].
Justin Send private email
Wednesday, October 12, 2005
 
 
==>The speed issue is possibly caused by Access dragging the entire mdb to the local client (unless this has changed in the last couple of years).


Umm ... not to burst your bubble or anything, but Access has *never* done this, in any version. Sure, it might drag an entire index to the local client, or in the absense of proper indexing, maybe a whole table or two, but the "entire mdb" has never been the Access works.
Sgt.Sausage
Wednesday, October 12, 2005
 
 
"As for #2, there is no cheap way out of that situation. A client/server DBMS is required. Your customer will need to pay for that, for the web application to be developed, and for the Access app to be overhauled . . . "

Not true.  Using an Access database with web front end is fine.  If usage is heavy then it will have problems, but it doesn't sound like OP anticipates heavy usage.

Fog Creek's own FogBugz web app can use Access as its back end. 

The decision to upsize to SQL Server or some other database should not be made prematurely.  For lightly used web application Access will be fine. 

As far as general speed of Access in the desktop application, I agree with others who say check the indexes, make sure app isn't made to transfer entire tables to user when all that data isn't needed.  Access can actually be faster than SQL Server in a desktop setting if it's used correctly.
Herbert Sitz Send private email
Wednesday, October 12, 2005
 
 
Regarding web-enabling an Access database, as long as you don't require anything too customized there are sh*tload of tools out there that will give you basic access to all the data through a web browser.  E.g., http://www.codeproject.com/asp/ute.asp#overview

There are lots of others.  You could start from them and customize something if you need to. 

A possible problem is that you won't have the expertise to customize using these tools, or perhaps even to use the tools to automatically get at your data.  Some of them are quite easy to use, trick is to find one that works the way you like.  If you can't, then you're going to have to pay to have it done.
Herbert Sitz Send private email
Wednesday, October 12, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz