Small Business Application development

[Clive Long]

OK, scenario is this. I'm starting to work with SME (smaller businesses) in making their processes more efficient. I've being trailing a new consultancy model with a test client. However one issue I've very quickly hit is the lack of integrated systems (or money to invest in them) means that data capture / manipulation / reporting will have to be done at a PC application level. My initial challenge was to capture incoming orders for standard products, pass them onto production for build, then confirm despatch, invoice etc and capture the information to use for analysis, CRM etc.

<< snip >>

All help gratefully received.

My take on this.

• Buy don't build.
• think business, think process, think robustness, support and availability. Don't think technology (yet)
• SMEs are interested in their business and are looking for technology that makes things simpler and quicker. They are not looking to build IT departments
• Maybe an ASP model will be cheaper in terms of TCO

You are looking for an end-to-end solution and in my limited experience you'll be spending a month trying to sort out the real business needs, sketching out the high level processes and which groups need to be involved and selecting a package that might touch those areas ...

Some technology suggestions.

• Small-scale CRM: SalesLogix is robust and scalable but "stops early" in the cycle.
• Open architecture databases with simple reporting tools
• Easy configurable screens and easy modifable work-flow
• Get customer to accept it is quick and cheaper to adapt to the limitations of the software rather than bend the software to every nice-to-have
• Avoid mods as far as possible - maintenance overhead
• Don't replicate what is already in place - try and integrate with it - e.g. MS-Exchange
• Work out where your "definitive" data sources are e.g. customer records and see if you can plunder those
• Avoid using spreadsheets to patch up gaps in the process
• 80/20. Don't try and boil the ocean. and other management platitudes
• Think about implementation and cut-over
• whatever you choose, you are building tomorrow's legacy problem
• Loads more I can't think of

[philsmove]

My take on this.

[*] whatever you choose, you are building tomorrow's legacy problem
[/LIST]

WE choose MS Access many years ago, on a stand alone PC with Windows 95

It’s now on MS small business server with 10 workstations and we are still very happy with it

It still won’t make tea, but apart from that, it does every thing I want from it, which is quite a lot

Minor changes and simple queries, I do my self, but out source the complicated ones

It does not however have to integrate with any other programs

[bigdjiver]

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

Access has a wide-open door to open software. Access can use the data on a large number of leading databases via ODBC drivers. The user does not see where the data is stored, it is just "The database". MySQL is a leading open source database. I checked on usenet and there were 28,000 hits on "ODBC driver mysql". Restriciting the search to Access groups returned 600. This sort of free almost instant support is hugely important to developers involved in new products.

I quote the top answer, the thread was "Access MySql ODBC strategy "

... Just have the user install the MySQL ODBC driver, and the
Access app will work with no further setup...

Many developers design and prototype with Access even though the eventual solution will be on a huge system, or on another platform. Many of the larger systems have their own tools, many of which cost more and do less.

[bigdjiver]

... 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

It is mostly due to business being difficult. Most managers do not know how their business is actually run, and like the rest of us, do not know how they will react to particlular situations. Computer systems need hard rules. Most human endeavours rely on bending with the wind. The other problem developers face, and suffer from, is EGO. People are unwilling to admit that they "don't know" or have got something wrong.Quite often the reason that the computer solution will not work right is because the business is not working right. Often that is the motivation for computerising in the first place. "Its not working, lets computerise it."

[tsh]

OK, so for a slightly different question, If I wanted something very simple (the sort of thing some people would chose to do in Excel say) and I don't want to use Access, what is the simplest application to use? (for a stand-alone WinXP, or web server based - either would be suitable)

Sean

[El Salsero Gringo]

Access has a wide-open door to open software. Access can use the data on a large number of leading databases via ODBC drivers.

I know that's true - I've used it in exactly that way. I was just pointing out that you don't have to be "into" open source to use an open source solution like Apache/php/mySQL any more than you have to be "into closed source" to use something like Access.

[Dreadful Scathe]

OK, so for a slightly different question, If I wanted something very simple (the sort of thing some people would chose to do in Excel say) and I don't want to use Access, what is the simplest application to use? (for a stand-alone WinXP, or web server based - either would be suitable)

Sean

xampp

[bigdjiver]

... If I wanted something very simple (the sort of thing some people would chose to do in Excel say) ...

Please expand on "something very simple". Like Clive Long sais you should not build something yourself if it is already out there at a reasonable price. There is freeware and shareware to do thousands of different applications. What you want has probably been invented already.

"What some people would do in Excel" is very little help. Because of its power, and people already have it, and have some skill in it, it is used in all sorts of applications where something else would be more suitable.

[El Salsero Gringo]

Because of its power, and people already have it, and have some skill in it

Aren't those three criteria which go a long way towards deciding what's "suitable"? Particularly the last one!

[ducasi]

OK, so for a slightly different question, If I wanted something very simple (the sort of thing some people would chose to do in Excel say) and I don't want to use Access, what is the simplest application to use? (for a stand-alone WinXP, or web server based - either would be suitable)

Sean

Um... So what's wrong with Excel then?

[bigdjiver]

Aren't those three criteria which go a long way towards deciding what's "suitable"? Particularly the last one!

That was one of the reasons why Access gets chosen so often. Unfortunately it is all too easy to plough on with what you started with long after it has become nonsensical. If you can start out right you have a better chance of ending up right.

One of my landmark quotes:

"There is no alternative but to start again, smarting but smarter, and build a redesigned version in which these problems are solved.... Hence, plan to throw one away; you will, anyhow" (Brooks)

http://www.robelle.com/library/smugbook/manmonth.html

[Clive Long]

Um... So what's wrong with Excel then?

I love Excel. Almost any data tracking I do in an Excel spreadsheet. I use only the simplest functions and filters - maybe I use 5% of its capabilities.

So what's the problem?

Most spreadsheets I have come across are very "fragile". The more functionality you pile on them, the more indirect references you use, the more data you absorb from outside, one day it will develop a rash of #REF! and #DATA! errors that will take hours to fix. It's the very power and ease to develop something quite sophisticated that is setting the trap.

Also, I have seen it used as an "integration" tool to pull together data from different sources. The cutting and pasting you wouldn't believe. Also, I haven't yet seen a multi-user spreadsheet - pretty disastrous when you are using it to integrate data and process from your "point" solutions. Cries of "haven't you finished with the booking spreadsheet?" across the office "I need to raise the invoices and enter payments". Maybe an MS-Excel front-end to a multi-user MS-Access or mySQL database would work. I'm way out of my knowledge on the technology.

I'm not saying it is impossible to write a robust, scalable, extensible application on an Excel base - it's just too easy to develop something you really depend on that will let you down. That's not a criticism or failure of Excel, it's just you have used it inappropriately.

Clive

[tsh]

Um... So what's wrong with Excel then?

I don't have a license (hence access is also unavailable). I'd only chose to use excel for numeric stuff though, because that's how I usually use it!

I know I can do what I want in access, more easily than I can find something free that does what I need - but I might as well start with a tool that is designed for the job if it means I have to learn something new!

Sean

[frodo]

I love Excel. Almost any data tracking I do in an Excel spreadsheet. I use only the simplest functions and filters - maybe I use 5% of its capabilities.
....

Lots of good stuff there. On Excel itself though I've only recently dabbled slightly in the latest version of Excel and just couldn't believe how limited it is for being a modern application.


Open two separate Excel files with the same name in different folders which you want to compare - nope.

File name containing square brackets - nope.

Simple lookup on separate worksheet - nope.

Conditional formatting - more than 3 bands - nope.

Powerful machine - want more than 65536 rows - nope.


The last one seems quite a hard limit to doing significant solutions in Excel, however well it is done.

***

For really simple situations such as primarily read only / single user applications an SQLite3 back end which just reads and writes to a single portable file like Access seems to work pretty well, and it doesn't have the file 1Gb/2Gb/4Gb size limitations of the free Microsoft solutions.

For systems that fit within the file size limits the free editions of Microsoft SQL Server 2005 and associated tools do look quite attractive in comparison to Access.

[bigdjiver]

Lots of good stuff there. On Excel itself though I've only recently dabbled slightly in the latest version of Excel and just couldn't believe how limited it is for being a modern application.


Open two separate Excel files with the same name in different folders which you want to compare - nope.

Don't you think having files with the same name that might be different is possibly bad practise?

File name containing square brackets - nope.

is that a big deal?

Simple lookup on separate worksheet - nope.

yup

Conditional formatting - more than 3 bands - nope.

You can set the formats of cells, and loads more besides, using a macro.

Powerful machine - want more than 65536 rows - nope.

I am running 2007 Beta (free until Feb) 1,048,576 rows by 16,384 columns

The last one seems quite a hard limit to doing significant solutions in Excel, however well it is done.

Do you have an example where you need to use more than 60,000 rows?

[frodo]

Don't you think having files with the same name that might be different is possibly bad practise?

No - this isn't production, I'm working with different versions at the same time - arbitrary renames for silly Excel limitations mess up my source control.

is that a big deal?

Not really, but again another thing to rename and change my naming schema because of yet another silly limitation.

yup

I probably termed it wrong - this might be termed a constraint rather than a lookup. OpenOffice doesn't have a problem with it.

You can set the formats of cells, and loads more besides, using a macro.

Once you start using macros, you don't have limitations, but you have to learn it, there are security implications, especially when distributing the spreadsheet, and it is just so completely unnecessary

I am running 2007 Beta (free until Feb) 1,048,576 rows by 16,384 columns

I'm aware of it, but haven't run it - I would have hoped they'd upped it a bit more - given it will probably be 15-20 years before they up it again - it also removes the conditional formatting limitation.

It won't be in widespread deployment for a while though - and it hasn't got the benefit of the doubt any more.

Do you have an example where you need to use more than 60,000 rows?

Exporting database tables / working with database table contents, logfiles, performance records.

[bigdjiver]

...Exporting database tables / working with database table contents, logfiles, performance records.

My instinct is that 60,000 records points towards a database solution, and I would tend to either work in the database environment, or select the records I wanted to work on in Excel so that I did not have to deal with such a large number of rows. I find Microsoft query an amazing tool. Perhaps I have just been lucky with the apps I have had to deal with.

[Magic Hans]

For what it's worth:

Access: Perfect (or at least very good) for proto-typing and piloting.

Not wonderfully stable [be prepared to lose a days work, unless migrating the back end to a decent DB engine eg MySQL or SQL Server].

Plan in an eventual migration to a more stable and supported application and back end.

As previously mentioned, any database with more than a few tables needs to be well designed.

Start of small: First step is the most difficult. Many small applications seem to fail at this point. Users want the 100% solution which is never possible [mostly due to evolving requirements]. Scope creep is probably the greatest project slayer.

Hopefully, by the end of the pilot, everyone will be far more aware of their requirements and be far better placed to make an effective decision on a solution - possible bought and configured, possibly built to spec.

Case Study:

I built an ordering/invoicing system on Access for a kitchen factory here in the East Mids. 5-7 man company, with husband also in factory and wife in the office. Neither were particularly computer literate. Being ambitious, he wanted automatic stock control included, and potentially a link into a website for online orders and quotes. I was continually managing his expectation downwards, and the little app was implemented and used successfully (training included). It became quite an integral part of the operation, and I was unwilling to give the support that it required.

Expansion happened, and the application was effectively outgrown.

They now have some major sheet cutting machinery. a small (5 PC or so) network, integrated ordering/stock control/customer management software, and an IT company to support it.

Job done [as far as I can see].

[DavidY]

Open two separate Excel files with the same name in different folders which you want to compare - nope.

Interesting that 10 years ago I was using Excel 4.0a which would compare spreadsheets (albeit with different names) and show you all the differences. For some reason they deleted this feature in later versions.

The Sort function in Excel 4 was better too.

[Gus]

Case Study:

I built an ordering/invoicing system on Access for a kitchen factory here in the East Mids. 5-7 man company, with husband also in factory and wife in the office. Neither were particularly computer literate. Being ambitious, he wanted automatic stock control included, and potentially a link into a website for online orders and quotes. I was continually managing his expectation downwards, and the little app was implemented and used successfully (training included). It became quite an integral part of the operation, and I was unwilling to give the support that it required.

EXACTLY the scale of development I thinking about Will have to catch up for a PM or two if thats not too much hassle?