none
Seeking advice on how to get the benefits of Access with the flexibility of Excel RRS feed

  • Question

  • Just started with a new company, not sure what to recommend.  Right now everything is done in Excel.  I'd like to move to Access for obvious reasons, but there are good reasons to keep some things (maybe everything) in Excel.

    In short, we do custom 'massaging' of (single table) data sets from a few hundred to maybe a million records.

    This involves data cleanup, dedupe/suppression, reporting; stuff very well suited to Access/SQL.

    Every job also includes complicated logic best written as Excel formulas.  It would be inefficient/pointless to code each job's logic in SQL (the turnaround time is usually less than a day, 5 to 20 jobs/week).


    Is there an efficient way to keep single-table data in Access (for scripts/queries and as a central data source) but also allow people to work in Excel and automatically append columns to the Access data?

    Off the top of my head, I imagine an Excel spreadsheet querying from Access where employees append columns in a named range linked back to Access.  I could write all the automation I need from joins of those two tables.

    Any more elegant solutions?  Happy to provide more detail.

    Thanks a million for any advice!

    -P

    Edit: I should add, if this is an inappropriate forum for the question I'm happy to move/resubmit anywhere you suggest.  Thanks again!


    • Edited by Snachmo Friday, August 21, 2015 6:50 PM
    Friday, August 21, 2015 6:48 PM

Answers

  • I should have mentioned upfront - there's no need for concurrent sessions.  One person owns each job beginning to end.

    Slightly deeper explanation:

    There's not much to it; we get a table of data, do some updates and append some formula columns.  Problem is the table layout and logic change with each job/client so there can be no meaningful template, and clients can request logic changes any time.  VB/SQL can't compete when flexibility is more important than scalability.  Excel is perfect for this part.

    There are, however, some parts which VB/SQL would handle much better, but they will not always come entirely before of after the parts best done in Excel.  

    In a nutshell I'd like to be able to use Excel's formula language and SQL on the same data.  This could be done in VB but writing it to accommodate any file layout would be a lot of work.  This isn't out of the question, but yea... trying anything else first :)

    Friday, August 21, 2015 9:34 PM

All replies

  • Hi. If you don't need to modify the data from Access, perhaps you can just "link" to the Excel sheets in Access.
    Friday, August 21, 2015 7:25 PM
  • Thanks for the response!

    We'll really need two-way updates for Access to be a better solution; changes in Access need to update formulas in Excel and vice versa.

    I'll need to be able to write formula columns in Excel, run an update query in Access, re-open the Excel sheet and see formulas recalculate on the new data without toooo much rigmarole.

    Friday, August 21, 2015 8:28 PM
  • Hi. Unfortunately, two-way streets between Access and Excel is a bumpy road. Instead, you can feed Access the data from Excel and let Excel do all the updates. Or, you can import the data into Access and export it to Excel (including new formulas). In other words, I don't think you'll be able to have people in Access and people in Excel modifying the same data at the same time if the final version of that data will be in Excel.

    So, if you want multiple users to work with the data at the same time, I would recommend moving the data into Access and have everyone at it. When it's ready for Excel, export the data, including the new formulas, to Excel - preferably, by using a template.

    Just my 2 cents...

     

    Friday, August 21, 2015 8:51 PM
  • I should have mentioned upfront - there's no need for concurrent sessions.  One person owns each job beginning to end.

    Slightly deeper explanation:

    There's not much to it; we get a table of data, do some updates and append some formula columns.  Problem is the table layout and logic change with each job/client so there can be no meaningful template, and clients can request logic changes any time.  VB/SQL can't compete when flexibility is more important than scalability.  Excel is perfect for this part.

    There are, however, some parts which VB/SQL would handle much better, but they will not always come entirely before of after the parts best done in Excel.  

    In a nutshell I'd like to be able to use Excel's formula language and SQL on the same data.  This could be done in VB but writing it to accommodate any file layout would be a lot of work.  This isn't out of the question, but yea... trying anything else first :)

    Friday, August 21, 2015 9:34 PM
  • Hi. Sounds like you have a good handle on it. Good luck!
    Friday, August 21, 2015 9:44 PM
  • Thanks for taking the time, much appreciated :)
    Friday, August 21, 2015 9:46 PM
  • Well, you've come to the right place.  You should definitely use the right tool for the job; do Excel things in Excel and do Access things in Access.  Don't try to force one technology to become another technology.

    Please start with the links below, just to get a general idea of how to move data between Access and Excel.  Again, do the spreadsheet stuff in a spreadsheet, and to the database stuff in a database.

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    Also . . . .

    http://www.erlandsendata.no/english/index.php?d=envbadacexportado

    http://www.erlandsendata.no/english/index.php?d=envbadacexportdao

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, August 22, 2015 1:08 AM