New member, learning Access vs OneDrive

[bobsy852]

Hi all!

I'm here to learn about Access and specifically ways in which I can make two existing Access datatbases which have relationships with eachother work on a new OneDirve system that our company has adopted.

I might be better posting this in another section, but I'll write it out here first:
Basically, I work for a small company 4-5 staff.
The company uses Access for timesheets, staff disbursements, contract administration etc. and was all set up by a previous director back in the early 2000s.
Staff use the forms to input data, reports to produce contract documents and admin staff use queries for determining expenses etc.

Nobody remaining understands how it really works in the background and so I'm taking it on myself to firstly try and get my head around that.


The company recently migrated their data onto a OneDrive system and were told by their IT technician that the Access database wouldn't work on this.
My Google searches seem to suggest this is correct as there is the potential for 2 staff to use the database at the same time creating clashes.

So the IT team put the database on a small office based server which staff have accessed via a VPN to use. However, the VPN dropped out while someone was using it and it's created errors with VBA Modules now. I'm still looking into what that means but when they asked the IT team they said that is the risk of accessing it via a VPN.

So I'm looking at long term alternatives which we can all access remotely.
The current Access database works well but seems to be getting outdated by the cloud based home-working methods introduced over the last year. This is something we'll be continuing with so I'm trying to figure out the solution to this.

I had a look at SQL servers but that looks like an expensive solution for such a small company and a steep learning curve for me to understand how to set up?

Is there anything else you could recommend?

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[theDBguy]

Hi. Welcome to AWF!

 

[The_Doc_Man]

First, welcome to AWF. Always happy to see new blood.

Second, your question is long enough that it shouldn't be in the Introduction section so... I'll move it to General. Don't worry, no penalty involved here. It's a common thing for moderators to second-guess where something goes.

Third, the problems with COVID and working from home have been industry-shaking. Your research is correct. Access does not work with OneDrive because OneDrive doesn't reasonably support SMB (Server Message Block) protocols or any other "normal" Windows File Sharing applications. Without SMB, Access does not work.

Using a server with a VPN isn't the problem. VPNs don't drop that often. BUT WAN-based networks DO. That is most likely what happened. If you have a network that depends on wide-area connections (which ARE likely to drop occasionally), you can expect issues. Back-end Access databases WILL get corrupted in such configurations.

Sadly, if you MUST have the remote connectivity, you are looking at one of two paths.

A. Develop your app entirely using web-based protocols and tools, because web protocols maintain connections differently than SMB. They are designed for "ragged" connections. Sadly, this does not include Access.

B. Consider CITRIX or RDP connectivity on that server you set up. You will still have issues if the connection breaks but they will be less severe. Search this forum for information on RDP and CITRIX as two (related) methods to handle this kind of network layout.

For the record, cloud-based STILL doesn't do what Access does. A cloud-based back-end will still have protocol issues that must be addressed. The good news is that COVID response will REQUIRE some new technology to eventually assist in fixing this problem. The bad news is, we ain't there yet.