Is it feasible to use Excel as a database front-end?

[Kobus]

I need to automate existing excel spreadsheets that we use for bills
of quantities. The sheets contain items, rates, quantities and totals
with various reports and monthly payment columns. The problem is that
the system becomes elaborate and easily breaks. It is difficult for
other users to work on the sheets because they are not sure how the
links and formulas work.

One solution is to abandon Excel and write a database program but
this
take the Excel feeling away and users will not be able to add their
own special reports or formulas. I thought it should be possible to
write a database program (using VBA) with Excel as the front-end.
This
will enhance reliability but retain flexibility compared to Excel
alone.

Is it feasible to have Excel as a front-end ("form") linked to the
database data (in Access) and to be able to add, delete, edit and
link
the data? All this must happen while the database and business rules
are enforced.

 

[Guest]

Feasible is a relative term. It is certainly possible to use Excel as the
front end to the database, I've done it myself more than once. It can
actually provide some benefits such as providing the 'flat-file' buffer for
the data: you can pre-validate it in Excel before moving it off into the
database. But the total effort may be time consuming and initially
maintenance/tweaking heavy.

I'm thinking you might want to rethink your approach and take more control
of the workbook you've already developed. At the same time, make it
friendlier to the user. You probably/should have some sheets in the book
that the end users can only enter specific data into and perhaps some other
'standard' sheets or functions that report this data in various fashions.
The end users should not be altering these sheets with their own added
formulas and features. If they've got special reports to prepare those
should probably be on added sheets for those special uses, whether added by
whoever maintains the workbook structure or by the end user him/herself.

You could add command buttons (or text boxes associated with macros) to
provide specific functionality on some sheets and require that all changes to
controlled sheets be performed through user forms that those buttons/boxes
bring into view. You could add comments to cells that are using formulas to
derive information explaining where the info came from and how it the
underlying formula works. That's for pure math type functions, for
VLOOKUPS() and such, simply explain what the data represents as "date
associated with invoice # in column A". Possibly even writing a User's Guide
would provide great benefit.

I've got something that's probably similar that I deal with: a very complex
labor utilization tracking workbook that also prepares a complex invoice
based on labor utilization, purchased materials and travel expenses. The end
user (the accounting department) is actually not permitted to 'touch' a
worksheet except for one column on one sheet where current hours for the
employees is entered. All other information is added to, deleted from or
edited via userforms called up by either buttons/textboxes or validation
lists that they choose from to perform a specific action such as adding a new
employee to the labor sheet or current material/travel expenses to those
sheets and even to preparing the final invoice. Everything else is locked
down and protected from accidental change. When I was first given the task
of managing this workbook, it was completely open and people often overwrote
formulas with hard values, put the wrong information into cells, and
generally simply made it an unmanageable, undependable mess that only got
worse over time. It has now been in use over 3 years without change and is a
reliable tool presenting consistent results - to the point that the invoices
have passed numerous audits by government auditing agencies.

 

[Kobus]

Feasible is a relative term. It is certainly possible to use Excel as the
front end to the database, I've done it myself more than once. It can
actually provide some benefits such as providing the 'flat-file' buffer for
the data: you can pre-validate it in Excel before moving it off into the
database. But the total effort may be time consuming and initially
maintenance/tweaking heavy.

I'm thinking you might want to rethink your approach and take more control
of the workbook you've already developed. At the same time, make it
friendlier to the user. You probably/should have some sheets in the book
that the end users can only enter specific data into and perhaps some other
'standard' sheets or functions that report this data in various fashions.
The end users should not be altering these sheets with their own added
formulas and features. If they've got special reports to prepare those
should probably be on added sheets for those special uses, whether added by
whoever maintains the workbook structure or by the end user him/herself.

You could add command buttons (or text boxes associated with macros) to
provide specific functionality on some sheets and require that all changes to
controlled sheets be performed through user forms that those buttons/boxes
bring into view. You could add comments to cells that are using formulas to
derive information explaining where the info came from and how it the
underlying formula works. That's for pure math type functions, for
VLOOKUPS() and such, simply explain what the data represents as "date
associated with invoice # in column A". Possibly even writing a User's Guide
would provide great benefit.

I've got something that's probably similar that I deal with: a very complex
labor utilization tracking workbook that also prepares a complex invoice
based on labor utilization, purchased materials and travel expenses. The end
user (the accounting department) is actually not permitted to 'touch' a
worksheet except for one column on one sheet where current hours for the
employees is entered. All other information is added to, deleted from or
edited via userforms called up by either buttons/textboxes or validation
lists that they choose from to perform a specific action such as adding a new
employee to the labor sheet or current material/travel expenses to those
sheets and even to preparing the final invoice. Everything else is locked
down and protected from accidental change. When I was first given the task
of managing this workbook, it was completely open and people often overwrote
formulas with hard values, put the wrong information into cells, and
generally simply made it an unmanageable, undependable mess that only got
worse over time. It has now been in use over 3 years without change and is a
reliable tool presenting consistent results - to the point that the invoices
have passed numerous audits by government auditing agencies.







- Show quoted text -

I think you summed it up very well and I agree with the protected
sheets with buttons idea.
I do however think that a database back-end is required to make the
data more manageable. Every month has invoices based on the bills and
at the moment we copy the last workbook to use for the next month with
links between the workbooks. We then have 30 workbooks after 30 months
each with 20 or more sheets of which about 15 sheets contains data and
the rest are reports. This becomes difficult to manage.
I thought of creating querytables in Excel to import the data and then
to update the database with adds, deletes and changes. I was hoping
someone may have a move elegant way of doing this.

 

[RB Smissaert]

Sounds to me that you have come to a stage where a real database backend
might be
the better option. It will be a lot of work to make the change, but will be
worth it in
the end.
If you do decide to go with a database then have a look at the free SQLite
database.
Easy to install and use and very fast. Not all users my have Access,
although I think
you can use a .mdb file as storage without having Access installed. Another
option is
to use text files as the backend. A further option is to use .xla sheets as
the backend storage
as the user can't see these.

RBS

 

[Guest]

It sounds as if you have a good understanding of why you do probably need to
move it off into a database rather than keeping it in Excel. The risk of
loss of data becomes higher as the number of files involved grows, if nothing
else. The effort involved in using Excel as a front end either for an Access
database or something more robust such as SQL Server or mySQL (via ODBC) will
be cause for a lot of work initially - but will probably turn out to be worth
the effort at time goes on.

Don't forget to do a close examination of the data structure you have now
and do a good design on the database's table relationships. Another time
eating process, but absolutely necessary if your database is to really do you
any good at all in the future. The more time you spend in the requirements
analysis and data relationship analysis, the less time it will take to
actually create, implement and integrate the new system into your workplace.

 

[MH]

Access is built for this type of work, why are you struggling with Excel?

Store the data in Access, use the forms provided by Access to develop the
app and the reports within Access to display the information. If you need
to analyse any information in detail fine, export it to Excel, but keep the
original data in Access.

MH