Remotely accessible database

Hi All,

We are looking to set up a database (using Microsoft Access) that can be accessed by several other organisations of whom we have shared clients. It doesnt need to be anything flash as it is just for entering basic data, if there was some ability to attached files like PDF's etc. would be good but not essential.

The way I am planning to do it is just create an access database and host it on a dedicated computer and give everyone necessary access to the computer through RDP to enter/access data.

How does this sound to everyone? it is probably a pretty crude way to do it but can't think of a more effective way, anyone have some better suggestions?

Many Thanks
LVL 2
ezekuelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
If you can arrange for RDP access (port 3389 or a custom choice) through your firewall, this is a fine and proven solution, and it requires nothing to be installed at the client.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sybeCommented:
Download and install MS SQL Server Express edition. It is free.
http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

Then all users can link MS Access to that database.
http://www.ehow.com/how_6896909_use-access-sql-server-express.html
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't need to use SQL Server Express as suggested earlier. It's certainly a good choice, but it's not a requirement.

As gustav has said, using an RDP setup is the way many people do this. Assuming your IT system allows for remote access, and that you can set this up, it's the quickest and simplest way to handle something of this nature.

There's also www.eqldata.com. This is a commercial service with monthly fees, however.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sybeCommented:
The disadvantage of RDP seems to me that it gets messy when multiple users work simultaneously.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd need to setup RDP sessions for each user, which means you need an RDP server - which gets pricey :)

That said, RDP is still the quickest and simplest way to get your Access app to be "remote". Assuming your remote users can log onto their RDP session, they can use the app as normal.
0
ezekuelAuthor Commented:
Thanks all. I think I'll end up setting up terminal services and achieving it that way. In regards to MS SQL express what are the benefits of that over MS Access. Would that allow it to be accessed remotely without the need for RDP? or is it just a better database?
0
sybeCommented:
Yes. MS SQL Server Express  allows for remote access without RDP. You do not even need a dedicated server.

And it is also a better database. MS Access is basically a desktop application. Not really fit to be used by multiple users simultaneously. MS SQL Server is meant to be used by multiple users simultaneously.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
MS Access can be used by multiple users simultaneously. It's done every day by hundreds of thousands of users worldwide. It's not appropriate for every situation, however, and offsite usage is one of those situations.

Note that running a "bound" access application remotely is almost certain to cause performance issues, regardless of the database used to store the data. Unless you've designed the database to be used offsite, it's rare that you'll be satisfied with the results. Moving to SQL Server (or Oracle, or MySQL) is not a "magic bullet" that will solve all of your troubles. If you do decide to move to SQL Server, you may very well have some changes to make.

See these articles for more information on those changes:

http://www.fmsinc.com/tpapers/index.html#SQLPapers
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp (Best of Both Worlds)
0
ezekuelAuthor Commented:
Thanks again. one last question. If I install SQL server express 2012 on my server and configure it for remote access how will users actually connect? if it doesn't use RDP what process will people use to remotely access the database? like will the use a web browser?
0
sybeCommented:
From http://en.wikipedia.org/wiki/Microsoft_Access :

If using an Access database solution in a multi-user scenario, the application should be "split". This means that the tables are in one file called the back end (typically stored on a shared network folder) and the application components (forms, reports, queries, code, macros, linked tables) are in another file called the front end. The linked tables in the front end point to the back end file. Each user of the Access application would then receive his or her own copy of the front end file.

Another things is that if there is a single MS Access application installed, then the first user will lock the database, and others can not open it. Back-end and front-end must be split. That means you can not simply have a dedicated server with RDP for difrferent users. Each user must have it's own 'front-end' installed.

http://www.pcreview.co.uk/forums/multiple-users-simultaneous-access-ms-access-database-t4025308.html

For users it is a lot simpler to have their MS Access application installed on their own machine, which links then to  the remote database. There is no need to open RDP and login. Just open an MS Access adp file on your own machine, and there you go.
0
sybeCommented:
> what process will people use to remotely access the database?

They can use MS Access as a front-end which then connects to the SQL Server and handles the tables as if they are in MS Access itself.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To be clear: SQL Server is a database engine only. It does not house the forms, reports, macros, etc that your users run to interact with the data. You'd maintain those forms, reports, modules, macros, etc in the "frontend file", as described in the Wikipedia article above.

Each user should have their own copy of the FE installed on their local machine, and all of those FE databases should be linked to the same backend database.

As mentioned above, you must configure SQL Server for remote connections. This MSDN blog gives you the high points of that:

http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Once you do that, you would then link a single FE to that SQL Server database. This can be done in a variety of ways, and is dependent on exactly how you've setup the server for remote use. If you're assigning an IP address to the server, for example, then you can connect using this type of connection string:

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;
Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

If you're using a VPN, then you might connect with this type of connection string:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Note: Connect strings from here: http://www.connectionstrings.com/sql-server/

Once you've established the connection, you'd then make copies of that database and deploy those to your users. If you make design changes to the FE (add a form, report, etc), then you'll need to redeploy new copies of the FE.

Regarding the mention of "adp file" - MSFT no longer supports the ADP format, although it will still work in most versions of Access. However, there have been increasing reports of troubles with ADP files in the newer versions of Access. An ADP file is not a "linked table" setup - it's a specific type of Access database that has a direct connection to a SQL Server. Creating an ADP file is not the same as creating a "Frontend-Backend", as described in the Wikipedia article above. It's an entirely different beast, and I'd suggest you stay away from it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.