none
Convert ADP with SQL 2000 to ? with SQL 2012 RRS feed

  • Question

  • My organization is migrating all our SQL2000 databases to new servers with SQL2012. The only database left on one server is an MS Access ADP which uses a SQL2000 back end.

    I did not write this database but I am the only person with any MS Access knowledge so it has fallen on me to migrate it.

    What are my options? I'm sure management will want this done quickly and  with the minimum of time expended.

    The database has about 6 simultaneous users and also interfaces with a mainframe database via ODBC.

    It has about 50 tables, 50 views and 100 stored procedures.

    Thursday, June 2, 2016 9:12 PM

Answers

  • I have not generally had problems with an adp when upgrading the backend SQL Server version, although I've usually gone one version at a time. The biggest constraint is that you can't use an older Access version to create or modify the SQL Server backend table structures or stored procedures, but you can always use SQL Server Management Studio or Visual Studio for that task.

    I would do a quick test by restoring a backup of your SQL 2000 database to your SQL 2012 server (you might need to go through SQL 2008 in between- I don't remember how many versions back SQL Server can restore) and then testing your adp. If it works, you're done. If not, you didn't waste too much time.

    Access 2010 is the last Access version to support adp, so you'll either need to keep Access 2010 or deal with the bigger issue of converting your current ado to an accdb.


    Paul

    Monday, June 6, 2016 9:18 PM
  • Can you expand on "you may encounter issues if you use SQL 2012 or later versions with your ADP".

    We backed up and restored the SQL 2000 db to SQL 2012 and changed the connection string. So far we have not encountered any problems.

    Tuesday, June 7, 2016 8:25 PM

All replies

  • Your question depends on the version of MS Access. ADP has been deprecated in the later MS Access versions. see similar link below:

    https://social.msdn.microsoft.com/Forums/office/en-US/cba21b60-0235-433b-9157-7e055e2e4b41/how-steps-for-convert-an-old-adp-file-to-accdb-in-access-20102013?forum=accessdev

    Thursday, June 2, 2016 11:00 PM
  • Hi AllTheGood,

    Do you only want to migrate SQL2000 to SQL2012, or you also want to change Access ADP FE to later Access version like Access 2013? If you migrate SQL2000 to SQL 2012, you will need to convert your ADP file, because you may encounter issues if you use SQL 2012 or later versions with your ADP. You could refer the link below for several different possibilities.

    # ADP support and the future

    https://msdn.microsoft.com/en-us/library/office/jj618413.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 3, 2016 3:01 AM
  • We are currently using Access 2010 but we will upgrade Access some time within 12-24 months. Longer term, my organization is abandoning MS Access and converting all apps to web apps. For now we just need a quick solution to get this database off the old server so the server can be decommissioned. This is the only database remaining on the entire server.
    Saturday, June 4, 2016 4:29 PM
  • Hi AllTheGood,

    As my above reply, if you want to convert SQL 2000 to SQL 2012, you will need to convert your adp file. So, for your current situation, I suggest you convert adp file to accdb file, convert SQL2000 to SQL 2012, and then relink the tables from accdb to SQL 2012. Before converting, I suggest you make a copy first.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, June 6, 2016 6:06 AM
  • I have not generally had problems with an adp when upgrading the backend SQL Server version, although I've usually gone one version at a time. The biggest constraint is that you can't use an older Access version to create or modify the SQL Server backend table structures or stored procedures, but you can always use SQL Server Management Studio or Visual Studio for that task.

    I would do a quick test by restoring a backup of your SQL 2000 database to your SQL 2012 server (you might need to go through SQL 2008 in between- I don't remember how many versions back SQL Server can restore) and then testing your adp. If it works, you're done. If not, you didn't waste too much time.

    Access 2010 is the last Access version to support adp, so you'll either need to keep Access 2010 or deal with the bigger issue of converting your current ado to an accdb.


    Paul

    Monday, June 6, 2016 9:18 PM
  • Can you expand on "you may encounter issues if you use SQL 2012 or later versions with your ADP".

    We backed up and restored the SQL 2000 db to SQL 2012 and changed the connection string. So far we have not encountered any problems.

    Tuesday, June 7, 2016 8:25 PM
  • Hi AllTheGood,

    >> Can you expand on "you may encounter issues if you use SQL 2012 or later versions with your ADP".

    This is a reference in below link. But, it did not say which issues would encounter.

    # ADP support and the future

    https://msdn.microsoft.com/en-us/library/office/jj618413.aspx?f=255&MSPPError=-2147217396

    >> We backed up and restored the SQL 2000 db to SQL 2012 and changed the connection string. So far we have not encountered any problems.

    Based on your test and Paul, it seems it works if you convert SQL 2000 to SQL 2012.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 8, 2016 5:24 AM