CODEWITHSUNDEEP
ms-access
Bing
Bing

Reputation: 21

Building user interface in Access or Excel

I am trying to build a user interface which will allow users to choose what kind of information they want, then based on these selected conditions, I need to query an access database and retrieve the corresponding data for future calculation. During calculation, there are two additional tables that I will query from. Finally, I need to display the calculated results to users. Now I have some questions:

  1. Which one will be easier if I use excel as a front-end to users and retrieve querying data from Access to excel vs. I use access forms as a front-end to users and directly work with access to query data?

  2. Does access forms can perform functions like allow users to select options from a drop down list? and once options selected, Access will query the target info?

  3. Can Access perform intermediate calculation for large amount records? Will it get very slow? Compared to excel, which one is better in terms of calculation?

  4. I never use Access before and just know a little about Excel VBA, not totally familiar with it, not to mention object models. So, in this case, which way is time-saving for me? or they are probably the same time-consuming?

  5. Anything else that I should be aware of?

Thanks so much!

Bing

Upvotes: 2

Views: 3603

Answers (1)

mellamokb
mellamokb

Reputation: 56779

Definitely Access - it is specifically made for working with, querying, and reporting on data. The language of VBA that you have experience with in Excel is the exact same language used for coding in MS Access!

  1. Access, no question!
  2. Absolutely. Of course it doesn't magically know this as your requirement. You have to drop the combo box on the form yourself, and then in the OnChange event of the combo box, alter a query statement that displays results to filter based on the user's selection.
  3. Sure. Make a temp table (a table that is only used for the calculation), populate the records with INSERT statements, apply intermediate calculations with UPDATE statements, and display the results. Access is built for this kind of thing and will most likely be faster than Excel.
  4. Access uses VBA as well. There may be a larger learning curve to switch, but if you are familiar with the user interface objects in Excel such as Button, Combo Box, etc., they will be very similar and yet more intuitive in Access (Because that's what Access was made for)
  5. Access has some quirks. Google a few tutorials and get some background on the ways Access is traditionally used for your kinds of scenarios and it will go a long way toward streamlining your development.

Start with the simplest model that will accomplish what your requirements are, and implement it. Then work through the quirks and bugs that you find with Google as your friend, and you should be able to get something solid pretty quickly! As you get into the experience, feel free to post new questions with specific problems you find along the way.

Best Regards,

Upvotes: 1

Related Questions