Need suggestion on Excel DB

  • Thread starter Thread starter Andrea
  • Start date Start date
A

Andrea

Hi,
I'm creating a database application for my sister's
business. They did not get Access and as a startup
company do not want to spend the money on it. I'm quite
comfortable with developing the DB in Excel but am
concerned with the integrity of it. This is what I have
done so far:

1) Sheet 1 - Basically this maintains the unique Key
field - one of these for each matter brought to the
company.

2) Sheet 2 - Each matter can have multiple clients so
this sheet tracks clients by the Key from sheet 1.

3) Sheet 3 - Each matter can also involve other parties
(one to many) so this sheet tracks 'others' by the Key
from sheet 1.

4) Sheet 4 - This is a GUI data entry sheet for my sister
and employees to retrieve any of the above data thru
multiple search methods. All other sheets will be hidden
and only accessible thru a manager login who understands
how to do sorts and knows that they must be done
carefully!

My questions:
1) Any lessons learned about having a DB like this and/or
anyone see any major flaws in this?

2) Looking for suggestions on the GUI interface. Looking
for a good way to allow edits to the fields without
having an 'edit' button next to each field on the sheet.
Could use forms but 1) same as having an 'edit' button'
and 2) runs clumsily.

Any help would be greatly appreciated!
Andrea
 
A few suggestions.
Take a look at the "Excel Template Wizard"

Once you have rejected that:

Assume your sister is trying to mess up the data. I know she's not but
don't give her the chance anyway.

Protect the whole GUI worksheet, call a userform from
Workbook_SheetBeforeDoubleClick and absolutely verify the data integrity
before writing it back.

Insist that a client exists in the database before allowing a reference
to it. Offer the clients from a dropdown list.
If necessary, pop up another userform to let them enter client details.

Put a "Cancel" button on the form as well as an "Ok"

Imediately before you write a record back, open a log file for append,
print# the details to the log file and close it again.

That way, when it all goes wrong, at least you can find the details
using wordpad and the business doesn't go bust.
Hi,
I'm creating a database application for my sister's
business. They did not get Access and as a startup
company do not want to spend the money on it. I'm quite
comfortable with developing the DB in Excel but am
concerned with the integrity of it.
Click to expand...
<snipped>
 
It might be worth it to look into having a real database such as the free
Firebird or MySQL to store the data.
You could than have a user form as the GUI linking with the database via ADO
and ODBC.
This has 2 advantages.
You don't have the limit of 65536 records and you have the power of a real
database, for example you can use indexes to speed up queries.
Excel might be fine for now, but these databases always get bigger than you
think.

RBS
 
You can use the Access database engine (JET) if you have MDAC (Microsoft
Data Access Components) installed. This way, if you decide to get Access at
a later date you can link a front-end to your JET database and you're
good-to-go!

Once you have MDAC installed, you can program the objects using DAO or ADO,
if you're using the JET database it makes sense to use DAO as it is
specifically designed to manipulate JET objects.

You can download MDAC from the microsoft site (obviously) for free.
 
Back
Top