none
Best way to connect Access 2016 to SQL back end RRS feed

  • Question

  • Hello,

    I have an adp access file that I'm moving from 2010 to 2016. I realize that adp is obsolete. I'm looking for a solution that stays with the access front end and SQL server backend and that can survive for many years. The adp file currently uses ADO.

    Can anyone advise on the best path forward:

    1) Convert adp to accdb, convert all ADO code to ACE/DAO

    2) Convert adp front end to accdb and leave ADO code alone, connect to back end using ODBC linked tables. I'm concerned that if the SQL 2012 sever is upgraded, and/or microsoft changes anything, can the ADO become obsolete or the ODBC connection?

    Thanks much in advance!

    Rich

    Thursday, April 12, 2018 4:10 AM

Answers

  • I would agree with .theDBguy. Both ADO and ODBC will be around for a long time, so I see no need to convert. If you are comfortable with Microsoft Access then yes convert the database to .accdb.

    At some point you may to consider learning .NET and using VB.NET (if you prefer VB). It will provide many more options for developing apps with SQL Server.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by smoenra Monday, April 30, 2018 2:16 AM
    Thursday, April 12, 2018 6:33 PM

All replies

  • Hi Rich,

    I am not sure converting your ADO code to DAO is really necessary. Technology may change but I don't see the end for ADO or ODBC in the near future.

    Good luck!

    Thursday, April 12, 2018 2:41 PM
  • I would agree with .theDBguy. Both ADO and ODBC will be around for a long time, so I see no need to convert. If you are comfortable with Microsoft Access then yes convert the database to .accdb.

    At some point you may to consider learning .NET and using VB.NET (if you prefer VB). It will provide many more options for developing apps with SQL Server.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by smoenra Monday, April 30, 2018 2:16 AM
    Thursday, April 12, 2018 6:33 PM
  • Thanks for the replies. Paul, regarding your comment on converting to .accdb, is there another choice I have not considered? I just assumed that the .accdb is my only real choice going forward.

    Thanks!

    Friday, April 13, 2018 1:00 AM
  • Thanks for the replies. Paul, regarding your comment on converting to .accdb, is there another choice I have not considered? I just assumed that the .accdb is my only real choice going forward.

    Thanks!


    Yes, you have assumed correctly. For a Microsoft Access front end the .accdb would be the only real choice.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 13, 2018 3:37 AM
  • Hi smoenra,

    Is your issue is solved now?

    I find that you did not done any follow up for a long time.

    If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the suggestions given by the community members.

    If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

    It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

    If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

    I suggest you to update the status of this thread and take appropriate actions to close it.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 9:41 AM
    Moderator