Question MS Access 2007 - Sharing a database across multiple international users

[markdalester]

Hi All,

New to the forum but couldn't find a previous question on this after searching the site.

I have created a database for the first time and created the relevant relationships and forms to enter the data. I now want to share the database with multiple users located in different countries.

To do this I have split the database - providing front-end forms/queries and back-end data. I am trying to work out how to centralize the data so everyone will edit the same database. According to Microsoft site there are three ways of doing this a Network Folder, Sharepoint Site, Database server.

I am new to this and being a small company we haven't got a VPN/WAN (as it would need to be an international network folder), Share point site or database server currently. I am happy to invest in one of these three but I just wanted some advice on what would be the best way to go about it (not particularly too concerned about a high security level just ease of use and collaboration).

Many thanks for your help

Mark

 

[Simon_MT]

I would consider a Terminal Server. Purely on the basis that you have the VPN in place and there is mininal modification in a FE/BE environment. The added advantage is the emails and documents etc can also be shared as well.

Yes there is a hardware cost but there is a cost somewhere in the equation.

Simon

 

[markdalester]

Thanks for your quick reply Simon - really appreciate it.

Sorry I might not have made it clear in the original post but I haven't actually got a VPN in place. Would you recommend setting one and then continuing as detailed in your reply in light of not having a VPN?

 

[Galaxiom]

The other alternative is so build a web application.

In this arrangement the forms are served up to the client in a browser. The data is submitted to the server and the server interacts with the database. The beauty of this approach is that the client only needs a browser and a password.

Unfortunately the database front end needs to be constructed as a web application.

 

[markdalester]

Thanks GalaxiomAtHome,

I have recently considering that as option as a way to share the database.

I need to find out the cost implications though. We have a relatively powerful desktop in the office that isn't assigned to anyone would this be sufficient do you know to run a SQL server or would it require more significant infrastructure and the purchase of a server/cloud server?

Thanks again

Mark

 

[Simon_MT]

You should consider a server. The only thing that I undestand that is not advisable is running outlook with Exchange Server.

You do not need to do anything to the database other that creating individual front ends for each user.

Simon

 

[markdalester]

Thanks for the advice Simon, After considering what both you and GalaxiomAtHome have suggested i think the server route might be the way to go.

Although creating a web application for the database sounds the best way in terms of ensuring everyone is using the most up to date forms, queries etc due to time constraints on this project i think Simon's idea of holding the data on the server and creating individual front ends sounds the best solution to my problem at present

 

[Galaxiom]

Simon is in a small minority in suggesting using Access directly across the Internet.

The problem is latency and lost packets. Access doesn't like the connection between backend and frontend being interrupted in the middle of executing a query and this can result in data corruption.

Most developers recommmend the use of Terminal Services if using Access across slow connections. This runs the front end on the server so maintains the connection. Only the keystrokes and mouse movements are transmitted to the server and screen updates returned.

 

[Jason1971]

If you are prepared to upgrade to Access 2010 you can do web databases and have them hosted on the internet by www.accesshosting.com (saves setting up your own server $$). Try a free trial of Access 2010 (Office 2010) and a 30 day free trial of AccessHosting.com to see if it's what your after.
Note: Split databases probably will not work in Access 2010 WEB database. Plus table Relationships are done a little differently in Access 2010 Web databases (lookup fields used instead) but you get the same result. So you will need to rebuild your database in Access 2010.
Best of luck.

 

[AJordan]

You can purchase a cheap (but stable) Computer and use it to host a mySQL database. Convert your back end to mySQL and keep Access as your front end. Other than the cost of the host PC and possibly a static IP address if you think you need it, it's very cost effective. Somewhere in this forum is a thread about remote connecting with mySQL that I used to help me with my a similar project like yours.

Overall it was not that difficult to do, and I had no experience with mySQL or ODBC etc. I read a few white sheets over at the mySQL website and asked some questions in the mySQL forum on this site.

Edit--
Here is the thread
http://www.access-programmers.co.uk/forums/showthread.php?t=114491

 

[Simon_MT]

I don't mind being the minority but having written both an ASP and ASP.Net applications the Terminal Server solution was a doodle. What you also can do is to distribure Outlook and any documents at the same time. Printer's dirvers have to be loaded onto the Terminal Server and each client needs to use use there own printer.

All you are doing is propagating screen dumps over the web and you have the benefit of local calls to the database. Upspeed may need to be increased if images are used extensively.

Yes you can use SQL Server or MySql but why change something that is not broken just to yeild it to international users. Each user will need a separate FE and the BE needs to be on a server of some description.

Simon

 

[Galaxiom]

I don't mind being the minority but having written both an ASP and ASP.Net applications the Terminal Server solution was a doodle.

Ah. My misunderstanding. I was responding to your last post which simply refered to "a server" without having to make any changes. I hadn't connected it to the earlier post advising Terminal Services.

It looked like you were suggesting a simple backend on a server connected via Internet.

Note that there are more than just hardware costs. TS licencing costs add up quickly. At least with Access the runtime can be used.

 

[darbid]

Hi markdalester,

I am relatively new to all this, and am not a programmer. I built some great forms in access and then realised that Access alone eg Front End / Back End does not really work that well for international people.

In fact I tried it and the BE was currupt immediately.

We used an existing SQLServer 2000 (the dark ages still) - it was relatively painless to get the data from Access to SQL server. I believe there is a free version of SQL server that you can use and you can use it on your existing desktop. I have a laptop with it running and it is old. You only need to put certain ports of the desktop into your router so that incoming requests are directed to the right computer.

It was also relatively painless to get the Front End to conect up to SQLServer. In the initial stages you can link tables from your front end to your SQL Server back end and when you do this you will not see many differences to an all access solution. (technically there are many I know - and experts will disagree but I am just making things a little simpler)

Once you get on your feet you will realise that linked tables work well if you are close to the SQL Server, but people on the otherside of the world will have problems and thus you will get smarter with your data transfers and design your FE to only request the information that is needed.

I have a simple .exe which sits on all desktops. When started it compares the current .mde to an mde stored remotely. If the remote one is newer it replaces the local one. if not or after replacing it, the .exe simply calls this .mde and the user is into the database.

I hope this helps.

 

[Simon_MT]

The trick with a BackEnd on a Terminal Server is the Session Timeout. A generous TimeOut allows reconnection should the connection go down. I have had only one instance, in three years with corruption and that was because a user let a database screen in edit mode for over 12 hours.

If I recall correctly a 5 user pack on the Terminal Server was under £100.

Simon