A2007 to A2013 - should I?

[Sinfathisar]

Hello Everyone,

I am hoping for some advice about upgrades. I am a Power IW, and its been a couple of years since I've done any developing with Access. My company has tasked me with doing research on what to do moving forward with Access since we are upgrading machines (thankfully we will be sticking with Windows 7).

We currently have a split A2007 FE with A2003 BE. We have a database template and make a new application for each "project" my company does. The tables get turned over to our clients at the end of the project. We dont have user security set up, and data gets added both at the office on PC and in the field (no internet connection available). Previously, field data was added using forms on ancient PDAs (the forms were developed using Visual CE software) and then imported as xml data into Access. We are currently working on updating the PDAs to tablets with Android and can no longer use xmls the way things are currently set up, so we already have quite a bit of re-writing to do. Might have a copy of Pendragon (used to make forms for tablets) that we can use, but I am not sure if there is something else better, and maybe open source that will be more compatible with Acess. If anyone has recommendations for tablet interface developer tools, that would be greatly appreciated as well.

It is going to be challenging and expensive if we want to keep A2007 for a few years, but I am unsure at this point what to recommend as a possible alternative. One of our clients has their own database and has gone the Citrix route with A2013. I have used it, and there seem to be some annoying bugs associated with the way they have it set up. I think I'd rather not go down that road.

I am pretty sure my company doesn't want to pay for MySQL or cloud storage or anything else big and we don't really need it for the amount of data we store. A couple of things have crossed my mind but I am probably unaware of all the options, and being a Power IW, I and my "team" of developers are going to need to learn a lot to get this done. Could we successfully migrate the application to A2013 since it relies heavily on VBA - can we still use VBA at all in A2013 if we are not doing a web app? I don't think macros only will provide enough functionality for all the complicated stuff we currently have working. Should I say maybe do an Access BE and a web FE with PHP or something? I would need to learn PHP...we possibly have a good chunk of time this year where things will be slow and there might be time to learn everything we need to do a rebuild.

I have been reading through some threads here to help get an idea of what things look like with the release of A2013, but I feel a bit overwhelmed at the moment. Sorry if my rambling doesn't give you enough information to answer my questions!

 

[Lightwave]

This definitely looks like a big and important decision for you.

Firstly I would recommend you getting your hands on a laptop or desktop with an installation of Access 2013 on it and just having a go.
Yes Access 2013 runs VBA and you don't have to go the route of MACROs as a result the majority of your old database should run no problems on Access 2013. The only gotcha to this is the Pivot Tables and Pivot charts. Information from MS indicates that this feature has been deprecated in Access 2013. If you use this feature this may be a hassle. If you don't have Pivot Tables it really shouldn't be an issue. If you specfically need Pivot table then there is no reason that you can't move Access 2007 on Win 7 and quite possibly Win 8 devices and there is no real reason that it should cost you money (this seems to be an IT myth that old legacy systems cost money err.. No old systems are digital and as per the digital promise they don't age sure in comparison the graphics look dated but if they work they could actually potentially get faster with age as although the code hasn't changed the underlying technology on which they run gets better ) Also Access 2010 is an option if you need pivot table as it still supports this.

If you desperately need to go the web route then there will be quite a bit of redesign - there is still very little that allows design of web databases with the ease and lack of cost that you get with desktop databases and even if you got the MS Access web route via sharepoint you will need to completely redesign your database and even then more complicated aspects may simply not be available.

Non MS Access Web options I have looked at are
Instant Developer (free trial version available)
Alpha 5 (free trial version available)
Visual Studio Lightswitch (and the general VS 13 suite) (free trial partially available)

As with everything simple databases seem to be fairly simple to get onto the web but complex setups seem to be a whole different ball game. And with Web Databases you are going to have to think about Web Servers - which means Sharepoint / IIS your own servers / VPS or perhaps some other more unusual variant. (you mention citrix)

With the Web I would recommend you budget for additional spend. It is likely that you will have to pay for software and or server time which initially is likely to be quite a bit in excess of MS Access. Additionally with a lot of web platforms they are wanting to move you to a "software as a service model" which is much more like being a tenant in a building rather than the old fashioned upfront cost then free afterwards so there are likely to be revenue implications.

Its difficult to argue with distribution through the web as the future but certainly I'm not keen on being the tenant rather than owner of my own IP.

Even if you go the Web route there is likely to be a transition phase which in all likelihood will cost money and take time to complete so you very probably may as well have a go at getting your systems into Access 2013 as in all likelihood you will have to do it even if you want to move to the web … which leads us back to getting your hands on a copy of 2013 and just ramming your old db into it (A2013 should run mdb and accdb files) - and given that the web is the future if you can afford start trying to look at the alternatives for Web Design just on one computer. MS Access Web Design is unfortunately tied into their proprietary sharepoint technology which again is making you into some kind of MS paid tenant with sharepoint pricing being more complicated to understand than Quantum Mechanics.

I should add if you do want to go the MS Access Web route you also have an option to research Office 365 route which I think is MS's route for smaller organisations to pay by individual seat and who don't want the sharepoint server management overhead but would still like web MS Access. Thankfully cost is a bit clearer on this one.

prices here
http://office.microsoft.com/en-gb/business/compare-office-365-for-business-plans-FX102918419.aspx

So in Summary
If going desktop may as well stick with Access if you're going with Web budget for spend and expect total re-design. Canny organisations will probably stick with legacy systems for complicated internal systems and then choose the web where it really adds value and distribution rather than complexity is important.

 

[Sinfathisar]

Thanks for the great feedback Lightwave. I am going to have a chat with a couple of people at work, see what they think, and see if I can get a copy of A2013. I know our database has been plugged in to A2013 already - though I haven't done it first hand, and there were some functionality issues. Will have to do some checking to see if they can be resolved.

Our machines already run A2007 mostly on Win 7, so that might be the best option if we can figure out changes with the tablet interface. My boss mentioned some costly option if we want to stay with A2007 because we would need to purchase stand alone copies of A2007 for some reason. I need to get more details about that and check if there are any alternatives.

I don't think we really need web, although we likely have a large re-design on our hands anyway! There are still big decisions to be made, and I'll try to update here in case this is ever useful for anyone else.

 

[Lightwave]

IMPORTANT UPDATE. Note Cross-tab queries are still available in Access 2013
Pivot tables and Pivot charts have been deprecated in Access 2013 which is likely to be less of an issue as I am not aware of a lot of people using this feature.

Updated on 9 January 2014

 

[spikepl]

From your tale it is difficult to deduce what your future needs are, and especially how they have changed compared to what you have now. Do not focus so much on specific tools, but on business needs and then from those derive the requirements (who needs access to what, when and where and how many).

Do not reject Citrix (or similar solutions, or any other solutions ) just because you have seen one imperfect installation. Developing some specific solution by yourselves is normally very expensive, if ALL incurred costs are honestly taken into consideration.

Update: Things like software as a service are here to stay. 100 years ago everybody built their own power statation, but now electricity comes out of a wall socket. Software is going the samme way. But the decision makers are behind the technology: the ones in power now probably get trained and re-trianed by their teenage kids

 

[Lightwave]

Please note : I have edited the original statement to clarify points about the position of MS Access 2013 on pivot tables because I was unhappy with the accuracy of my original statement. Hope I didn't confuse people.

 

[Sinfathisar]

@spikepl - thanks for your comment. It made me take a step back and think about some things I hadn't been focusing on.

As far as business needs - most of our database functionality requirements have not changed in the past few years. The biggest change is updating hardware.

We do need to replace our field data collection forms because old technology/hardware is finally failing (PDA's). We purchased some Android tablets and are looking in to software options for creating forms. Field data need to be available for collection when offline and easily exportable to import back into Access. It appears there are many options for this. Droid DB from Syware is being tested but doesn't seem to have easy data transfer. We might look into formotus with infopath as another option here.

As far as the desktop client goes, the big concern with upgrading to 2013 is loss of replication as an option - which we currently do use occasionally since we don't always have internet connection. The boss would prefer to keep all data hosted in house, and data/tables must be presented to the government (Corps of Engineers) in "Access" format - we are currently unclear if there are restrictions on JET vs ACE and are contacting authorities to help in our decision making process. Because of this, for the time being we are going to go with MS volume licensing so we can keep using A2007 with our new hardware. We will also be testing loss of functionality with A2013 to see how much re-write would be needed if we decided to upgrade in the near future. If that ends up being the case, we might be able to get around the replication problem by setting up a machine as a server to remote in, but I'm not sure yet if that would be an effective solution.

Thanks guys for your thoughts