Real World Access (15)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Arvin Meyer's houses

Construction Database: Database creates, tracks, and awards bids for production homebuilding. It then tracks vendors and costs, loads the data into individual lots (at about 700 homes per year) and issues purchase orders, automatically emailing them to the vendors. Purchase orders are loaded into the individual house and into the accounting system and tracked until final payment is made. An audit trail and a history are kept of all price changes, and a comparison between costs of various models can be implemented. A backlog of homes to build is kept so that projections can be made of 6 to 9 months of activity.

On the sales side, the app creates and tracks contracts from initial buyer interest through closing. Housing prices are determinined by cost projections. Individual contracts are analyzed and accepted. Option costs and prices are tracked and options sold. On the planning side the housing starts are monitored from initial permiting through closing. Warranty service is tracked and quality control reports issued.

Tags: microsoft+access

Powered by Qumana

Real World Access (14)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Steve Schapel's auctions

Dunbar Sloane Ltd is New Zealand's leading auctioneer of art, antiques, artefacts, toys & dolls, wine and colonial collectables.  I have developed an Access application, right at the hub of their operations, to manage their auction sales.  They typically conduct 2 or 3 major sales per week, sometimes involving thousands of "lots".

Prior to the sale, the application is used to prepare goods descriptions, which are output to printed and online catalogues, and printed to the “running sheets” that are used by the auctioneers.  During the auction, the database caters for live recording of sales results, and processing of payments received and goods collection, including the printing of invoices for buyers.  After the sale, the application is used to produce statements for vendors, and financial summary reporting for accounting purposes.

Tags: Microsoft+Access, Micro-ISV

Powered by Qumana

Real World Access (13)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Pat Hartman's hair care

Clairol, manufacturers of hair care products, converted their existing applications to SAP but SAP didn't provide all the functionality they needed so I developed an Access application that worked with SAP to produce workorders for the shop.  The application managed tables that stored the formulation instructions for each product.  SAP held the Bill of Materials and calculated demand.  A nightly batch job imported the SAP production orders and my application used them to calculate the quantities of each material required to produce the requested batch size and printed the shop floor paper at the appropriate work stations before start of shift each day.  The application was two Access databases, one for user interaction and the second to do the nightly processing and they both linked to an Oracle back end.

Tags: Microsoft+Access

Powered by Qumana

Real World Access (12)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Ken Snell's retailers

Point-Of-Sale Database for retail stores. Handles parts inventory (including parts that have unique serial numbers associated with each item, e.g., electronic equipment, cameras), customer orders, work orders, quotes for business opportunities, purchase orders, automatic backordering and followup invoicing for backordered parts, accounts receivable (customer payments, customer statements, customer credits), reporting of sales tax amounts, profit margin reports for individual parts and for product groups, inventory valuations, "customer memberships" (e.g., "Preferred Customer Club"), archive copies of all orders and quotes, accounting reports (sales tax collections, invoice register, "end-of-day" cash flow and income/sales), inventory count sheets (to do physical inventory of some / all parts), and gift cards/certificates. Used in multiuser situations via split Frontend/Backend on local network. Current customers range from photography services ($50 - $100K per year in sales), to wholesale/retail distributor of hydraulic parts ($4M per year in sales).

(Someday, soon I hope, it will also do accounts payable. It's possible to do accounts payable with the current database, but it requires one to use accounts receivable in "reverse", which isn't too intuitive for many users.)

The Database also serves as an employee time clock, and can produce various time clock reports to show hours worked by employee, by job, by task, etc.

Tags: Microsoft+Access, Micro-ISV

Powered by Qumana

Real World Access (11)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Alex Dybenko's ships

There is a biggest shipping company in a world, Maersk Sealand, which once asked me to build a small Access application for one department.  People liked it, and it started to grow and grow, once tables were moved to SQL server.  And now this is one of the key applications in the company, people use it to deploy all their container fleet all over the world, including schedules, contracts, etc.  It has several frontends, and other systems get data from it.  They call it Vodka - Vessel Operation and Deployment Key Application.  Their system department is dreaming to get rid of it, rewriting on something "cooler" then Access, but Access still works fine...

Tags: Microsoft+Access

Powered by Qumana

Real World Access (10)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Tom Wickerath's aircraft

In development right now, a database for the AOG (Airplane on the Ground) tool room organization of The Boeing Company.  It will be used to help pack kits of tools for transport anywhere in the world that they are needed for working on an airplane that is grounded. This will pretty much be a complete re-write of an "Access spreadsheet" that was giving the customer lots of pain.

Tags: Microsoft+Access

Powered by Qumana

Real World Access (9)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Duane Hookom's nutritional products

Our factory makes products like infant formula and other nutritional products. It is very important to track which ingredients are used in which products. Each "recipe" is stored in its own Excel file and these are "controlled" documents. Our quality staff had been keeping a huge spreadsheet listing ingredients across the top and products along the left side. Picture "X"s in a grid. Searching and maintaining the cross-reference was a huge but necessary task.

I created a fairly simple Access application that pulled a list of Excel files from a folder on our server. Users would click a button and code would open each Excel file individually and find the top of the list of ingredients. The ingredients and products were read and stored in a normalized table linked from SQL Server.

Finding out which ingredients are in which products and vice-versa are now a snap. I actually have reports/queries in Access and on our intranet. Users are super happy with a minimal amount of work. I would like to replace all the Excel files but they have lots of other functionality and are "controlled". I put in about 15% of the total effort and got about 85% return in less maintenance and greater accuracy with the new system. Some times you have a draw a line when measuring return on investment and other priorities.

Tags: Microsoft+Access

Real World Access (8)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Karl Donaubauer's sleep research

I've created several Access applicationss for the sleep research team at the department of neurology of the University of Vienna.

They use a device called Actigraph (you wear it like a wrist watch) to collect data about the movements of patients with sleep disorders. I import the data of the actigraphs (many thousand values after several weeks), compute a bunch of evaluations, compare this objective data with subjective notes of the patients about their sleep, and data from other sources, to create movement charts etc.

The end result for the single patient is his personal sleep diary. The scientists use the apps to evalute the impact of medication, of lunar phases and many other influences. The apps are also used for comparative sleep studies all over Europe.

Tags: Microsoft+Access

Powered by Qumana

Real World Access (7)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Jeff Conrad's restaurant network

I created a small Access application for each of our remote restaurant locations to enter their daily numbers. Each evening the restaurants had to fax their daily numbers to the main office where someone would spend a few hours each morning transferring this information to a spreadsheet. If any stores forgot to fax their numbers, we would have to call them as a reminder. Once all the information was complete, this person then had to print out a copy for all the big wigs in the office to see. Another copy was faxed to each Regional Manager.

We already had hardware available so I set up a VPN and placed a BE Access file on that server. I remotely placed a FE file on each restaurant PC and linked them to the VPN BE. I even remotely placed a FE copy on each of the Regional Managers’ home PCs. In the office I placed a FE copy on each of the big wigs' PCs. With this setup (no additional costs here) all of the stores could enter their daily numbers into the Access database. All of the Regional Managers and the big wigs could pull up a report on their own PCs now to see the numbers. I created several other types of comparison reports as well.

We saved probably 20 hours per week for that one person who used to manually compile this information as well as all the long distance fax charges. We only have two stores now, but I still use it and have 5+ years of data for our current locations.

What really surprises me is that I had only been using Access for a very brief time before setting this up.

Tags: Microsoft+Access

Powered by Qumana

Real World Access (6)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Tony Toews's welding shop

Hows about my big kahuna, the ERP app for welding shops with several hundred welders?   Handles drawing status, material receving, inventory scheduling, fabrication, very complex quality control (15 different types of tests), shipping and invoicing. 

All for refineries, oil sands processing plants, power plants, etc, where the wrong piece of steel can cost someone a billion dollar shutdown.

Twenty five users in Alberta and Ontario.  One hundred sixty tables, 450 forms, 350 reports.

Tags: Microsoft+Access, database+development

Real World Access (5)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Joan Wild's tree breeding

It started as a database to track the breeding activities (tree breeding, that is) of a tree improvement cooperative.  Selection of parent trees, collection / extraction / storage / testing of pollen, breeding plans/activities, collection / extraction / testing / storage of seed.

It then grew to include all the testing of this material.  Growing, planting of the tests, and then the capture of measurements in the field.  This latter part was done via handheld units, with Excel as the intermediary.

Tags: Microsoft+Access

Real World Access (4)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Peter Doering's shoes

I wrote a sales force automation tool for the apparel/footwear industry, i.e. with style/color/size handling. Currently it is running in about 30 countries and 5 languages on all continents.

It's used by sales reps for offline recording of sales orders. Later on they sync with a console application that also handles the communication with the main ERP systems (Navision / SAP).

The client and console applications are Access based (A02/A03), while backend databases are either Access (JET) or SQL Server 2000.

For most customers it's the only tool they use for order capturing, so you can call it an Access based key application.

Tags: Microsoft+Access

Powered by Qumana

Real World Access (3)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Allen Browne's roofing manufacturing

Currently working on a good sized project to automate a factory that produces metal trusses, purlins, studs, etc. for roofing. They want to key the client's order into the Access database, have it send the correct information to the programmable logic controller for the correct machine to manufacture each part, and have the part rolled, cut, punched, notched, and printed ready for the client to pick up, without operators setting up the machines.

The interface involves:

• A main form to enter the client order;

• A subform for the parts in the order;

• A related subform for the holes to be punched in the selected part.

• A 3rd subform that gives a quasi-graphical view of the part, allowing the user to verify the absolute and relative distances are right before they click a button to set it to the machine.

Tags: Microsoft+Access, Micro-ISV

Powered by Qumana

Real World Access (2)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Albert Kallal's fishing lodge system

During the off season a well established holding company that owns many restaurants and all kind of properties also owns a successfull fishing lodge. The office has a 1-800 number, and also several staff at this "main" office take reservations during the off season (winter). The system is thus multi-user, and several users in the office all use the system at the same time to book, invoice and follow up customer's requests for bookings and information. MS-access allows this system to be multi-user and functions without having a specialized database server.

When summer comes along, one of the staff members takes the data file with them and moves to the lodge for the summer (lucky dog!!). Thus, for the summer months, they run the reservation software at the lodge (they have one computer in an office at the lodge!).

The beauty of this system is that the MS-Access database can be transferred as a simple file on disk. This is no more work then transferring an Excel or Word document. This "one" file for ALL OF the data has been a great feature of ms-access since day one. Taking that ONE data file to the lodge means that WHOLE system is now transferred to the one lone computer at the fishing lodge.  The staff member is now ready to work! This single file concept for the data also simplifies backup, maintains, and essentially means that a complex data application is really just a simple windows file.

It would make no sense to hire a database administer to fly out to the fishing lodge to support, setup and maintain some type of database system on that one computer.

At the end of the summer fishing season, that data file (and staff member) returns back to the "main" office.  Thus, once again, moving a compete system with contacts, reservations, and full sales data is really just a simple file copy.

The reservation system has a nice large Outlook-like calendar display for booking customers.  The application also features customer relationship features such as reminders and follow-up for the sales process. And, of course once the sale is made, then the system handles invoicing, balance due etc. And, these invoices can be sent via email (in PDF format). So, the system covers both the sales process, and the daily practical managing such as occupancy reports for the lodge.

An Access application is thus simple, easy to maintain, and yet you have powerful self contained multi-user database system, but none of the hassles of having to install a database server at the lodge.

Tags: Microsoft+Access, Micro-ISV

Powered by Qumana

Real World Access (1)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

Graham Mandeno’s joinery

A medium sized business - a timber joinery - which builds very high class custom door and window assemblies for well-heeled clients.  They understand Excel and used to set up spreadsheets to calculate quantities of time and materials needed for different assemblies.  The results of these calculations were used to produce customer quotes.  This was very flexible, but also extremely time-consuming, so they asked me what could be done.

I set up a database for them (originally Access 2 on WFW, now Access 2003 and SQL Server on SBS) in which they could define "templates" with each template being associated with an Excel spreadsheet.  Each template was related to a table of input and output parameters, with each parameter being associated with a given cell in the spreadsheet.  The application asked for the input parameters, fired up an automation instance of Excel with the spreadsheet open in the background, popped the parameter values into the required cells, recalculated the spreadsheet, and then extracted the results from the output parameter cells.  The spreadsheets could perform whatever calculations they required, provided the necessary values were placed in the appropriate cells. Once the quantity of time and materials had been ascertained by this process, it was an easy matter to apply unit costs from the database and generate the quote.  Also it was easy to create variations (how much would this cost in mahogany instead of cedar?)

As time went on, the app has grown with the addition of invoicing, order entry, stock control, CRM, links to MYOB accounting system, etc.  Basically it now runs their entire business!

Tags: Microsoft+Access, Micro-ISV

Powered by Qumana

Access is too good

Ok, I am a fan of Microsoft Access.  Like a lot of fans, I find it hard to understand why the world is not teeming with fans for the same things I am a fan of.

But in the case of Access, I have noticed some contradictions that I frankly can't understand.  Some of these contradictions were highlighted for me at a an all-day event I attended this week, a public training day put on by Microsoft.  This event was all about 2007 Office, and also Vista.

Don't get me wrong here...  This was an excellent event, well attended and well run, and I enjoyed it and got a lot out of it.

But where does Access fit in?  Why wasn't Access featured?  Here are some pertinent facts:

• Microsoft have invested huge resources into 2007 Access, multiple times more than they have in any previous version.
• The JET database engine has been significantly extended.
• The new features in 2007 Access that make it even more attractive, both to information workers and developers, are exciting and extensive.
• Microsoft at the product development end have clearly signalled an intention for Access to have a very bright and significant future.
• As a RAD tool for developing utilitarian database applications, nothing else comes anywhere remotely close to Access's abilities.
• Access newsgroups continue to be the most active of all the Office-related newsgroups.
• Microsoft continue to significantly increase the number of Access MVPs awarded.

I could go on and on.

So, how can this happen?  Microsoft has got a fantastic product with a fantastic future, but the evangelists act like they are not even aware of its existence.  Microsoft put on a large public function, with a focus on Office 2007, and there's apparently no intention of even mentioning the word Access even once.

(Well, as it happens, Access did get a small mention, in response to the input of a certain "community influencer" - but that's another story!)

One of the sessions at the conference was called "The 2007 Microsoft Office System: the next generation development platform".  This was very interesting, about using C# to make add-ins.  Fair enough.  I know there were a lot of Office developers in the room, already familiar with VBA, for whom any managed code demonstrations would have been more relatable in VB.Net as against C#, but that's beside the point.  As for Access developers, I would imagine that for the immediate future at least, VBA will continue to be the programming language of choice, and again, Microsoft seem to have committed to VBA's future.

But all that aside, this event was supposed to have a focus on 2007 Office products, and using them as a development platform.  Wouldn't you say, of all the Office products, there is more application development done in Access than any of the others?  Wouldn't you expect this to continue to be the case?  So wouldn't you expect Access to get a mention in this context?  We had examples of Word, Excel, Outlook, PowerPoint, but not Access...  Why?  Is it because Access as a product, and Access development, are too different, too unique, too many exceptional aspects, to be able to comfortably fit in?

Here are a few possible explanations I've thought of:

• Erik Rucker and Clint Covington are the front men for an intricate optical illusion hoax.  There will be no Access in retail Office 2007.
• We are onto a hidden clue to the secret of Microsoft's business success - spend 100 man-years (or whatever it is) building a product upgrade, and then don't tell anyone about it.
• Access is so good that it just sells itself, so no promotional effort is needed.

Can anyone think of a better explanation?

Tags: Microsoft+Access