Small Business Application development

[El Salsero Gringo]

a) Yes, unless you and your clients are into open source.

Don't choose Access unless you and your clients are really into "closed" software.

[tsh]

My guess is that the larger problem is going to be the database design. Unless you've done this sort of thing before, you'll end up re-structuring it many times, even if it seems to be simple.

Two hints (apologies if you're familiar with this)

1) key every record by an arbitary number. Everything else might change later.
2) Build up everything as lists of lists. I've no idea what common database tools can cope with nowadays, but 20 years ago (when I was writing the sort of application you're talking about) this was enough to limit your choice of tools!

Sean

[bigdjiver]

... I always recommend ...

Hmmmm

I think Access is too limited for future expansion,

In many cases so do I, which is why there needs to be the option for scaling up to SQL server, or another database as a back-end, and then the option of going into a Visual Basic front end or web forms or something else. However most people do not need a bulldozer for their front garden, and most people just have the front garden.

I would not have that as first choice

There are installations with huge databases that use Access as a first choice because of the power of its tools. They can get a prototype application up and running very quickly so that the clients can see what they are asking for and decide what they actually want very quickly and cheaply.

- only as an option if they already have it

or, I would say, are used to Microsoft Office products, and not having to learn a completely new interface.

and have the time to develop what they need in it.

Access is for people that want to save time when developing new products. It has an amazing range of tools built in.

I suggested the open source alternatives above because they came with all server software and the application itself.

You can put Access onto a server. I an not clear what you mean by "come with the application itself." Do you mean the development application?

I'd agree that the wizards are simple but you're forgetting just how difficult it is for n00bs to design the structure and decide what data needs to be stored in the first place, best if its all there.

I did not forget that at all. I suggested that the n00bs looked at the many sample solution for one that was a close fit, and used all of the help available out there, so much of it free, to modify the application to suit. Gus said time was an issue.
Designing a database is fundamentally the same whatever method is used. One place Access scores over many is that it automatically diagrams what has been designed so that you can see how everything relates, and can see many problems from across the room.

[bigdjiver]

My guess is that the larger problem is going to be the database design. Unless you've done this sort of thing before, you'll end up re-structuring it many times, even if it seems to be simple.

Two hints (apologies if you're familiar with this)

1) key every record by an arbitary number. Everything else might change later.

The facility is called Autonumber in Access. For example it is a fundamental error to use something like a Membership number as a key. Many readers will have multiple membership cards. There are many superb tutorials on database design on the Microsoft MVPs sites that I referred to earlier. These are not product specific, but general principles.

2) Build up everything as lists of lists. I've no idea what common database tools can cope with nowadays, but 20 years ago (when I was writing the sort of application you're talking about) this was enough to limit your choice of tools!

Sean

Sean, things have so moved on.
Some free Flash video tutorials to show you what I mean.
http://www.datapigtechnologies.com/AccessMain.htm

[El Salsero Gringo]

Oh goodie. An argument about databases. My turn to buy the popcorn, I think....

[Gus]

Funny thing about 'Pro' developers. Access always seems to be a beating but over the years the most robust systems that have actualy met user requirements have tended to be Access based. I find it strange that for all the new technology and great boasts of new software tools FDs claimed (somweher) that 80%+ of IT development fails to meet expectations. Now, whether thats down to techies being too caught up in being clever or users being too dumb to specify correctly is a whole thread in itself

[under par]

Oh goodie. An argument about databases. My turn to buy the popcorn, I think....

I'll have some popcorn please ESG ,

would you mind ever so if I sit down next to you 'cos I think you might be able to translate it for me!

Its like listening to a debate in Lithuanian or Latvian to me

[Dreadful Scathe]

Hmmmm

Hmmm to you too , I recommend and prefer one server for everything. Administration of client machines should not be needed, ever.

(bring back terminals)

You can put Access onto a server. I an not clear what you mean by "come with the application itself."

The end user application that the users will use and that administrators administer. Better to get one that does what you want rather than write one, no mattere how many wizards you have - developement choices take time , even when accompanied by nice pictures.

Designing a database is fundamentally the same whatever method is used. One place Access scores over many is that it automatically diagrams what has been designed so that you can see how everything relates, and can see many problems from across the room.

All of what you say is fine, but i suggested a pre-built application so that there arent ANY problems with an already user tested application.

[Dreadful Scathe]

Funny thing about 'Pro' developers. Access always seems to be a beating but over the years the most robust systems that have actualy met user requirements have tended to be Access based.

Perhaps in SME's that is true, its certainly not true for bigger companies. But your implication is correct - keeping it simple is the way to go, its the many divergent, disparate, proprietry products and technologies that cause the dubious robustness and scalability of many IT departments. Note Im not talking about specific apps or servers (which on their own may be very robust) but the computer section/IT department of any decent sized company.

I find it strange that for all the new technology and great boasts of new software tools FDs claimed (somweher) that 80%+ of IT development fails to meet expectations. Now, whether thats down to techies being too caught up in being clever or users being too dumb to specify correctly is a whole thread in itself

only 80% ? Try 90%+ The reasons are a bit of both - the end users dont know what they want, the IT people have their preferences, biases and particular skill sets

...but the main reason that many companies, councils and the like have failed expectations is one you missed - the people with the money buy anything that impressive sales people will sell them - only later do the users and IT people have to make the best of it

[Gus]

Perhaps in SME's that is true, its certainly not true for bigger companies. But your implication is correct - keeping it simple is the way to go, its the many divergent, disparate, proprietry products and technologies that cause the dubious robustness and scalability of many IT departments. Note Im not talking about specific apps or servers (which on their own may be very robust) but the computer section/IT department of any decent sized company.

Actualy, to date, I've only come across Access based systems in Blue Chip orgbnaisations, e.g. RSA, HSBC, Credit Suisse etc. There is an increasing requirement for local solutions to deal with data to specific requirements and allow manipulation and reporting accordingly. Even with ERP systems such as PeopleSoft and SAP, these have real limitations, and the business community (and consultants like myslef) simply don't have 6 months+ to wait while IT departments roll out applications that fail to work. Sorry, but I speak from bitter experience. AND don't even get me started about any Business who has been stupid enough to outsource their IT department. Anyone know any business that doesn't now regret that decision?

[Beowulf]

Some good points in this thread.

As a professional programmer I wasn't poo pooing Access. It has it's uses and for what it does it's quite good.

I myself have written applications that used Access databases but were not written in Access themselves but merely connected to access via ODBC. Later when I've upgraded the systems All I needed to do was port the data and use a different ODBC driver. program unchanged and database upsized.

Autonumber is good enough for small to moderate DB's but in my work we're frequently merging databases together from various sources and an auto number between two seperate db's is not always guarenteed to provide uniqueness.. obviously we use multiple field keys but recently we've started using GUIDS as keys in our databases.. obviously at the expense of size on each record and ultimately the entire database.

all of this is of no use to the original poster though.. just to say that asking what database is best is like asking someone what colour is better Red or Blue
it depends on what it's needed for.

But yeah, I agree with most if not all of the points above.

[Gus]

Some good points in this thread.

Aye. Many thanks to all those who have contributed, given me a few options to think about that were previously unknwon to me. Ta!

[Dreadful Scathe]

Actualy, to date, I've only come across Access based systems in Blue Chip orgbnaisations, e.g. RSA, HSBC, Credit Suisse etc. There is an increasing requirement for local solutions to deal with data to specific requirements and allow manipulation and reporting accordingly.

Ah, ok. As my experience is mainly Oracle there is little chance of anyone I visit using Access. For local solutions I've used Oracle Portal/html db , Oracle Forms , Oracle Apps, Oracle Discoverer - entirely depending on the data manipulation needed. No company, that i know of, sticks entirely to one technology - so where they might have Business Objects as a BI tool instead of Discoverer and Siebel instead of Oracle Apps, Ive not come across Access for any live systems in any decent sized company - clearly there are some, and if it works, then fine

AND don't even get me started about any Business who has been stupid enough to outsource their IT department. Anyone know any business that doesn't now regret that decision?

Indeed I am currently waiting to get a small java app installed - it will take another week (been waiting one already) as the company that handles the server for this project cant do anything until a previous bit of work is signed off and a new costing agreed. The app should take about 30 mins to install and test

[El Salsero Gringo]

Ah, ok.

Indeed

Oi, you lot, stop agreeing. I've not finished my popcorn yet. And Under Par hasn't got back from the ice-cream counter.

[Lou]

the end users dont know what they want, the IT people have their preferences, biases and particular skill sets

.... and that's why analysts were invented.

There is an increasing requirement for local solutions to deal with data to specific requirements and allow manipulation and reporting accordingly.

*shiver*

Do you have any idea how tricky that makes my work? I've seen so many tactical solutions deployed locally without a thought for any strategic or corporate viewpoints. Try building an integrated Business Intelligence solution where you have to troll amongst various departments to get the data you need - from Martin's accounting Access database (which he's loathe to share as knowledge means power), to Les's sales spreadsheet (updated whenever he gets the time), for instance. Give me a company with proper IT strategy anyday.

- so where they might have Business Objects as a BI tool instead of Discoverer .....

Bless! How are the SW folks?

[Gus]

Do you have any idea how tricky that makes my work? I've seen so many tactical solutions deployed locally without a thought for any strategic or corporate viewpoints. Try building an integrated Business Intelligence solution where you have to troll amongst various departments to get the data you need - from Martin's accounting Access database (which he's loathe to share as knowledge means power), to Les's sales spreadsheet (updated whenever he gets the time), for instance. Give me a company with proper IT strategy anyday.

I AM a BA Its totaly possible to have 'local' solutions supporting small SBUs that still comply to an overarching IT strategy. Looking from the Business point of view, whats more important, the Analysts having to work a bit harder or the Business to actualy be able to move quickly enough to meet the market demands and so still stay in business? In too many businesses, especially the big banks, its the tail wagging the dog where IT actualy restricts how the Business operates instead of facilitating it. IT technology may be SOTA but its development methodologies still remain in the Dark Ages (dons flameproof armour and waits )

[El Salsero Gringo]

Try building an integrated Business Intelligence solution where you have to troll amongst various departments to get the data you need - from Martin's accounting Access database (which he's loathe to share as knowledge means power), to Les's sales spreadsheet (updated whenever he gets the time), for instance. Give me a company with proper IT strategy anyday.

Gus raises a good point, Lou, and I'm curious to see your answer: would that business have been better off if Martin had been denied the opportunity to crate his Access database, and Les's sales spreadsheet had never happened? In an *ideal* world, of course, each of them could have phoned IT and had a systems analyst create them a perfect solution based on company IT policy, within a day, and provide constant support to extend the functionality of the solution on an as-needed basis. But given that's as unlikely as flying to the moon, should the go-ahead sales staff be prevented from looking at their own data by stroppy, slow, self-important jobsworth IT bods and their "corporate IT strategies"?

[bigdjiver]

... AND don't even get me started about any Business who has been stupid enough to outsource their IT department. Anyone know any business that doesn't now regret that decision?

I believe Ceroc does not own its own membership system, which is Access based, and does not regret it at the moment.

[bigdjiver]

... only 80% ? Try 90%+ The reasons are a bit of both - the end users dont know what they want, the IT people have their preferences, biases and particular skill sets

...but the main reason that many companies, councils and the like have failed expectations is one you missed - the people with the money buy anything that impressive sales people will sell them - only later do the users and IT people have to make the best of it

Another problem is that often the business changes faster than the I.T. can keep up.

The I.T, department is not immune to fashion and salesmen either. I got sacked by one place after I embarrassed my bosses by replacing the total functionality of a very expensive Mini-computer (we are 70's here) with a single sheet of A4 paper.

[Lou]

should the go-ahead sales staff be prevented from looking at their own data by stroppy, slow, self-important jobsworth IT bods and their "corporate IT strategies"?

I'm grumpy, ES, as I've come down with a lurgy.

I'm just looking at it from my purely selfish point of view, of course. When someone in HQ wants a corporate wide reporting solution, it's far easier for me to do if the IT dept have a corporate data model, a consistant approach to systems, processes, documentation, etc. and when there aren't a load of locally based independent, undocumented, unstandardised solutions dotted about.