Hello,

I have a query regarding database structure in a large WAN. Apologies if I'm not posting this in the correct place but I figured that this would fall under the general Q & A section. I've tried searchng the forums and found a couple of bits and pieces regarding my issue but no real solutions were listed that would fit what I need given the fact that I have many constraints in my current network environment. Basically, my problem is this:

Our (Government) organisation has 13 regional offices across England. I have developed a database which is the same in each region. This works well given that the front end and the backend are located in the same office. However, it has now got to the point where data stored in each backend needs to be shared across the regions by the different management teams. As mentioned previously, we have a number of restrictions on the way that I have to deploy databases because of our IT Service Provider.

The working of the database is fairly simple. I have two tables in the backend, one with approximately 10 fields which a user is required to add to using their frontend interface, the other which contains a list of names which is used in a combo box in a frontend form. All validation of fields is carried out at form level. This database is therefore just used as a collection tool so that a number of users can simultaneously add data. Managers can then use the data for their purposes. At any one time there is likely to be a maximum of 10 users in each region accessing the database.

The solutions that I have thought of so far are as follows:
1. Locate one Access backend in a geographically central place and have all frontends link to it
2. Upsize to SQL
3. Stick with the current solution of locally stored databases and devise a tool to collect all the data when required
4. Use ASP.NET or similar

I know from looking at the many posts on the internet that running a database across a WAN is a known problem and not recommended and that upsizing to SQL is the preferred option. However, in our current environment, this is not a option as my organisation will not allow for the use of SQL Backends or development in ASP.NET. I'm therefore a bit stumped as to what the best solution may be. I figured that having a backend in a central location may be my only option and had hoped that because there is only a small amout of data travelling across the WAN that the performance issue may be something that we can cope with.

It should also be noted that because our provider will not allow access to teh C:\Drive of the local machine, users are having to run the frontend from their Home Directory which is stored on the local server.

Hopefully I've explained my problem in enough depth and hopefully the more experienced developers may have a solution that could help? I'm pretty sure there will be some stones that I've left unturned but I just haven't been able to find those yet.

Thanks in advance.

Andy

Does your organization have SharePoint, if so you could publish your database to SharePoint and everyone could work with it in that manner.

I agree that Sharepoint is a good tool for this, though there may be a little bit of learning curve (less than that of SQL Server, though, I'd think).

Another alternative may be Jet Replication. However, this is supported only in MDB file format, not Accdb (2007 can handle MDBs though, even though it's default is accdb, provided you don't use multi-value fields or the like).

Jet Replication used to be an excellent answer to these types of solutions, and I, for one, wish they hadn't done away with it in the accdb format. That's neither here nor there, though - these days the answer is clouds (sharepoint). If you're interested in this approach, do a google search for "David Fenton Jet Replication Wiki" and you'll find a wealth of knowledge on the subject.

Depending on the nature of the data, a custom "collection tool" may not be out of the question and may have little impact on the current setup of your BEs. The major concern when trying to coordinate data in two separate locations is the possibility of duplicate records, and how to handle it if person at location A edits a record, and person at location B edits the same records: how to reconcile that? This is where Jet Replication does excellent work, but if you're strictly talking data entry at both locations, then this may not be an issue. A reconciliation routine may consists of having the dbs emailed, then running a couple INSERT queries to put them in a master.

Just some food for thought.

Cheers,

Hi Guys,

Thanks so much for taking the time to provide some advice on my problem.

Daniel, our oranisation does not have Sharepoint, which I agree, would have been a good solution. I had thought of it (apologies, I should've pointed that out in my post) but you couldn't even begin to imagine the constraints that are applied on our network!

Jack, I think your suggestion of Jet Replication is definitely something I will now be exploring in more depth. I've generally always published my databases in MDB format (because we only upgraded from 2000 to 2007 two years ago) so hopefully this will be the best solution. I think I've got some reading ahead of me but the site you directed me to looks as though it will give the guidance to be able to implement such a solution. With regards to your comment of co-ordinating data in two locations, I can confirm that the tool would be exclusively used as a data entry tool and there wouldn't be any reason for a standard user to have to amend a record. I figured that this may still be an option given that each time a record is submitted, it is only a reletively small amount of data being submitted to the backend.

Thanks once again for your input. I'll keep you posted on my progress.

Andy

Just one last thing to keep in mind regarding a custom reconcilation solution: even with data entry only (no amending), each "set" will have their own Unique IDs (presumably), which may conflict when pulling them into a master. This may or may not be problematic, depending on how you'd approach it.

Again, this is something that Jet Replication handles quite well.

Good luck,

Thanks again for the advice Jack. I'll definitely bear that in mind when looking at Jet Replication but having explored the guidance and the potential pitfalls, I think that this may be something that I may look at in the future.

The fact that I need to implement something fairly quickly (given that management want something yesterday) will lead me to try the option of chancing the backend on a remote server and see if there's any performance issues that I may need to address using your suggestions.

Thanks again.

Andy

A bit out of my area of expertise here, but if you do feel you might need to risk accessing over a WAN, the type of recordset you use might help reduce the risk of corruption. Take a look at the following article, particularly the references to Chatty vs. Chunky handling.

Choosing between DAO and ADO

Though this is just an idea off the top of my head rather than anything I've researched or had experience with, my thinking is that if you can enforce "chunky" connections, you can cut the risk down a bit.

hth

edit: also note that my major concern with a WAN connection would be interruptions, and thus corruption, rather than performance. Not that performance wouldn't suffer, but the possibility of corruption in the data is a much larger problem, IMO.

Thanks again for the further advice Jack, hopefully all of this will lead me to an acceptable solution! (IMG:style_emoticons/default/thumbup.gif)

Check out my article on WANS here:

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

I give some tips and ideas and some possible solutions:

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Hi Albert,

Thanks for this! I've now got a wealth of information to go through so hopefully I'll be able to come up with a viable solution!

Cheers

Andy