Access Database via remote VPN Access

Hello,

Like many other probably are right now, some colleagues and I are working remotely from home, and accessing our network resources remotely via a secure VPN.

However, accessing anything that uses a Microsoft Access Database, for example, the E3 database(s), from a central shared network location is unbearably slow and in some cases not even functional.

Is there a suggested way to make a shared database more functional for remote use?  Our database has shared edit rights and various people can make updates that need to be accessible to all people using E3. 

Yes I could limit it to only one person to do database work and distribute database updates, but that will bog down the database editor and slow down the other users that are capable of making the edits they need to. So downloading a copy of the database to local machines, while it would be quick, appears impractical, and bandwidth to our company network is not the fastest, only 100mbps up and down among the entire company to use.

Is the best solution trying to convert the access database to a SQL database that could handle the queries much better? Is there some documentation help on doing that conversion?  Any drawbacks from converting the access database to a SQL database?

Any other suggestions?

0

Comments

10 comments
  • Hi Michael, 

    The discussion about available bandwidth, performance and the overall efficacy of access databases is not new and we can likely expect multiple viewpoints and a lot of back and forth. However, there are somethings you can look at already that we know to be true.

    1. SQL does work better than Access when multiple people are accessing the database. 
    2. SQL is overall more efficient when it comes to larger databases. (smaller local databases in access seem to be more efficient since there is no overhead).
    3. VPN + Remote Deskop will likely work better : If you can set-up Remote Desktop through VPN you will likely have better performance over the same bandwidth in this case (since the transactional data is mostly display related and not files and queries.

    E3.series conversion of database

    Drawbacks

    • There is IT overhead to using an SQL database. It will need to be configured and maintained by the IT team. 
    • The backups need to be scheduled and won't be as easy and making a copy of the access file. 

     

    Having said all that, 

    • Are you guys making changes to the database so often as to make the local database option cumbersome?
    • Considering the overhead vs a workflow implementation would be a good exercise
      • Can you team take turns to make changes?
      • Can you update the Database once a day on the local drives and would that not suffice?
    • If this is a database creation time for a project – then
      • can the database creation team not work on their own databases;
      • save the new symbols, components and model in a file ?
        • File à Save As in DBE mode à Share the file
      • One person then checks and saves all the data into the shared DB?
      • Can you E3.CopyDatabaseEntriesTool as well to copy just selected parts from one database into another.
    0
    Comment actions Permalink
  • Here's a video of configuring SQL in E3.series - I haven't watched it but should give you a rough idea. 

    https://community.zukenusa.com/hc/en-us/articles/360007078834-Configuring-a-Read-Only-Library-in-SQL-Server

     

    0
    Comment actions Permalink
  • Hi Sanu,

    We do make pretty frequent updates to our database, whether it is new components / symbols being added for new product, fixing attributes, fixing symbols, etc, but I can't say that we can't make a local database work.

    We do also have some SQL databases in use for other applications, so our IT is ready for a SQL database.

    Guess I will have to chat with my supervisor and IT team on what would be the best method for the next few months.

    0
    Comment actions Permalink
  • Also there are some drawbacks to using remote desktop, such as multi-screen support, 3D mouse support, etc.

    0
    Comment actions Permalink
  • Follow up to using a local copy of the database from the server.

     

    What Happens when you pull stuff from the local database, but then go back to the main database?  Does editing a part in the main database update the parts in the project when you run an update?  Does it create a discrepancy in database links to parts if not managed correctly?

    0
    Comment actions Permalink
  • While I think local copies would be your best solution, managing updates between a main shared database and a local copies with multiple people potentielly making changes can get real messy real fast.

    Maybe have one person collect all the daily changes, having everyone send them the e3s file of their DBE change, and have that one person update the master copy on the network share.  The users can pull down a new copy daily perhaps.

    0
    Comment actions Permalink
  • So as long as only the master copies of the configuration, components, and symbols databases are edited and subsequently copy all three to local machines, with local machines mapped to the local copies, we shouldn't run into any issues eventually changing back to mapping to the master in the future?

    0
    Comment actions Permalink
  • Why not combine all three - components, symbols and configuration -  into a single file?  That, you only need to copy one file instead of three.

    0
    Comment actions Permalink
  • Yes, Michael if you have the right mapping there shouldn't be any major issues with mapping back to master. Provided you have the same structure. 

     

    For your question: What Happens when you pull stuff from the local database, but then go back to the main database?  Does editing a part in the main database update the parts in the project when you run an update?  Does it create a discrepancy in database links to parts if not managed correctly?

    If I understand your question correctly ( please feel free to rephrase if the answer does not make sense)

    If you switch databases (I would not suggest switching them very often) and run update configuration, symbols or components. The current database is used to update the items. I am not entirely sure of the top of my head if it used the last edited timestamp or a combination of other items. 

    That is; When you switch to a new database and click update it will update the items you select or all the components with the latest from that library. It is not comparing what you did in the previous library. 

    So, as long as you have the process of updating your library (the one source of the truth) then updating the project should be straight forward. 

     

    0
    Comment actions Permalink
  • Although Subversion is not thought for that (large binary files), what about using Subversion (TortoiseSVN) to have a control version of the DB and an easy way to share the files ?

    Have anyone some experience on it ?

     

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post