Results 1 to 5 of 5

Simple web-based database

  1. #1
    cohlar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    2

    Simple web-based database

    Hello,

    I am completely new to MS Access and would like to double check my understanding before I deep dive into endless work.

    I am into ecommerce, I work alone, I currently use Google Sheets as a sort of database to store all my sales data which I update once a month - some of this data is calculated in Google Sheets based on other Google Sheets tables (e.g. FX rates, VAT rates, etc.). I then use MS Excel and PowerQuery to extract the data I need from my Google Sheets, transform it as needed (mostly remove the unnecessary columns and make some basic calculations), and load into my Excel which I then use as a reporting tool.
    I am very happy with this setting given my lack of programming skills and time available to deal with it. One of the main advantage is that since my Google Sheets are online, I can open my Excel spreadsheets from any computer and they'll still update based on the latest data.

    However my sales spreadsheet has become too heavy, too slow, I guess I've reached the limit of Google Sheets for my needs, and I am looking for the quickest and simplest solution for me with which I can scale. As a result I am considering to migrate all my data to MS Access (I hope I can achieve all my current calculations with simple relationships and calculated fields in MS Access). I want to us MS Access just as a database to hold (and make some basic calculation on) my data - not as a custom developped front-end app.

    I have read about all this before coming to this forum but I find it hard to understand all what's out there given my lack of expertise, so I'd like to cross check:
    Does MS Access web databases sound like a good solution to my problem?

    Many thanks for your help,


    Phileas

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,355
    Access is actually two products. A RAD tool for creating front ends/reports etc and utilises as a default the ACE rdbms as a backend which comes free with access. You can use other rdbms's such as sql server, my SQL etc. Sounds like you just need the back end part. ACE is not a web based database, it is a client based one (i.e. the app is located on your machine or network and utilises local memory). So if you are a single user and working from a laptop you would locate it on your laptop. If you want to be able to connect to it from other devices you would need to use remote desktop or similar.

    web based databases of any sort have their own security requirements and you typically will need a front end to be able to access them.

    New users often try to 'share' access on One drive or equivalent. Because of the ACE file sharing features, even with a guaranteed single user, this is unlikely to work effectively and is not recommended.

    MS did try a web based solution utilising sharepoint as a back end, but it has now been discontinued

    You might want to look at using Office 365 rather than google sheets. Doesn't provide access as a web based solution but you might find their excel is more powerful than google sheets and you can utilise things like powerapps

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,107
    IMHO the reason Excel workbooks become so slow and cumbersome is because the entire data set along with all the calculations for every cell, the charts, pivot tables, etc. etc. must all be loaded each time it opens. A relational db isn't like that, assuming it's designed properly. However, I think you'll find that it's easy to build something in Access, but it's also very easy to do poorly. You cannot even rely entirely on the M$ documentation to always steer you in the right direction. Generally speaking, an example of this would be the use of multi value fields in tables, the use of which seldom has any upside.

    About the only use for drop box type of file hosting (including M$ One Drive) is to store files. You could upload a db to a local drive, work on it locally, then put it back on the file share, but that would be about it. As noted, do not allow Access to work on data that's located on such a drive. Perhaps you can speed up workbook performance by splitting data by year or quarter period and maintain it faster while you go about developing a db tool for your own use. I wouldn't advise dropping the workbook and diving into Access. Nor would I recommend building an Access app then switching over to it cold turkey.

    Learning as much as you can by reading books, online data or watching video tutorials will serve you well. Learning should also involve practice, but don't assume you can build your finished db in practice fashion.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  4. #4
    cohlar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    2
    Hi,

    Thank you both for your answers - then it seems like MS Access is not exactly what I need. Which leads me to two additional questions:
    1. Could I do the same as I do today but with Excel files rather than Google Sheets? (i.e. having an Excel file on OneDrive / Google Drive and import data from that cloud Excel through PowerBI)
    2. Would the free version of SQLServer (or the version included in Office 365 Home edition) be a good solution to my problem?

    Many thanks for your help, merry Christmas and happy new year!
    Phileas

  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,355
    1. I would think so, it is not something I have tried
    2. Only by placing on a web server - so back to square one

    You need to research the difference between client based solutions and web based ones. Nothing of any significance (in terms of business applications) on the web is free

    If you have a 'base' computer, in addition to using remote desktop you can also investigate using something like teamviewer or Jump desktop

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

Similar Threads

  1. Replies: 2
    Last Post: 11-10-2017, 07:52 PM
  2. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  3. Replies: 4
    Last Post: 01-17-2016, 02:52 PM
  4. Simple Database
    By officefiend in forum Access
    Replies: 1
    Last Post: 07-16-2013, 07:26 PM
  5. Really simple database
    By aveit in forum Access
    Replies: 3
    Last Post: 03-23-2011, 04:43 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
  •  
Tech Forums: Microsoft Office Forums