Remote connection to access database on Synology NAS

[killerflappy]

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?

 

[Ranman256]

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.

 

[The_Doc_Man]

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.

 

[killerflappy]

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.

 

[The_Doc_Man]

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.

 

[Mark_]

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?

 

[killerflappy]

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?