Starting a New DB

[MarkNie]

Hi All

I am currently in the process of starting a new DB, the old one is out of date and needs to be revamped.

What I would like to find out is what will be the best way to do the following:

The DB used to work with the user inputting info into an excel spreadsheet, once that was finalized it was then imported into the DB.

What they have asked me to do is stop the duplication of work by having to insert into excel and then import into access. So what would be the best way to do this.

I have some theories but would like some other opinions please.

1. Linking the Excel spreadsheet to the Access DB and then manipulating the data from there. The only problem with this is once the financial quarter starts the data needs to be locked down for the month.

2. Just creating the table in Access and keeping all the data there but then have the same problem as above and if the DB falls over or the data goes missing, I will need to create and auto backup every hour or so.

Well that is all I can think of for now.
Any suggestions would be greatly appreciated.

Kind Regards
Mark

 

[Trevil]

I would get rid of the Excel interface. Create the tables and forms required to provide the user interface. You could opt for a "similar to the old Excel process" as the quickest way, but you may want to add additional functionality.
Are the users required to log in, or do you track user names? You could add a "Menu" command that allows an administrator to set a "lock" flag, then have all your forms check if the lock flag is set, and if so, prevent any updates. Of course you could still allow an administrator to make changes even if locked (if necessary).
I assume the database will reside on a server to allow multi-user? You could also prevent the user from deleting (physically), but allow for a logical deletion by using a flag.
I just finished converting an Excel application, with locks, etc.

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[lespaul]

Read the fundamentals document linked below to help figure out the best way to structure your tables so your database doesn't "commit spreadsheet".



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins