Results 1 to 12 of 12
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    How to effectively split frontend and backend of this database?


    Hello,

    I hope I have placed this thread into the correct category...

    we are a small company with a database coming and I am thinking about sharing solution. We have multiple options:

    - Upload the tables on Dropbox but I have heard that there might be problems with this.
    - Somehow combine Access and MySQL database (can have server for free). I have no idea how that would work though.
    - Any other possibilities? There will a maximum of 5 people operating the database so there is no need for something fancy.

    Thank you for your ideas,
    Tomas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Use the split db on MySQL.
    as long as you have the ODBC driver,you can let all users access the backend.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Where are the 5 people located? On the same LAN?

    Split the Access dB: the BE (back end) has ONLY the tables and the FE (front end) holds everything else (forms, queries, reports and modules).
    If everyone is on the same LAN, say on one floor, just link the tables in the BE to the FE.
    If you convert the Access BE to something like MySQL or SQL Server, then you will need to create an ODBC connection to have access to the tables.

  4. #4
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Unfortunately it's not the same LAN all the time. It needs to be managed from multiple locations.

    Is a SQL server the best option for us? And does Access have some kind of "semi-advanced" user friendly environment to accomplish this?

    Thank you,
    Tomas

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access is basically a client based system - everything on a LAN.

    The WAN options are host based (i.e. everything on an internet server) are Azure (which is a form of SQL Server) or SharePoint.

    After that you are into using things like VPN with terminal server or citrix.

    There are also access hosting websites out there which is effectively providing a VPN/terminal server type service.

    Other options include Teamviewer (but each external user needs a local device to connect to) so probably not what you want, but good for occasional connections

    What is right for you will depend on your requirements and budget. You need to consider current and future requirements and the environment you are in - such as do all users have fast internet connection?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As Ajax advised, get your requirements identified and documented. Look not only at your current business, but also include any plans for future business directions. Do not be too quick to buy hardware/software that may not satisfy/support your needs.
    Do some analysis to identify the business to be supported by this database. Incorporate even the "fuzzy" ideas in your model. This will identify how the things in your current and future business will fit together logically. This will assist your design and simplify future development since you'll know these things exist/were expected.

    Do/will you need remote online access to your data? (internet based???)

    Once you identify your requirements (holistically), you may find that other solution options are available --buy in to a service, build your own, COTS....

  7. #7
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    I thought about it.

    The database is not going to be very big and will be operated by only few people. I would like to get a free solution.

    So I should split the database to front and back end, then make ODBC connection. But where? I need internet based online access. Do I need to look for SQL server hosting providers? How does it work?

    Can it be done free of charge?

    Is there some kind of a manual which will tell me how this is done step by step?

    Thank you
    Tomas

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    should split the database to front and back end,
    that is standard operating procedure, even if system is only going to be used by a single user.
    Can it be done free of charge?
    No, anything online involves a charge. With access, consider Azure or SharePoint
    Is there some kind of a manual which will tell me how this is done step by step?
    It is a wide subject with many possible solutions. look online, ask your questions of google/bing

  9. #9
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    I just talked to our ISP and there is a possibility to create a MySQL or a MSSQL database for free. Comes with the hosting. Would that work? I have up to 1 GB space there. I have a server, login and a password.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ask them about how you would connect to the db from access based on your machine. Most ISP's do not allow direct access for security reasons, you need to go through API's

  11. #11
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    No, anything online involves a charge. With access, consider Azure or SharePoint
    I did some searching today. If I understand correctly, all I need is Office 365 Business Premium (which includes SharePoint online) and I will be all set, with online Access solution.

    Correct?

    Tomas

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Maybe. You know your requirements. My understanding was access was dropped from office 365, but looks like it is being reintroduced so personally I wouldn't like to say yes or no until I know someone who has tried it.

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

Similar Threads

  1. Link split database backend to another?
    By Cratebug in forum Access
    Replies: 5
    Last Post: 02-11-2016, 04:02 PM
  2. Replies: 4
    Last Post: 03-31-2015, 01:20 PM
  3. Replies: 2
    Last Post: 01-15-2015, 08:18 PM
  4. Frontend Backend Database Questions
    By data808 in forum Access
    Replies: 29
    Last Post: 03-26-2014, 11:58 PM
  5. Replies: 1
    Last Post: 11-25-2011, 11:16 AM

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