Access Replication

[thomas.dahl]

I would be really grateful if someone could help me with the following-

We have a Access 2003 based distributed database system for keeping track of customer contacts and actions within our company.

Currently we have 4 users with Laptop and 3 users working with a direct connection to our network.

The database is split into a "Master" which is located on a networked winXP machine in the office and each laptop has a replica of this master. They are able to synchronize with the "Master" when they come into the office. Each user then has a "Front End" database which is linked to a replica (in the case of the laptop users) or direct to the "Master".

The database has become very large over the years but as it is fully normalised it is manageable.

It works very well and the users are able to keep track of projects and had "actions" to each other over the database. We are very happy with the solution. It is very stable and has worked for 15 years with no major issues. Once in a while I will sit down and add some minor functionality when users request it.

We now hear that Access 2003 may no longer be supported in the near future. This is itself is not really a problem so long as it continuous to work and the windows versions we are using keeps supporting it.

Now...

It would be really cool if we could use the database on our iphones and start to build more robust back-up and usability features into the front-end of the system.

How do we do this with Access 2013? It looks like replication is no longer supported. Does this mean that we need to spend money on a real server? SQL etc. which is rather expensive. Can someone estimate how much work is involved?

Many thanks!

Thomas

 

[Beetle]

You might find this sticky helpful.

 

[thomas.dahl]

Thanks Beetle!

This is really depressing. We have gotten used to working with replicas and usually do not have Internet access when traveling.

Is there any solution?

I guess I could upgrade from 2003 to 2007 to get some life extension out of my database. This would mean having to forgo tablet and mobile connection. So be it!

What happens when 2007 no longer works?

If I am going to have to rewrite my whole FE I might as well look around for an alternative software solution. Actually I am tempted to do so out of spite!

Does anyone know of any solution out there that will do what I need? Replication and Real Time access. How about FileMaker? Will that do this?

We are not yet in a world with 24/7 global high speed Internet. Try getting access to a terminal server while at a trade show or in airplane!

Microsoft had a great product in Access but with replication gone they should rename the product to avoid confusion.

Thomas

 

[Beetle]

Just a few additional thoughts. I don't know about A2013, but replication is still supported up through A2010 as long as you use the older .mdb format (not the .accdb format). Not sure if this will help in your situation or not.

Also, the replacement for replication from what I understand is Sharepoint Synchronization, which would imply that it has the capability to let users work in a disconnected environment and synchronize later. Now, I don't know if this is even possible, I've never looked into it, but here's another thought (just spitballing really). An Access web app (again, i'm talking A2010 here, I don't know about 2013) can have both web and client objects (queries, forms and reports - not tables, tables are web only). The client objects can use vba, etc. just like you're used to with older versions. If all of your users have Access installed, and therefore don't necessarily have a need to run Access objects in a browser, maybe there is a way (through a hosted Sharepoint service or something if you don't have direct access to Sharepoint) to have a "web" app with client objects and just synchronize the data. Again, may not be possible, never looked into it, just throwing it our there. Wouldn't be surprised to see someone else jump in and tell me I'm basically a complete moron and have no idea what I'm talking about.

 

[Pat Hartman]

I might as well look around for an alternative software solution. Actually I am tempted to do so out of spite!

I hear you. Believe it or not, MS does listen but you have to write to them. Not enough people bother so all they hear is from their big corporate clients and if they don't use a particular feature, MS assumes that no one does.

If your remote users don't have WiFi access, then there is no solution except for replication (you could write your own) so converting to some othe product will result in the same issue. Working on a MS tablet (Surface) is possible because the newest Pro version supports the installation of Office and so it works like a PC. I'm not sure you would actually like working that way since you would need to use the on screen keyboard for data entry and you wouldn't have a mouse. And if you installed an add-on keyboard to facilitate data entry, you're back to a PC again and a laptop would be much more versitile. I won't be buying a tablet any time soon since they simply are not useful for what I do or even to run the apps I develop. They are best used for web surfing and watching videos.

As Beetle pointed out, A2010 will run older applications and even support replication so perhaps a compromise would be to convert the FE to A2010 so you can take advantage of some of the enhancements in forms and reports but leave the BE as an .mdb. That will allow you to use replication as long as possible and leave you in a better position for when WiFi is more generally accessable or more reasonably priced for carring your own HotSpot.

I would not suggest trying to create a web app with A2010. If you want to go that route, I would use A2013 which is much better technology. Anything created with A2010 is a dead end since the technology is very different in A2013. Both REQUIRE SharePoint but at least A2013 lets you keep your data in SQL Server which is far superior to SharePoint lists (which are not relational and so cannot enforce RI). In either case, you are in for a complete rewrite since neither offers a conversion path and neither supports VBA. All you have are macros and macros cannot do everything you might need to do so make sure you can reproduce the app with macros before you start on this journey. Start by comparing the events available for web forms with the events available for client/server forms because if you are using events that aren't supported, you need to change your process in order to proceed.

 

[Beetle]

Both REQUIRE SharePoint but at least A2013 lets you keep your data in SQL Server...

Well that's certainly an improvement. It always baffled me how MS ever thought that Sharepoint Lists, as implemented, could have ever been a viable alternative for folks using a RDBMS. I've never had a specific need for an A2010 web app, but I did create some test apps when it first came out, just as a learning exercise, and it just didn't seem like it could ever be taken seriously as web alternative because of all the limitations. I would think that most medium to large organizations would already have had true (.net or whatever) applications in place (or in development) and, even if they were already running Sharepoint within the organization, would probably view A2010 "web" apps as somewhat of a joke. Smaller developers on the other hand, would likely find it of little use because of the Sharepoint Enterprise (the high $$$ version of Sharepoint) requirement. I don't know who their target audience was, but they seem to have managed to completely miss everyone.

 

[Pat Hartman]

That was my opinion also. I looked, I saw, I walked away. I can't recommend Access web apps to my clients. For the most part Citrix/Terminal Services(Remote Desktop)/PCAnyWhere/etc are an excellent solution for remote access. I have also created apps with Access FE's linked to SQL Server or Azure databases hosted in the cloud. Those also work well as long as they are created with client/server techniques.

Access apps are not the kind of app where you expose them to the public so there is generally no issue with either the remote desktop or cloud-hosted BE. You always know who the users are and can provide them with a full Access installation or the Runtime engine. It is only applications like this one (Access World Forums) that are public facing and require unrestricted access that truly need a web platform.

 

[thomas.dahl]

Thanks guys.. But you have gotten me even more confused.

Are you saying that I can get at least the same functionality using Share Point as I have today? I am happy to ignore the web stuff until later. I simply need to get to the same level of functionality that I have today first.

Do you know of any good background reading that refers to how to do this?

Thanks!
Thomas

 

[Pat Hartman]

If your people need the ability to work offline, Sharepoint might be a solution if you don't have more than a few thousand rows in any table. If they don't need to work offline but they do need to work remotely, a better solution would be Citrix or Terminal Services (Remote Desktop) or linking to a SQL Server or Azure database hosted in the cloud. The Citrix solution doesn't require any application changes but the SQL Server solution might. It depends on how client/server friendly your app is. The primary change would be to make sure that every form is bound to a query that uses criteria to select the fewest number of rows possible. You don't want your forms sucking hundreds or thousands of rows of data over the internet. They will never load.

 

[Beetle]

If you just want to continue to use replication as you are now then, as mentioned before, you can upgrade to A2007 or A2010 and replication is still supported as long as you keep the database in the older .mdb format (not the newer .accdb format).

In the meantime you can research the other options that Pat discussed here and in the sticky. In your first post you mentioned -

We now hear that Access 2003 may no longer be supported in the near future.

I'm guessing you meant that you heard Microsoft may no longer be supporting it. I don't know if this is true or not, but does it really matter? The last time I went directly to MS for support on an Access db was....ummmm...never. Most people come to forums like this when they need help or "support" anyway, regardless of whether or not MS still officially supports the software in question.

 

[thomas.dahl]

Many thanks guys!

I am beginning to see the basis of a plan here. The plan being to go nothing.

Pulling tables out of the net is not an option as we have done tables with 25k plus rows so it would be very slow.

It seems that the only way to get replication and web access is to migrate to FileMaker. Have I missed any other options?

I might buy a copy of FileMaker to see what it does.

Again thanks for your help.

Thomas

 

[Pat Hartman]

The Citrix/Terminal services solutions do NOT pull data over the web. Your app runs on a server and the only thing sent over the web is keystrokes from your end and pictures of the desktop from the server.

With the SQL Server/Azure solution, you have complete control over how much data you bring into a form. Certainly 25k rows is too many for your user to deal with. That is why you use queries with selection criteris. Access passes the queries to the server and the server sends back selected rows. There are caveats but once you understand the architecture, you will have acceptable response time. People get sloppy when working on a LAN because LANS are incredibly fast compared to the internet. If you bind a form to a table containing 25k rows on your LAN, you would hardly notice but, you are correct, that won't work with a cloud based BE. That's why you need to learn to control the data your forms select.

File maker would have exactly the same problem as Access assuming it supports bound forms. The only good thing about File Maker (and this is from their propaganda rather than experience) is that client/server apps can work as web apps without changes. That is of course not the case for Access. An Access web database is totally different from an Access client/server database and there is no way to convert one to the other.

You never answered the question about working offline vs remotely. That answer will dictate your solution.

 

[thomas.dahl]

Thank you Pat for your explanation. This makes a lot of sense for remote devices.

Of course, it still does not solve the issue of our road warriors who need to work on data when off line. I spend hours in airplanes working on the datra kept in the database. In fact this is the best time to add notes and write meeting minutes.

It looks like there is no solution from Microsoft for this. The current Access set-up works brilliantly and I will tr to keep it alive for as long as possible. At the same time I need to prepare for the future and I can only find Filemaker that has the capability (with plug ins). Do you know of any other relational database that can be used. What ever happened to Borland, DBase and all the rest?

Thomas