What is the easiest way to edit an Access database online?

Problem:

In our office we have an Access customer database that is about 20MB big. People mostly use it from within the office's LAN, but some other team members are external and they currently have the following workflow:

Ask somebody in the office to upload the file to an FTP server
Tell others to stop making changes in the database
Download the database, commit changes
Upload it to the FTP server and tell the office that it's usable again

Question:

What is the easiest way to allow those people to make changes to that database without needing to resort to the methods above?

There should be no need for real "live" and collaborative editing, but the process should be fairly easy to learn for "normal" PC users and involve as little effort as possible for me on the developer/sysadmin side.

microsoft-access

slhck

Posted 2011-05-26T11:20:14.360

Reputation: 182 472

Answers

The simplest solution, although not necessarily the cheapest, is to allow your remote users access via Terminal Server/RDP. Now if you only have one user at a time using the app then you could easily dedicate a PC to this task and open a port on your firewall and route the traffic to that PC.

Also note that you should consider splitting your database into a front end and back end and allow multiple users to work with data at the same time on your local network. See See the Splitting your app into a front end and back end Tips page.

Tony Toews

Posted 2011-05-26T11:20:14.360

Reputation: 424

+1, I never thought about the first solution, but it seems like the best fallback if there are no resources available to migrate the DB or buy/setup Sharepoint. – slhck – 2011-05-27T08:27:28.517

Note that Sharepoint is relatively expensive. – Tony Toews – 2011-06-10T03:48:11.407

Yeah, that's my main concern, although I'm not the one with the budget, only the guy who has to implement it. Actually I just convinced them just to go for the RDP solution. – slhck – 2011-06-10T10:49:57.790

Consider a switch from using Access to using MS SQL. Access (2007 at least) has Wizards to help you convert pretty easily.

SQL will give you the availability and multi-user capabilities you seem to require in your DB.

Ƭᴇcʜιᴇ007

Posted 2011-05-26T11:20:14.360

Reputation: 103 763

There is a tool from the SQL Server group which is better than the included SQL Server Upsizing Wizard. SQL Server Migration Assistant for Access (SSMA Access) http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx However upsizing may require significant amounts of developer time in getting things going. As well as SQL Server to be installed on a server. Note though that SQL Server Express is free.

– Tony Toews – 2011-05-27T05:31:48.873

1You should also change your answer to state that the poster should switch from using Access to store the data. And keep Access as the front end. – Tony Toews – 2011-05-27T05:35:14.547

@techie007 @Tony So basically I'd have to migrate using one of the Wizards, then have one centralized SQL database and still use Access as front end? I have never ever used Access, but would that allow me to remotely connect to this database? – slhck – 2011-05-27T08:31:21.337

@slhck - Having never done an Access front-end, as-in how Tony has pointed out, I can't say for sure on Access' end, but as long as you open the port in your firewall and configure SQL server to communicate and accept connection via TCP/IP you can easily make an SQL server available via the Internet. I can't think of any reason why Access would be prevented from using the Internet to connect, hopefully without much user intervention. Warning: You'd be safer locking it inside the LAN and having remote users connect via VPN before accessing the SQL via Access. – Ƭᴇcʜιᴇ007 – 2011-05-27T13:17:26.320

1@techie007 That figures, I was only thinking there might be some restrictions on the Access side. I'll take everything into consideration. Thanks for the answer! – slhck – 2011-05-27T13:21:21.893

techie007 is correct. This works quite well. Indeed I know someone who was using a well written Access app against a SQL Server connection via 56 kpbs modem. He stated it was sluggish but it did work. Also a plug for a commercial software app I'm working on. In a few months I plan on adding the capability to the Auto FE Updater to allow developers to update the Access FEs via web servers. – Tony Toews – 2011-06-10T03:50:36.257

If you're interested, I added a follow up question: How can I easily migrate an Access database to MySQL?

– slhck – 2011-07-21T12:14:00.943

You could try a service that we offer- EQL Access. We enable you to either synchronise changes over the web to multiple, local versions of Access (GeoSync) or you can use OnWeb which lets users interact with Access via a web browser. It means no change to the way people use/develop Access and you can schedule syncs if you want it to occur on a regular basis.

I know I am (very) late to the party with an answer here but this is exactly what we designed the service to do.

anthon

Posted 2011-05-26T11:20:14.360

Reputation: 121