Go Back   Access World Forums > Microsoft Access Discussion > Access Web

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2018, 06:55 AM   #1
killerflappy
Newly Registered User
 
Join Date: Aug 2017
Posts: 34
Thanks: 8
Thanked 0 Times in 0 Posts
killerflappy is on a distinguished road
Remote connection to access database on Synology NAS

Hi guys,

A couple of months ago I started to program an Access database to process orders, scanning and drivers papers. This database sits on a Synology NAS. For the warehouse it's a good solution.

The last months I programmed a lot of VBA code for all kind of things. The database will grow to about 50mb. Every day I copy (via WebDAV) the database to the main office for analysis.

The next step is to use the database from the office without copying every day. There are a lot of options, but I don't know the right one.

Opening the database directly via WebDAV is way to slow.

I tested with a split database. A copy of the front and in the main office en the backend on the Synology NAS. TYhis also is way to slow for some functions.

Because I programmed a lot in VBA, the Access web app isn't an option.

What other options do I have to use the database remotely?

killerflappy is offline   Reply With Quote
Old 01-12-2018, 07:45 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,862
Thanks: 0
Thanked 623 Times in 608 Posts
Ranman256 will become famous soon enough
Re: Remote connection to access database on Synology NAS

can you not improve the network connection?
we all have the frontend in various places with the BE tables residing on server.

This works great for everyone even tho SOME people have slower PC and the connection too seems slow for them.
Ranman256 is offline   Reply With Quote
Old 01-12-2018, 10:57 PM   #3
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,601
Thanks: 41
Thanked 1,020 Times in 930 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Remote connection to access database on Synology NAS

The main question is whether the connection to your NAS is reliable or subject to frequent drop-outs. If you have a slow but reliable NAS connection, it will be ok if a big sluggish to watch. If it is lightning fast but equally as capricious as lightning, you will corrupt the crud out of the shared file.

At our office, the connections to most of our storage were based on fibre-channel running anywhere from 1 Gbit to 8 Gbit depending which segment and which NAS server you were using - and with the U.S. Navy, we were approaching a Petabyte in total of NAS when I retired. Depending on the FC adapters we could get, we could take advantage of some pretty bangin' channels. The 8 Gbit channel was just about as fast as a physically connected drive - maybe more, depending on file transfer sizes. We ran our BE files on an NAS drive that gave us (I think) about 2 Gbit service rate. It was easily reliable enough most of the time.

However, before the project was in full swing, our test bed - also using NAS drives, was much slower, not based on FC technology, and therefore subject to network drop-outs. We had to make frequent backups to assure that the DB would remain available. There were times when corruption crept in, doing its dirty deeds on us.

So NAS isn't an issue. The connection method is THE big issue.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 01-17-2018, 05:27 AM   #4
killerflappy
Newly Registered User
 
Join Date: Aug 2017
Posts: 34
Thanks: 8
Thanked 0 Times in 0 Posts
killerflappy is on a distinguished road
Re: Remote connection to access database on Synology NAS

Thanks for the tip about upgrading the internet connections. The 1Mbit/s upload is being upgraded to 8 Mbit/s. Thats the maximum.

For future use, it won't be enough. Maybe there is a way to virtualize Access, so it's starting Access on the Synology and only send the keyboard, video and mouse actions. Anyone know about this? I see the Docker app for synology, but i'm not sure if this is for app virutalization.
killerflappy is offline   Reply With Quote
Old 01-17-2018, 06:32 AM   #5
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,601
Thanks: 41
Thanked 1,020 Times in 930 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Remote connection to access database on Synology NAS

Based on your post #4, you are going to have to look at a Terminal Services solution, and some of our members have found that Citrix works for what Access needs.

Use the forum's Search feature looking for CITRIX and TERMINAL SERVICES as two possible search topics. You will find articles that may be helpful to various degrees.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 01-17-2018, 07:15 PM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,098
Thanks: 13
Thanked 204 Times in 202 Posts
Mark_ will become famous soon enough
Re: Remote connection to access database on Synology NAS

Quote:
Originally Posted by killerflappy View Post
Hi guys,

A couple of months ago I started to program an Access database to process orders, scanning and drivers papers. This database sits on a Synology NAS. For the warehouse it's a good solution.

The last months I programmed a lot of VBA code for all kind of things. The database will grow to about 50mb. Every day I copy (via WebDAV) the database to the main office for analysis.

The next step is to use the database from the office without copying every day. There are a lot of options, but I don't know the right one.

Opening the database directly via WebDAV is way to slow.

I tested with a split database. A copy of the front and in the main office en the backend on the Synology NAS. TYhis also is way to slow for some functions.

Because I programmed a lot in VBA, the Access web app isn't an option.

What other options do I have to use the database remotely?
Is the code you wrote mostly for front end or back end consumption? i.e. is most of it "Functions to translate data so the user sees something they want" OR is it mostly "When they do X in data, changes Y, Z, and 1 to 17 also happen"?

If the former then looking into how you are requesting data and limiting to only what you need may be your best option. If the later, is getting a true SQL server that has stored procedures an option?
Mark_ is offline   Reply With Quote
Old 01-18-2018, 02:36 AM   #7
killerflappy
Newly Registered User
 
Join Date: Aug 2017
Posts: 34
Thanks: 8
Thanked 0 Times in 0 Posts
killerflappy is on a distinguished road
Re: Remote connection to access database on Synology NAS

Quote:
Originally Posted by Mark_ View Post
Is the code you wrote mostly for front end or back end consumption? i.e. is most of it "Functions to translate data so the user sees something they want" OR is it mostly "When they do X in data, changes Y, Z, and 1 to 17 also happen"?

If the former then looking into how you are requesting data and limiting to only what you need may be your best option. If the later, is getting a true SQL server that has stored procedures an option?
Hi Mark, Its for the front and backend. Remotely we want to see history, load routes and other information.

I'm not that familiar with SQL server and stored procedures. Can I compare VBA code with stored procedures?


killerflappy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Data From Remote Access Database to a Local Access Database twcaddell Modules & VBA 3 01-26-2015 06:02 PM
Security on ODBC connection to remote MySql database latex88 General 0 04-25-2013 06:15 AM
Advice. Remote Connection to Database Len Boorman General 1 05-14-2009 06:56 AM
Remote Deskptop connection to Access DB on Server Curious General 5 04-12-2007 04:00 PM
[SOLVED] Remote Connection EricDiehl SQL Server 0 06-15-2006 09:56 AM




All times are GMT -8. The time now is 09:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World