Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Server configuration for MS Access - SQL Server app

Hi Experts,

We have an Access ADP project app (A2003) that is linked to SQL 2005 Express.

Now our IT guy wants to upgrade the server to Windows server 2012.

Do you foresee any issues with such a move?

P.S. And whats about to upgrade the SQL to 2008+.., will that transfer go smoothly?
SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Hi Experts,

@Kelvin,
I assume that is for the Server that has your SQL Server instance on?
Yes.

@Jim,

This is something new to me

We continue to support creating and working with .adp files in Access 2010.

BTW, We have some users that have A2010 in their pc, and I had to do some adjustments for the app as certain things didn't work as they used to..However now that everything works perfectly, if all users upgrade their office version to 2010, would you say its ok to go ahead and convert the app to 2010, meaning I should start designing it in A2010?

P.S. If thats a long discussion then leave it, will do it in another post.


@Vitor,
Mind that SQL Server 2005 does not work on Windows 2012.
This is crucial info, thanks for sharing it!

As I see the more experts chime in the better I get prepared..will wait another day or two to finalize it.

Thanks,
Ben
An adp is really a pre-Access 2007 tool. There is not Access 2007 or later version - they won't run with Access 2013 or later. You can still make changes in Access 2010 and add odd bits and pieces from VBA that were added after Access 2003 (i.e. TempVars).

As Jim says, test, test, test


Kelvin
You will have less problems with A2010 and SQL 2008 than you will going the other way A2007 with SQL 2008.

 As the article pointed out, working with a later release of SQL can be an issue.  An Access ADP simply isn't aware of the features in the newer version.

I don't think you need to move to A2010 now, but I would be aware that at some point, that's the version you'd want to get to as it will carry you forward the farthest.

 But with that said, A2010 is the last version to support ADP's, so I would look at moving away from using ADP's at all rather than focusing on A2010.     A2010 is now six years old already and while you can live with it for another five years or so, at some point your going to have a problem.

 Best to start making plans now for getting away from the ADP format.

Jim.
Spot on, Jim.
If a change need to be made then do it properly. Do not use unsupported versions anymore. It will only cause more issues in the future.
Avatar of bfuchs

ASKER

Thanks to all participants!
Avatar of bfuchs

ASKER

Hi Experts,

Sorry for bothering you..

As we are now at the point of ordering the new server (2012), I would like to know if all what was concluded in this thread is still in effect.
Meaning that its OK to move on to Access 2010/SQL 2008. despite the fact they may no longer be supported.

PS. Our manager is very concerned of using unsupported software in the wake of new threats like the wannacry ransomware..

Thanks,
Ben
It SHOULD work OK. If it were me, I'd go Access 2013 (the last version of Access that supported apd's)., and the later the version of SQL Server the better. I've not tested an adp against SQLserver 2016 (or vNext for that metter), but 2014 is good to use.


Kelvin
<<Meaning that its OK to move on to Access 2010/SQL 2008. despite the fact they may no longer be supported.>>

 Yes and Access 2010 is the last version that will support an ADP.

Jim.
But I would go with SQL2008R2 as Victor suggested, not 2008.

Jim.
SQL Server 2008R2 should be the minimum wanted here because the limit for the database size were improved to 10GB but if the OP can go for a more recent version, why not?
Avatar of bfuchs

ASKER

Hi Experts,

First thanks for your response.
I'd go Access 2013 (the last version of Access that supported apd's)
Yes and Access 2010 is the last version that will support an ADP.
Actually this is something that I also saw contradicting opinions on the web, and I'm planning to purchase a copy of 2013 to perform a test. (or perhaps a runtime version is just enough).

Actually I have seen people stating the sorting A-Z doesn't work properly in ADP's, and if that is mainly the case I can live with that, as its simple to provide a sorting menu or button.

Thanks,
Ben
Avatar of bfuchs

ASKER

Hi,

Just confirming Jim 's stand, see attached.

Thanks,
Ben
Untitled.png