Sharing an Access database

[glitch]

I need some help with a small business set-up that's worked okay until now but is in danger of causing some big problems for us as we continue to expand.

The business effectively 'runs' from an Access database that contains all the information for our customers, orders, etc. That database is held on a desktop PC in the office and shared on the local network to a laptop. Presently those are the only two PCs in the office but we're thinking of adding a third as we grow.

The problem we face lies with the fact that we've recently added an 'extra' person to helps us process orders that come in at the weekends and some of the evenings. They need to be able to access the database from a remote location and to 'solve' that we're currently using GoToMyPC to allow access to the desktop machine whilst it's not being used.

But that's sort of knackers us if someone needs access to the finance package which is also on the desktop PC and our extra person is 'using' it. What we need is a solution that will allow us to share the Access database locally as well as remotely but free up the desktop PC for someone to GoToMyPC into in case they need to get to anything on there during out of office hours.

Any bright ideas? I'm sure there's something simple but my brain is drained form trying to manage the day-to-day running of the business and doesn't have the spare capacity to think about anything else!

 

[glitch]

That sounds deceptively simple.

So we install a VPN client on the desktop machine (and the same on the external one) and the remote worker simply VPNs into the desktop and accesses the database? Do you have any recommendations for suitable clients?

And would a system like that work if three or more people (with one of those being remote) were accessing the database at the same time? And could the same VPN system work if one remote person wanted to access the database and another wanted to access either the database or the accounts package?

 

[glitch]

AFAIK the desktop O/Ss have a one connection limit with VPNs. If you have a proper server in the office (with a server OS) I would configure that for VPN access - this would support multiple connections, but the concept would remain the same.

I suppose the single-user limit makes sense. Unfortunately we don't have an in-house server we could use or we'd already be using it!

However, if you're looking to expand the business I would look for a better database than Access - from what I remember it's pretty iffy with concurrent connections, and I shudder to think what it would be like speed-wise over a VPN (remember you are limited by the upload/download speed of your internet connection).

The business is constantly expanding and at some point the current database will need to be migrated over to a better technology but when that happens is another matter.

Apart from anything else like cost or available time, I'd need to know with 100% certainty that the data would be perfectly accessible in another format. If we lost even one record it could have a massive knock-on effect for the rest of the business.

You could really do with a SQL solution rather than access. Converting from Access to SQL shouldn't be "too" difficult either. SQL is designed for multiple users, access isn't.

The phrase 'shouldn't be too difficult' fills me with fear! We rely on the current database to run the business and were anything to happen to that data we'd be completely screwed.

Can you give me some idea of how one might go about converting an Access database into an SQL database?

You could also look at something like Sharepoint maybe? I've never used it, and it might not even do what you need.

Well, it's worth looking into. I'd completely forgotten about Sharepoint.

Definitely, although I am biased

If your business cannot afford SQL Server, check out SQL Express. It should be more than sufficient for your needs.

And by being 'biased' does that mean you know a lot about SQL databases?

Can you explain how I'd go about converting my current Access database? And once converted, how do we continue to use it? Is there an SQL front-end of some sort or does that have to be designed specifically for the database in question?