Small Business Application development

[Gus]

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.

No surprise that all attempts to get Excel to capture, sort and report have failed :(. It occurs to me that Access (from my basic knowledge) has the ability to build input forms, create relational databases and generate the necessary reports to maintain the CRM and accounting systems. HOWEVER, I'm not sure that my VBAsic skills (which are nil) are up to the task. Open question;

a) Is MS Access the best tool for this job
b) Can a duffer like me train himself up in say a month or so to be able to build reliable small scale applications?

All help gratefully received.

[El Salsero Gringo]

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.

No surprise that all attempts to get Excel to capture, sort and report have failed :(. It occurs to me that Access (from my basic knowledge) has the ability to build input forms, create relational databases and generate the necessary reports to maintain the CRM and accounting systems. HOWEVER, I'm not sure that my VBAsic skills (which are nil) are up to the task. Open question;

a) Is MS Access the best tool for this job
b) Can a duffer like me train himself up in say a month or so to be able to build reliable small scale applications?

All help gratefully received.You might also consider an open-source solution like MySQL with middleware such as PHP, and the Apache web-server. (There may be other open-source packages you need to achieve part of your goals however). Then you have a platform-independent, and completely free solution.

It doesn't (to me) sound like a month is sufficient time from scratch to learn any of those tools (Access or something open-source) and put together something you could put in front of a client.

[Beowulf]

Access is ok for small to moderate applications. However it has it's limitations.

It's not a true multi user system so if you need more than one person accessing the database at the same time you can get problems. Our office had an Access database connected to a Delphi Application that was used on over 80 PC's.. and although it seemed to work most of the time on particularly busy periods we would get data corruption and missing records.

If it's to be a small application with a few tables with one user running it at a time then yes I suppose it's fit for purpose if you have nothing else.

As for learning it.. it's not difficult. You done much programming before? Languages are irrelevant as it's just syntax in the end. a month or so to learn it is probably ample I would say. Lots of good books and websites out there if you need them and the help system is usually sufficient.

[Dreadful Scathe]

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.

No surprise that all attempts to get Excel to capture, sort and report have failed :(. It occurs to me that Access (from my basic knowledge) has the ability to build input forms, create relational databases and generate the necessary reports to maintain the CRM and accounting systems. HOWEVER, I'm not sure that my VBAsic skills (which are nil) are up to the task. Open question;

a) Is MS Access the best tool for this job
b) Can a duffer like me train himself up in say a month or so to be able to build reliable small scale applications?

All help gratefully received.

First off, ignore Beowulf - as a geek its "easy" to him but languages are not just about the syntax, theres the whole concept of why,what,where first. So forget doing something yourself inside a month, what you can do is plan and take it from there with a good concept of what you need and how you are going to get it.

You want to provide data entry from a PC. Your easiest option is a web site, even if the whole application is internal. Why? Because you only have the server to worry about, not individual PCs with their own software - they already have a browser.
So you need a backend server that provides screens for data entry, interacts with a database, produces reports etc..
helpful if its free too.


have a look at these two CRM options they come with most of what you need and they are all open source

http://www.siteground.com/sugarcrm_vtiger.htm

get a geek in to do the installs if there is no one in-house, establish some sort of backup/recovery plan and bobs your uncle.

another list of free stuff is here

http://www.occhosting.com/easyapp.html

[WittyBird]

Access can do the job. Whether someone can bring themselves up to speed on it is a different matter and probably comes down to the individual. Access is a curious beast - I can't get on with it, but I have seen some very complex applications built with it.

[Beowulf]

First off, ignore Beowulf - as a geek its "easy" to him

:confused: sorry.. I misunderstood I thought he said a month or so to learn access. Not learn access AND develop the app.

programming's not rocket science and perhaps I've got the wrong end of the stick from the original posters post but if all he needs is data input via form to a database and some reports out with minimal processing in the middle then the actual programming is minimal as most can be done in Access by clicking and dragging form components around.

if it's multi user , multi site then yes I agree a LAMP system (linux, Apache, MySql , PHP) set up would be best. however if it's single user, single site then I would say simplest solution is the easiest. PHP/MySQL is great but again.. there's more stuff to learn. and with server side apps you've loads of security concerns to address.

Programming is not always "easy" but I did think I made some valid comments :confused:

in a nutshell what I was saying was
a) Is MS Access the best tool for this job
YES and NO.. if it's all you've got it'll do

b) Can a duffer like me train himself up in say a month or so to be able to build reliable small scale applications?
Depends on complexity and how small scale they are.. but looking at your requirements I would say yes.


Access can do the job. Whether someone can bring themselves up to speed on it is a different matter and probably comes down to the individual. Access is a curious beast - I can't get on with it, but I have seen some very complex applications built with it.

:yeah:

[Dreadful Scathe]

Programming is not always "easy" but I did think I made some valid comments :confused:


Of course you did, I think I forgot a smiley :) Just thoguth you may panic Gus when he looks at the documentation for access for the first time :)

[straycat]

To throw another one into the mix, and this all depends on the scale and sophistication of application, there's always Filemaker (http://www.filemaker.com).
It's reckoned to be much easier to get started with than Access, and it's very simple to create basic databases applications and so on, generate input forms, summaries, reports etc.

Where it falls down (well - used to fall down - I'm a couple of versions out of date) is when you want to get more sophisticated - you occasionally have to move heaven and earth to accomplish something that an experience Access developer could have done in half an hour. Back when I was self-employed I used it to build myself a comprehensive quoting/invoicing system, and that took very little time to do. Well worth a looksee.

[Gus]

.... but if all he needs is data input via form to a database and some reports out with minimal processing in the middle then the actual programming is minimal as most can be done in Access by clicking and dragging form components around.Thats where I was coming from. I just need to hand of a few related databases to hold the product and price information then pull this together in different formats to produce reports such as 'lapsed' customers, outstanding orders, stock out issues, basic profitability etc. This is only a 'tactical' solution, probably not long term.

Delivery time is crucial. To specify, build and implement I would have about a 3 to 4 week window in some cases. To give you an idea, my current client is only a £100,000 t/o start-up business with only 2 office staff but growing very rapdidly.

[El Salsero Gringo]

This is only a 'tactical' solution, probably not long term.They tend to be the ones that are still in place after five years....

If you can use a technology that does scale bigger - even if you don't think you'll need it for "ages" - then that's a bonus.

[Lou]

to produce reports such as 'lapsed' customers, outstanding orders, stock out issues, basic profitability etc....

Sounds like you need a couple of people to build you a nice reporting solution - maybe delivered through some sort of portal....

Hmmm... I wonder where you might find a couple of consultants who specialise in that sort of thing.... :whistle:

[Gus]

Sounds like you need a couple of people to build you a nice reporting solution - maybe delivered through some sort of portal....

Hmmm... I wonder where you might find a couple of consultants who specialise in that sort of thing.... :whistle:Hey, I can't afford my own rates at the moment, never mind that of the Welsh IT Mafia :na:

[DavidY]

Microsoft seem to be plugging the various versions of SQL Server as a database backend for multi-user/ network environments where Access can struggle. The SQL Server Express edition is free and I believe you could use Access as a front end. However I imagine it's pretty complex - it would be a lot more than a month to do this unless you really knew what you're doing..

Edit because I saw this...

If you can use a technology that does scale bigger - even if you don't think you'll need it for "ages" - then that's a bonus... an advantage of SQL Server Express over Access is that it's easy to scale up to a more powerful version of SQL Server later if you need to.

[WittyBird]

Hmmm... I wonder where you might find a couple of consultants who specialise in that sort of thing.... :whistle:


Then obviously someone is needed to provide all the hardware and software :whistle:

Don't know where you'd find them tho :D

[Lou]

Hey, I can't afford my own rates at the moment, never mind that of the Welsh IT Mafia :na:

Not just Welsh - I'd recommend Smurf-boy too, you know... :respect:

How did I just know you couldn't afford me.... :na:

[Dreadful Scathe]

How did I just know you couldn't afford me.... :na:

What? a black wedding dress with a purple choker and a packet of chocolate biscuits and you're anybodys :)

[Beowulf]

Hmmm... I wonder where you might find a couple of consultants who specialise in that sort of thing.... :whistle:


Then obviously someone is needed to provide all the hardware and software :whistle:

Don't know where you'd find them tho :D

*cough* nope no idea. :whistle:

[bigdjiver]

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.I am writing for any non-geeks who may be interested.
Q1. How many of your potential clients run Microsoft Windows, and how many run other operating systems?

Q2. How many of them have experience in Microsoft Office products such as Word and Excel?


No surprise that all attempts to get Excel to capture, sort and report have failed :(.:confused: No surprise at all, if you did not know where to look for help.
If you have Outlook Express on a PC look under Tools and you will find Newsgroups. These are free and open groups. If you search on Excel you will find a list of Excel groups. If you subscribe (free, just click) to some of these you will find questions being asked and problems being solved, usually by Microsoft MVP's (Most Valued Professionals):respect:
http://www.mvps.org/links.html#Excel

These newsgroups are archived and searchable.:clap:
http://groups.google.co.uk/advanced_search?hl=en
(there is no smiley adequate for over two decades of expertise at your fingertips)


It occurs to me that Access (from my basic knowledge) has the ability to build input forms, create relational databases and generate the necessary reports to maintain the CRM and accounting systems. You are looking at problems that are best solved by a database.


HOWEVER, I'm not sure that my VBAsic skills (which are nil) are up to the task. I am sure that they are not, but you may not need VBA. (Visual Basic for Applications, runs behind Excel and Access. Have you ever needed it in Excel?)


Open question;

a) Is MS Access the best tool for this job
b) Can a duffer like me train himself up in say a month or so to be able to build reliable small scale applications?

a) Yes, unless you and your clients are into open source.
b) :rofl: :tears: :eek:

But why build what has probably already been built?

Access comes with templates and sample applications. The MVP sites are full of others. You and your clients should start simple, and one of these sample apps will probably be a good place to start.

When you are ready and when you need to, you can move to a multi-user database such as SQL Server. There are some massive organisations that run this. Many of these use Access as a development tool or as a front end. Access is a superb development tool, no matter which database solution you finish up with. Input forms can be generated by "Wizards" in minutes. The query builder is superb, also with wizards. The facilities for import and export of data are also ... (I am beginning to sound like a fan, aren't I?)

[Dreadful Scathe]

I am writing for any non-geeks who may be interested.
Q1. How many of your potential clients run Microsoft Windows, and how many run other operating systems?

Q2. How many of them have experience in Microsoft Office products such as Word and Excel?


Im not sure what your point is here? I always recommend a data entry front end should be a web browser with a user interface that takes no more than 5 minutes to learn. :)



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

I think Access is too limited for future expansion, I would not have that as first choice - only as an option if they already have it and have the time to develop what they need in it. I suggested the open source alternatives above because they came with all server software and the application itself.

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.

[Lou]

What? a black wedding dress with a purple choker and a packet of chocolate biscuits and you're anybodys :)
Actually, I'd just settle for the choccy biscuits. But don't tell anyone, as they'll keep haggling me down.... :eek:

[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!

SeanSean, 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:rolleyes:

[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:confused:

[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:rolleyes:

:rofl: 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?:mad:

[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! :cheers:

[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?:mad:

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.
IndeedOi, 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. :whistle:


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? :mad: I've seen so many tactical solutions deployed locally without a thought for any strategic or corporate viewpoints. :rolleyes: 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! :D How are the SW folks?

[Gus]

Do you have any idea how tricky that makes my work? :mad: I've seen so many tactical solutions deployed locally without a thought for any strategic or corporate viewpoints. :rolleyes: 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:cool: 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:rolleyes: )

[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?:mad:I believe Ceroc does not own its own membership system, which is Access based, and does not regret it at the moment.

[bigdjiver]

...:rofl: 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. :na:

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

[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:rolleyes: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 itAren't those three criteria which go a long way towards deciding what's "suitable"? Particularly the last one! :wink:

[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! :wink::yeah: 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.


yupI 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 columnsI'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.:confused:

The Sort function in Excel 4 was better too.:rolleyes:

[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?