none
Migration from Access 2010 .adp to Access 2016 .accdb RRS feed

  • Question

  • Hi All,

    I need some help. I'm not an access expert yet! I am upgrading an access 2010 .adp access front end with a sql server 2012 backend. I imported all of the forms, reports, etc created linked tables into an access 2010 .mdb then imported into an access 2016 database. now my issues are all of the forms use the currentproject.connection. I tried to create a global module and in the first form I set the connection for the project, but it is not working or is there a different way I should be doing this? help!

    coder



    • Edited by codefreak1 Wednesday, December 26, 2018 3:47 PM
    Wednesday, December 26, 2018 3:44 PM

All replies

  • Hi,

    Quick question first, are you trying to access the database online? Or, do you just want to use your Access file as a front end to your SQL Server back end?

    Wednesday, December 26, 2018 3:55 PM
  • Ok, what you have to do is import the forms.

    In fact, before you import the forms, you should create that blank database, and link up all of the tables.

    (Use the linked table manager).

    Make sure your linked tables work.

    And make sure your linked tables have the SAME name

    In access “odbc” that you are migrating to, if you link to a table called tblHotels, access will “usually” link the table as:

    dbo_tblHotels.

    So after linking the tables, you want to re-name above to tblHotels. This will be a linked table to sql server.

    At this point, your form does NOT have some “connection”. And in fact ADO forms did not have a connection property.

    So I not sure what you mean by your forms use that “connection”. They should not.

    However, it is possible that you mean some VBA code in that form?

    You can continue to use CurrentProject.Connection.

    You “old” code likely looked like this:

    Private Sub Command61_Click()

       Dim rst     As New ADODB.Recordset

       rst.Open "select * from tblHotels", CurrentProject.Connection  

       rst.MoveLast

       MsgBox rst.RecordCount

     End Sub

    The above is an “air code” samples.

    To make this code work in your new form, y ou have to search + replace the “open” command. You code will become:

       Dim rst     As New ADODB.Recordset

       rst.Open "select * from tblHotels", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic

       rst.MoveLast

       MsgBox rst.RecordCount

    Note how I was able to use the same code, and NOT have to change the use of the built in connection object.

    The above code should work.

    Depending on the ADO code, you may well not have to add “adOpenKeyset”, and the adLockBatchOptimistic, but I believe that “most” ADO project code did NOT include these extra keywords, but standard VBA + ADO reocrdsets will have to.

    Also make sure you put the ado reference above the built in “DAO”, since all of your reocrdset code VERY likely was written using ADO, and if you don’t do this, lots of your code will break.

    So you can continue to use CurrentProject.Connection in your code.

    However, it is some what “confusing” you stating that the forms use this connection? The forms do not.

    The VBA code will of course use the connection object.

    So if the forms “data source” was a simple table name, or query? You likely can continue to use that table name.

    For a query, you may well be able to use the existing “view” that the ADP project created.

    So the form does not have some “hidden” connection.

    If you have a ADP form without code?

    Then just ensure that a linked table with the SAME table name exists. Then import the ADP form. At this point, ZERO changes should be required to that form in most cases.

    The extra work will be checking the forms VBA code – and especially “ado” reocrdset code.

    With linked tables, then no connection string(s) are required in your code – you can continue to use the built-in connection object access provides.

    If you get these steps working in the right order,  you will save HUGE amounts of effort and time.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Thursday, December 27, 2018 5:42 AM