Costs Database - Accessible by numerous staff at various sites

  • Hi All


    I am about to start a large ish (hopefully) vba project, and just after some more experienced advice/thoughts on it before I invest too much time.


    My company (multinational) has a need for a database of freight costs, each freight cost will have 7 fields (maybe more):


    POL (port name) / POD (port name) / Carrier (short string) / Start (Date) / Valid To (Date) / Type (a few set options) / Cost (number) ------- And maybe a few other costs types as well depending on what is needed.


    These costs then need to be presented to each user based on what they enter for POL / POD / Carrier / Type etc.



    Now I know I can do this in excel, have a sheet with all the rates as each row (hundreds of rows) and then a user form to present to the user, but I was thinking access might be better, having the rows as a backend database, and then a simple front end user form referencing that backend data.


    I'm just after what you very talented and knowledgable individuals would do in this case before I invest too much time into it?


    I am much much better at Excel vba than Access, but I have made smaller access programs in the past, although I'm worried about the backend being on a server shared drive and that causing issues.



    Or is there a whole other alternative I could look into beyond Excel/Access, one that has a short learning curve as I would have to learn how to do this myself.


    The backend/master freight costs data would need to be easily updated, excel/access is easy to do this via a template file etc but that needs to be considered.


    Thanks in advance :)

  • I would sy that Access is the best Option.


    If your company uses Office 365 then you can use Excel's Collaborate feature or Microsoft Teams.


    Collaborate in Excel


    I can't see why you need VBA for this though

  • Not saying it would need VBA in Access but it might.


    In excel it would in my opinion.


    A shared workbook would be a nightmare I think, I have done them in the past and always horrible experience, plus the majority of users dont need to do anything other than bring up the costs they need. Data entry etc would be handled separately which is why I was thinking of a Access backend / frontend setup.


    Does Access still have the free frontend version? As I'm fairly sure the company does not have Access for all users.




    Access is going to be difficult for me haha, I haven't really done anything on it for years now and the user form to reference the data will need to be fairly complex in my mind, like the POL / POD names would need to appear as a user types them for example, as there will be literally hundreds and hundreds of port names.

  • I don't think there is a free version of Access. I believe you can generate a shareable database but for an international company I would he thought they would be better hiring a developer.

  • So long as you have at least one license for Access you can create your DB. Split the Front End (Forms and Queries) from the Back End (Tables). Put the F/E on each PC. Download MS Access Runtime (which is free) and put it on all unlicensed machines. Changes to the Design of both the FE and BE of the DB can only be made from a licensed copy. Look at this link for further info on MS Access Runtime.


    https://www.hitechcoach.com/mi…access/65-runtime-version