Sharing an Access DB via SQL Express 2014

Question

I work for a ecological research station and we're trying to move 35 years of data out of Excel and into Access. Most of it's in there now, and so we're starting to think about the coming field season and letting a bunch of research techs enter data into our precious databases.

Part of the reason for making this move was (obviously) to reduce the number of errors in the data that resulted from typos or incorrect codes being entered. We're using forms & validation rules to do this. We don't want the research techs who will be entering data to have to know anything about coding or Access or SQL, so forms and pre-designed queries are pretty perfect for this.

Now, we want everyone on the crew to be able to enter data into a single db, look at the data and pull it out. We've looked into splitting the DB into a front and back end and hosting it on a shared network, but then no one can access it if they're not in the office (and people routinely work from home or enter data on the weekend).

I found out about SQL Server Express 2014 and have downloaded it. Experimenting with a copy of a DB, I've gotten the tables on there using the Upsizing Wizard and created several user logins with different permissions. All my forms & queries still work in the original Access file, the relationships and validaiton rules are intact (though the relationships are no longer visible on the relationships window in Access). I'm thinking I could share the Access file (which seems to be a "front end" now) and give people logins to the SQL server so they can enter data via the forms from multiple locations and have it all go to a central, shared file.

This seems to be what we want, as long as there's a user friendly way for others to log onto this server & access it. But this is where I'm getting confused. All the documentation I've been finding is written with developers in mind, so I don't even really know which terms to be searching for. And I'm not sure which version of SQL Server Express we'd want to distribute to our crew members. Between versions with tools, without, "lightweight versions" and the management studio, I'm not sure which one they could install quickly and then just ignore & log in through Access, if there is one.

Also, I don't quite understand if the DB tables are being stored on my computer currently or in the Cloud. I'm pretty sure it's my computer, as I'm hosting the server I set up, but is there some way to change that?

Thank you for the help. I realize these are basic questions, but I've spent about 4 hours reading microsoft's "lessons" and clearly don't have the vocabulary to keep up yet.

Answer 1

Well, the issue or problem is where to host that instance of SQL server. You could consider SQL Azure (cloud based). The only downside is a typical internet connection is about 100 times slower than your office network. Because of this issue, often significant amounts of work is required to obtain good performance.

So the first question is “where” you plan to place and run that instance of SQL server. You could place this instance on say your existing server, but then you have to open up that server to allow external connections. (not for the faint of heart - you IT department will likely setup a VPN). And you have to then issue and allow users to connect to your office network. So this issue has to be done correctly, as the internet is a VERY nasty place!

Often, even a better solution is to consider a server running remote desktop. Remote desktop is a great choice since you don’t have to do much of anything in the way of re-writing your software (performance wise). And another bonus is users don’t need to install Access on their computers. Another choice would be to create a web based application in Access. (but keep in mind they are quite different from desktop, and no VBA code).

I explain the issue of performance and choices here:

http://www.kallal.ca/Wan/Wans.html