Results 1 to 13 of 13

Enterprise Database Solution

  1. #1
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31

    Enterprise Database Solution

    I and my team have created a multi-user database platform that utilizes microsoft access for the FE and BE and several plugins to handle file-sharing (for images). I'm now at the point that I need to work on getting the program "online" so that users may be able to use the program in the field. I would love suggestions as to what would be the best route for securely allowing this connection and how I need to configure my BE to handle them.

    A little background: the program is an inspection management system and there are two primary sides of it; administration (desktop) and inspectors (touchscreen tablets). The inspectors perform the inspections as required by the admin and also take pictures with the tablet camera. The data is then used to compile several different inspection reports. The admin side will almost always be at the office and have direct access to the company server. The inspector side has both an online and an offline system at play. When the inspector syncs the program, he downloads all of the data as available for all of his inspections and then becomes and offline mode that uploads data to the server in the background when internet is available.



    So my question is what is the best method for a BE to interface with a server? My initial though was to use SQL Server and an open port to allow the incoming/outgoing connections from the tablet but IT are concerned about opening a port. I am confident that the only security vulnerability would be if someone were to exploit the inspection management program. Is this correct or is there something that I am missing? What is a typical solution for a similar application?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    I don't consider myself a web application developer but I have built apps for use in a lab environment. I have done this recently because I understand Users' desire to access real-time data. I have always avoided the issue by playing the security card. One approach I have used in the past is to see if the business rules allowed for a simple append process as a solution. For instance, a user would be on the network and grab the latest data via a snapshot. The snapshot is held in a temp tables and this allows the user to "Go Offline". The user creates new records and appends these new records to the production DB when they "Go Online".

    So the first question for me is, "Do you need bidirectional synchronization?". If you do, I would suggest looking into SQL server as the back end. The user can still have an online/offline status, but SQL server can manage bidirectional synchronization using Timestamp and GUID columns. If you need bidirectional synchronization, you will likely need something other than Access as the FE. Also, you need to consider how "Real Time" the data needs to be. So you could have SQL server in the cloud to manage Point to Site connections of tablet users in the field. Then, a site to site connection to a virtual network could manage synchronization. In other words, your office would have a hybrid cloud solution where on premises hardware communicates with a virtual network via a VPN. In the virtual network a public facing endpoint would communicate with the tablets. This can get a complex as the security rules and business rules needs it to be.

  3. #3
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31
    Quote Originally Posted by ItsMe View Post
    I don't consider myself a web application developer but I have built apps for use in a lab environment. I have done this recently because I understand Users' desire to access real-time data. I have always avoided the issue by playing the security card. One approach I have used in the past is to see if the business rules allowed for a simple append process as a solution. For instance, a user would be on the network and grab the latest data via a snapshot. The snapshot is held in a temp tables and this allows the user to "Go Offline". The user creates new records and appends these new records to the production DB when they "Go Online".

    So the first question for me is, "Do you need bidirectional synchronization?". If you do, I would suggest looking into SQL server as the back end. The user can still have an online/offline status, but SQL server can manage bidirectional synchronization using Timestamp and GUID columns. If you need bidirectional synchronization, you will likely need something other than Access as the FE. Also, you need to consider how "Real Time" the data needs to be. So you could have SQL server in the cloud to manage Point to Site connections of tablet users in the field. Then, a site to site connection to a virtual network could manage synchronization. In other words, your office would have a hybrid cloud solution where on premises hardware communicates with a virtual network via a VPN. In the virtual network a public facing endpoint would communicate with the tablets. This can get a complex as the security rules and business rules needs it to be.
    The way that the system is currently designed is not using a website-interface but rather a single front-end that is installed directly on the computer but with two different interfaces. The desktop FE interface will connect directly to the server since it will only be installed on office computers. The tablet FE interface is installed on rugged tablets to be used in the field for performing inspections. These tablets need to be able to download the inspection data/requirements while a connection is available and mark it as "checked out" by the inspector so that no others can manipulate the data and be able to upload the data/images when internet is available again.

    Really I do not think the concept is very complex in the fact that I just need to know the best way to allow a secure connection to the SQL Server BE and allow Filestream to transfer images and PDFs to/from the office server. I am not familiar with creating remote accessible databases so essentially this is new to me.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,290
    What type of network are you dealing with? If there is any wifi involved, then other forums have strongly suggested to go with SQL Server (even Express) backend. WIFI/wireless network with any issue can result in corruption with Access backend.
    I haven't experienced this myself but I have seen the comment from people whose opinions/experiences I respect.
    Also when you say Enterprise-- it makes me think company wide/important/mission critical -- so I'd probably go sql server in any case.

    Good luck.

  5. #5
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31
    Quote Originally Posted by orange View Post
    What type of network are you dealing with? If there is any wifi involved, then other forums have strongly suggested to go with SQL Server (even Express) backend. WIFI/wireless network with any issue can result in corruption with Access backend.
    I haven't experienced this myself but I have seen the comment from people whose opinions/experiences I respect.
    Also when you say Enterprise-- it makes me think company wide/important/mission critical -- so I'd probably go sql server in any case.

    Good luck.
    The network is a typical secure office server system but I am not highly versed in network security so I am really not able to provide much more information than that. In talks with the IT people they think it would be best to use VPN to allow the tablets to connect to the SQL Server db remotely but I think there must be a better solution.
    The FE would likely use SQL Server and filestream for the backend and file transfer...but I do not understand how to create the connection to the DB and server without either opening a port or else using VPN. Am I missing something here?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    If the users are going offline after "Checking Out" specific records, then there really is not a problem. The user can synch when they connect the tablet to the network. If they need to synch while out in the field, IT will need to provide a public facing endpoint and a connection string.

  7. #7
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31
    Quote Originally Posted by ItsMe View Post
    If the users are going offline after "Checking Out" specific records, then there really is not a problem. The user can synch when they connect the tablet to the network. If they need to synch while out in the field, IT will need to provide a public facing endpoint and a connection string.
    The inspectors will need to sync over an internet connection that is not associated with the office network. Most of the inspectors go from one job to another for weeks at a time before ever stepping foot into the office/connecting to the network.

    How do I make the SQL Server db and Fileshare available over the internet (not office network) to the access front end?

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by sharkey_lsu View Post
    ...
    How do I make the SQL Server db and Fileshare available over the internet (not office network) to the access front end?
    You have IT create a public facing endpoint.

  9. #9
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31
    Quote Originally Posted by ItsMe View Post
    You have IT create a public facing endpoint.
    I apologize for my ignorance, but what is a public facing endpoint? Does this open up any security risks?

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    You have to open up your network to gain access from outside of the network. Yes, this creates security issues. I explained a couple of options in post #2. So, you could have a second SQL server in a virtual network and create a Site to Site connection between your on-premises SQL server and the one in the cloud. Then, you can have a public facing endpoint on the virtual network, allowing point to site connections. However, there are many factors to consider. Alternatively, you can open up your on-premises SQL server to the outside and have the clients log in directly to the on-premises SQL server.

  11. #11
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by sharkey_lsu View Post
    In talks with the IT people they think it would be best to use VPN to allow the tablets to connect to the SQL Server db remotely but I think there must be a better solution.
    SQL Server via VPN is your best option.

    Jeff

  12. #12
    sharkey_lsu is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    31
    So we have attempted to use VPN for access to the SQL Server DB but the throughput speed of the connection is very slow for some reason. Therefore the program is unusable slow which means I need to find another option for the connection...especially when it comes time to sync images which are much larger than the data being transferred.

    As far as opening a port goes, exactly what type of security loophole is created? I assume that the only way to do any damage would be to bypass all security measures of the frontend and go from there. Is this true or can the open port be accessed through other means?

    On a side note...it is bewildering that something that seems so simple could be this complex and difficult to get up and running. I am certain that there must be a better way.

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Access is great because of the tools provided that help developers create applications quickly. Sometimes, these tools introduce performance issues when they are not properly understood. For instance, binding a form to a table and then using a Macro or code to apply criteria to the Form's filter property is common. However, this creates a large recordset and a great demand on the network/resources. One solution here is to adjust a form's Recordsource using a WHERE clause.

    Regardless of the technology you use, you will need to consider how much data you send over the network and how many trips to the data you take.

    Ajax posted some links that are helpful.
    https://www.accessforums.net/access/...tml#post284345

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

Similar Threads

  1. Replies: 3
    Last Post: 12-04-2012, 10:36 AM
  2. Replies: 1
    Last Post: 12-03-2012, 05:47 PM
  3. Web Based Solution Reading Access Database
    By EddieN1 in forum Access
    Replies: 1
    Last Post: 08-06-2011, 04:38 PM
  4. Help for Database Solution
    By ritorocking in forum Access
    Replies: 2
    Last Post: 05-17-2011, 09:46 AM
  5. Corrupt Database: Possible solution?
    By asearle in forum Access
    Replies: 2
    Last Post: 07-14-2010, 01:59 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