Results 1 to 6 of 6
  1. #1
    guycarnegie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2

    Application Selection for manufaturing database

    The company I work for has evolved quickly from a small family company to being owned by a multinational corporation. We manufacturer "widgets" for the oil & gas industry.



    Right now we use Excel to store the manufacturing data for our widgets. We have an excel "register" which contains the manufacturing data for around 20k widgets which we have built over the last 10 years or so. We have different families of widgets with each family's data being recorded in different sheets within the register workbook. Each worksheet has different column headers to reflect the different parameters associated with each type of widget.

    Since we have grown rapidly, we now have around 20 manufacturing technicians, each of them building, testing and recording widget data. They record their data into a individual record sheets (Excel), which someone then has to copy the data from into the register. We have macros which do this, so the effort is not enormous, but I find it difficult to envisage a more "clunky" solution, which is bound to run into problems soon as we continue to grow. It is complicated slightly in that we also need to import some other data from external suppliers excel sheets, keyed by the serial numbers for some of the widget components. I already have a huge backlog of record sheets which I really need to transfer into the register.

    One of the main issues was the requirement for users to store data concurrently, which is why they are generating their own individual record sheets. Concurrent, direct access to the data respository will be essential.

    It is obvious to me that a database is the correct solution, but my question is what platform to build that on.
    I have a little but of training on databases and can use SQL and VBA in equal measures - albeit not experienced in access specific vba. My first choice would be an SQL database with a web based front end, but I'm working with a couple of limitations. As we are in a corporate environment, our IT environment consists of shared network folders and "dropbox" style cloud file sharing areas, as well as a globally managed web-based intranet. I can therefore store files, but asking to get SQL server or Apache up and running on a server is proving difficult.

    There is a "legacy" sharepoint area on our intranet, and I can request a site, but no idea if "legacy" means it is unsupported or could disappear at a moments notice, and I'd need to be able to administer the database myself - ie adding tables and columns etc, which I'm not sure how to set up.

    I want to get something set up to start using from year end, so I think I am therefore limited to a MSAccess type database - albeit that access for me has an "image problem". That said, it may be the best solution in this instance.

    I don't want users to have to "install" anything themselves, which means they are limited to either using an excel front end or a web based front end. I agree that a single MDB would be optimal, but I'd guess users would need to install access for that to work.

    As much as I hate to admit it, it FEELS to me like my only available solution is to create an MDB back end with an Excel front end and I'm not convinced that would give me any better a solution than my current excel workbook with individual recrod sheets. I'm VERY open to other options though, but I'm struggling to see any here.

    thanks for any input.
    Guy
    Last edited by guycarnegie; 11-28-2019 at 08:10 AM.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If your users have MS Office installed, chances are they already have access installed.

    Excel as a front end is not a good choice - fine for extracting data from a database, not so good acting as a data entry tool - and extremely weak on data security.

    With regards 'image problem' do you mean Access - the front end development tool? or JET/ACE the free RDBMS that comes with Access? The two are often confused and the fact that access is available to anyone (as opposed to say SQL server which has strong restrictions on who can access it and how)

    Your backend (database) needs to be stored in one of your shared network folders, it will not work in a dropbox environment. Each user needs their own copy of the front end on their machine - there are plenty of examples on this and other forums as to how this can be managed.

    Instead of using Access, you can develop a web based front end which can connect to a JET or ACE database. If you have the relevant skills already and don't know access, then it may be a more viable option for you, but in my experience development times are roughly 10 times longer. Whichever way you go (Excel/Access/Browser), you need to design your database and how you access it as if it is a web app to minimise network traffic. Failure to do so will result in slow performance.

  3. #3
    guycarnegie is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2
    Quote Originally Posted by Ajax View Post
    If your users have MS Office installed, chances are they already have access installed.
    Afraid not. They don't have office professional so no MSAccess front end available at the moment.
    Can a web based front end really be built without any server side code?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can a web based front end really be built without any server side code?
    Not sure what you mean. You need server side code to instruct the server to return values or to update/insert data. The bulk of this would be in sql server, but the front end still needs to identify which routine to run and send parameters. If using an access backend, you would need code that the web front end can run - take a look at visual studio.

    If this process is business critical then your IT dept should be taking an interest.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Guy

    You could have the users use Access Runtime if they do not have the Access programme.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

    I stole the following from a post by orange - it is better than anything I could write:
    ---
    "I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
    <snip>
    <snip>
    Too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

    You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."



    And this is by AJAX
    ------
    Access is not a bigger excel - it is completely different. Excel combines data and presentation (i.e. calculations) in a single view and the data is typically stored is 'wide and short'. Access stores data in tables and presents through queries which are then used in forms and reports. Data is stored 'narrow and tall'

    Access works by using the rules of normalization - in its simplest terms, stores data only once, Excel is about as far away from normalization as you can get. Consider your list of gym member activities - how many times do you repeat the same gym members name or other details? In Access you have a table to list the members details (once), another table to list the exercises and yet another the measurements. You can even have a table of categories and instead of using the iif statement you would lookup the category for any given BPM. Benefit here is if you decided to change the category, or the BPM range it applies to, you would not have to go into the code to change it, you would change it once in the table.



    My comments: So you need to know about Normalization. Here is a 5 part article about "What is Normalization?". You also need to know about "Naming Conventions", "Reserved words in Access/SQL".

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.




    Quote Originally Posted by guycarnegie View Post
    ....we now have around 20 manufacturing technicians,.......
    Are they on the same LAN in the same building?
    How many technicians will be entering data at the same time?


    Once you think you have a pretty good handle on the design, post the design here to have it reviewed. There are MANY forum members that are very good at dB design. You might also post the Excel sheets (with data changed if sensitive data) to help in the design review.



    And Welcome to the forum.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-13-2019, 06:05 PM
  2. Replies: 7
    Last Post: 02-07-2017, 05:19 PM
  3. Replies: 1
    Last Post: 10-01-2015, 09:14 AM
  4. Application Selection Report..?
    By leamas in forum Access
    Replies: 4
    Last Post: 07-12-2012, 09:29 AM
  5. Ms Access database application
    By delhiris in forum Access
    Replies: 3
    Last Post: 02-21-2010, 06:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums