Replace Excel sheet completely with Access?

  • Hi everybody,


    in my new job I am working among other things with a big and very, very slow excel file. The purpose of the File is to show the Customers some information. For each product there is a new Excel File "X" created.


    First I have to look for some Data in SAP and insert it in the file "X", than the file uses some lookup-makros and computes some information. After this information is created, some new information gets created through references to Excel File "A" and "B". The Files A and B are used as database at the moment. The generated information is then represented for the customer.


    I haven't build the Excel file and think there is much room for improvement. My idea is now, to use VBA instead of the makros to automat all this stuff with less consumption of resources and use MS Access for the Files A and B. File A contains 30,000 rows and File B round about 200,000. I am familiar in programming, but haven't used MS Access ever before.


    Before I want to start my project, I would like to figure out, where I should start. Should I use MS Access only as Database and do the computing in Excel, or should I do everything in MS Access and use Excel only as input/output file? Yet, I haven't figured out where to start and hoped to find here some help. Good sources for further information would be appreciated :)


    Thanks

  • Re: Replace Excel sheet completely with Access?


    For a starter, I would read the following white paper on design of databases. It is a good read and will help you to make the right decision


    http://sbuweb.tcu.edu/bjones/2…fundamentalsdb_design.pdf


    Once you have read and understand the concepts, look at this basic tutorial on setting up a database


    http://www.accessmvp.com/strive4peace/


    Before you start to build, layout a schema on paper to make sure that you have captured all that you need. It will be a lifesaver as you start the build process to have a clear idea of what you have, what you want to achieve and what you will need to do so.

  • Re: Replace Excel sheet completely with Access?


    Hi Alan,


    thanks for your fast reply. I will use both your posted links :) I often use a pencil and paper to illustrate my schemas. It always helps me to detect faults and needless steps.