Link to home
Start Free TrialLog in
Avatar of 911bob
911bobFlag for United States of America

asked on

Developing database that gets updated from Excel. Looking for best approach

I am capturing labor data in the form of an excel file (Single row per employee), and want to import that into an ACCESS or SQL database.
I am thinking of starting with Access as it is more user friendly (I Mostly VBA programming experience).

I see some examples, but one problem I am running into is that each day I get the last 5 days of data (in case the manager edits the older data), so I am looking to update an replace older data each day. One approach is to delete the last 4 days of data, then import everything as new.

Just looking for some suggestions on best way to get into database, so I am not missing something that I later regret..

Not sure how I will award point, maybe split it multiple good replys
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<I see some examples, but one problem I am running into is that each day I get the last 5 days of data (in case the manager edits the older data), so I am looking to update an replace older data each day. One approach is to delete the last 4 days of data, then import everything as new.>>

  I think your idea is a sound one if you are certain you will always get the last five days worth of data.   You'll just have to make sure that if that data is used in some way, it's noted that it could change or make sure the data can't be used for five days.

Deleteing last four days and then simply using  DoCmd.TransferSpreadsheet should work fine.

You also have one other option; linking to the spreadsheet as a "table" and working with it directly.  With that, you could compare data and only update what has changed.

 To say which approach is really better, I think we'd need to hear what the plans are once you get it in Access.

 Also, one other point; Excel does use VBA.   Still though, Access is probably the best choice for working with the data.

Jim.
Avatar of 911bob

ASKER

Thanks,
I am NOT keeping ALL of the data in the Excel file at one time, I delete the data in Excel, then re-import from a CSV each day, doing one day at a time

I think I will check the for data after I import it into Excel and then if the data for that date is there, delete the same data/ Same location (I have labor for 25 locations)  in Access then move over from Excel to Access again.

I am thinking of starting with Access them move to SQL if required. It is a small organization (they manage fast food restaurants), so currently only 5 people would be accessing and Access will hopefully be sufficient.

Currently doing a lot of VBA modules to create summary dashboards from data (Combonation  of sales/ labor/ labor $ etc. etc)
Moving to Access to allow easier report generation, and avoid File in Use issues

Just doing a gut check before I start. Easier to rule out blind holes/ dead ends now than when I have it 80% done and feel trapped.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 911bob

ASKER

Thanks for input
Personally, I prefer to:

1.  Link the CSV file to Access
2.  Import the data from the linked table "staging table" which has the appropriate indexes and data types.  This way if the data in the CSV does not link to Access in the correct format, you can convert into the correct data  type.
3.  Perform an update query for records that are already in Access, rather than doing a delete.
4.  Then append records which are not already in Access.

Actually, steps 3 and 4 may be performed in a single query, but this is a bit more advanced SQL.