ezekuel
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
The disadvantage of RDP seems to me that it gets messy when multiple users work simultaneously.
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.
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.
ASKER
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?
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.
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.
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)
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)
ASKER
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?
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.
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.
> 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.
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.
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,143
Initial Catalog=myDataBase;User ID=myUsername;Password=myP
If you're using a VPN, then you might connect with this type of connection string:
Server=myServerAddress;Dat
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.
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,143
Initial Catalog=myDataBase;User ID=myUsername;Password=myP
If you're using a VPN, then you might connect with this type of connection string:
Server=myServerAddress;Dat
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.
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