Forum Discussion

tim_p70f9's avatar
tim_p70f9
Copper Contributor
Jun 10, 2020

Access is this possable

Hi all
I’m new to the forum I’m looking at weather access is suitable for my needs
Currently we are using excel for users to record the serviceability of items the problem is we require multiple users to fill in the sheet at the same time which runs into all sorts of problems so my thought were could we put an access database file on a network shared drive and excel could then connect to the relevant kit the users can update the options and then update the database the it infrastructure is very restrictive
Any ideas kind regards tim
access
  • George Hepworth's avatar
    George Hepworth
    Steel Contributor
    Jun 11, 2020

    tim_p70f9 

    All good up to the point where you want to use Excel as an interface. That's within the realm of things that can be done, but should not be done.

     

    If you want to create a relational database application to manage your inventory of serviceable items, Access is an excellent choice. However, the concept of a relational database application relies on three layers, or tiers, or components.

     

    First is the data layer. This consists of the tables in which your data is stored in a properly normalized design. 

     

    Second is the interface layer through which users interact with that data. This consists of the forms, reports and queries that you create for this purpose.

     

    Third is the logic layer. This consists of the VBA written to manage and manipulate your data and to automate the interface.

     

    Notice that nowhere in there does a spreadsheet appear. It is an excellent REPORTING tool once your data is ready to be consumed by an end user who needs to see only the output. Excel spreadsheets are a poor second choice for actually trying to manage a user interface to relational data, though.

     

    Invest some time in learning the process of Normalization. That's the key to success with any relational database application.

    • tim_p70f9's avatar
      tim_p70f9
      Copper Contributor
      Jun 11, 2020
      Hi thanks for the detailed reply
      My biggest problem is my employers it system is very restrictive all users have Microsoft word excel and PowerPoint and that’s about it the chance of getting it to allow us to install any other programs is basically 0. I’m trying to find a solution would the user interface require installation if it could be run as a stand alone executable without install then I guess it might be possable
      Kind regards tim
      • George Hepworth's avatar
        George Hepworth
        Steel Contributor
        Jun 11, 2020

        tim_p70f9 There is an Access runtime which doesn't require the full development version of Access to run your relational database application, but it would still require installation so that might not be acceptable.

         

        Unless you can get permission from your IT, I would say your best bet could be to optimize your current Excel solution as best you can.

Resources