none
Migrating an Access 2016 back end Database Schema, Tables, data to Azure SQL Database

    Întrebare

  • Hi Azure Forums

    As a first attempt setting up Azure, hopefully this is not a repeat question.

    I have been attempting with MVP to get an access 2016 backend into the SQL Server I set up.

    My dialogue is here, and have not found any documentation similar to the 

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

    for access.

    That is what applications do we need to install on our pc, and the steps to prepare and upload or migrate the Access 2016 back end Database Schema, Tables, data to Azure SQL Database

    Any assistance is appreciated, even by tech support.

    Thank you.

    Simon

    miercuri, 11 iulie 2018 11:01

Răspunsuri

  • Hello,

    You can have your local database as SQL Server local instance used for reporting and have the Azure SQL Database for data entry as long as you keep them in sync with Azure SQL Data Sync.


    Having to write to two databases from your front end will increase transaction time and may impact application performance.


    Be careful with Access Forms that have many combo boxes if each combo box represent a query to the database. Another suggestion is to avoid using row versioning with timestamp fields.


    One more thing, please create another thread for different questions, this forum thread already contains too many questions and answers.


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    marți, 17 iulie 2018 14:17

Toate mesajele

  • Hello,

    You need to use SQL Server Migration Assistant as explained on the following documentation:

    https://docs.microsoft.com/en-us/sql/ssma/access/migrating-access-databases-to-sql-server-azure-sql-db-accesstosql?view=sql-server-2017

    The documentation has detailed steps.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    miercuri, 11 iulie 2018 12:01
  • Thank you kindly Alberto

    I have attempted for the first time to use these tools.

    At first I downloaded:

    Microsoft SQL Server Migration Assistant v7.8 for Access

    https://www.microsoft.com/en-us/download/details.aspx?id=54255

    But I do not seem to have some of the requirements.  Can you please indicate where I can download the following items: 

    • Requirements for the SSMA v7.8 for Access client:
      • Microsoft Windows Installer 3.1 or a later version.
      • The Microsoft .NET Framework version 4.5.2 or a later version. You can obtain it from the .NET Framework Developer Center.
      • Access to and sufficient permissions on the computer that hosts the target instance of SQL Server.
      • DAO provider version 12.0 or 14.0. You can install DAO provider from Microsoft Office 2010/2007 product or download it from Microsoft web site.
      • Microsoft SQL Server Native Client (SNAC) version 10.5 and above. You can install SNAC from Microsoft SQL Server web site as part of SQL Server Feature Pack.

      As an alternative question, can use a virtual machine / or file server concept in microsoft Azure to simply store the backend access accdb file that has within it only the tables and relationships, and any rules within these table properties.

    The front end file contains all the forms/reports/vba, 

    and we would just link via dns odbc.

    Thank you kindly.

    Simon

    joi, 12 iulie 2018 00:06
  • Hello,

    You don’t need to install Windows Installer 3.1 since it was intended for Windows XP. Windows 7 used Windows Installer 5.0. A new version of Windows Installer comes with every new Windows version.

    You can obtain the latest version of .NET Framework on the following URL:

    https://docs.microsoft.com/en-us/dotnet/framework/install/

    Installing the latest Access runtime may get installed also the latest DAO.

    https://www.microsoft.com/en-us/download/details.aspx?id=50040

    Get the latest SQL Server Native client from the following URL:

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-2017


    I answered the question about the backend on another forum for you. I answered this question on the Storage forum.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    joi, 12 iulie 2018 04:36
  • Thank you kindly Alberto

    A Question about the access runtime.

    I am downloading it now, it is about 316 mb, and has _428 .exe at the end.

    I previously installed the access 2007 runtime, and it then caused all the access files, when clicked on file explorer to open in 2007, which would not work.

    In this this situation, will not the same thing occour, and the access files open with this 2016 run time version instead of the office 365 full version already on the machine.

    Or can they both exist, and then I just have to go into control panel, change the file type to open with the access 2016 office 365 version after i install this.

    Thank you.

    Simon

    joi, 12 iulie 2018 06:13
  • Thank you kindly Alberto


    Another question please about 

    "Get the latest SQL Server Native client from the following URL"

    I was following this fms guide

    link-to-azure-sql-database

    That indicates:

    There's a bit of confusion around the installation of SQL Server. As the developer, when you use SQL Azure, you don't need to install the full SQL Server product on your PC, just the SQL Server Management Studio (SSMS) to manage the hosted database. Alternatively, you can install the free SQL Server Express version. 

    My question is such that I have already installed SQL Server Express.

    Will this SQL Server Native Client still be necessary to install Microsoft SQL Server 2012 Feature Pack

    Thank you kindly.

    Simon

    joi, 12 iulie 2018 06:24
  • Hello,

    About the runtime, they can coexist, you just have to change which program should open Access file based on their file extension.

    If you have SQL Express installed you should have already the SQL Native Client installed. You can verified that on the list of installed programs of your computer.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    joi, 12 iulie 2018 12:45
  • Thank you most kindly for all your help Alberto,

    And apologies for my over enthusiasm to get a quick response!

    I did notice yesterday that after the installation of the access2017 runtime, that as you indicated, 

    all the access related files (including 2016) defaulted to opening this program.

    Hence, in control panel, apps, default apps, are,  i changed which ever ones back to using Access 2016 whenever the option was presented.

    There were some remaining access file types (access 2016) , that did not give the option in the control panel.

    At first, although i didn't need to do it, was to uninstall access 2007 run time.  I then wen't into control panel to find those file types to reset at access 2016, but they were all not displaying now from the list... .mdb, accde, etc.... basically all the file types.

    Hence the solution next was in file explorer itself, when you right click on the file, to then find the access exe runtime to have this associated with the file type.

    As I was unfamiliar where the access 2016 runtime exe was, I asked support to set this for me.  They reassociated the file type.  All files are opening fine again.

    But in the control panel, apps, default apps, there is no listing of all the access file types still.  It is not too much concern, and I imagine if the office 365 set to default setting programs will realign everything.  

    But I just wanted to share this experience.

    My next challenge is to still get those missing DAO's required by SSMAforAccess_7.8.0, 

    because it is still not installing past the first error message I have listed in the 

    https://social.msdn.microsoft.com/Forums/en-US/7ef85365-839b-4d55-b55b-cd556a9cbe10/missing-daos-when-trying-to-install-ssmaforaccess780?forum=sqldataaccess

    Thank you once again most kindly for your help.

    Simon

    joi, 12 iulie 2018 23:41
  • Hi Alberto, 

    We have been looking at different options, 

    and if Sharepoint server is an option to host the backend as well.  I will look at all options if you have any recommendations about sharepoint to achieve the task at hand above.

    Thank you kindly.

    Simon

    vineri, 13 iulie 2018 06:48
  • Hello,

    Working with SharePoint is not an option from my point of view. It requires a lot of resources/servers and a complicated infrastructure.

    Allow me to investigate how to overcome the issue related with the DAO requirements.


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    vineri, 13 iulie 2018 15:28
  • Thank you once again Alberto

    I have put some forum discussions about other people's experiences in my other discussion regarding the missing DAO's.

    I am wondering if it is worth me learning using an SQL Server Management tool to manually recreate the tables by visually looking at the access tables i created and replicating.  Perhaps any guidance on a tool, and download area, and any instructions, or advantages, disadvantages if I go down this path.  

    As a small note, the reason why i mentioned sharepoint, is because

    Daniel Pineault, MVP has a 

    how-to-hybridize-your-ms-access-database-in-office-365-azure-database

    devhut.net/2014/01/13/

    I have not put the full link as it does not let me save this discussion with a hyperlink.

    Thank you again kindlly

    Simon

    sâmbătă, 14 iulie 2018 05:02
  • My apologies that last link was actually about having Enterprise i believe

    in order to get to the Sites as indicated.

    https://www.devhut.net/2014/01/13/how-to-hybridize-your-ms-access-database-in-office-365-azure-database/

    But I am also reading an interesting forum discussion.

    https://answers.microsoft.com/en-us/office/forum/office_2010-access/access-2010-split-database-backend-on-sharepoint/a08dc320-8873-4133-9f90-a935f308f50d?db=5

    Thank you again for all your assistance.

    sâmbătă, 14 iulie 2018 05:07
  • Hi Alberto, 

    Just as an update, I have been chatting with Azure Support, and they indicated they will be allocating a Engineer to help me.  Will post any updates.

    Also there is something you have indicated to me about Azure SQL Database previously.

    I think I already created a empty Azure SQL database within the portal. 

    I thought there were 2 different types, and Azure SQL Database and SQL Database in the portal

    when you click the SQL Database link on the left hand menu.  But i think it is simply an Azure SQL Database?

    Thank you.

    Simon

    sâmbătă, 14 iulie 2018 06:39
  • Hello,

    You have the option to create an Azure SQL Database (logical) server with Azure SQL databases, you can also create an Azure SQL Managed instance.

    https://joeydantoni.com/2017/10/05/managed-instances-versus-azure-sql-database-whats-the-right-solution-for-you/  


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    duminică, 15 iulie 2018 18:42
  • Thank you very kindly Alberto for showing us all the options:

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance

    Simon

    luni, 16 iulie 2018 02:00
  • Hello,

    You are welcome!


    If you need anything else, just let us know.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    luni, 16 iulie 2018 12:47
  • Thank you very kindly Alberto, for your patience

    I have asked this opinion, and perhaps you can give me also a viewpoint:

    Hi UtterAccess

    As I am understanding Azure more now, an opinion please

    1) A local SQL Server Database is setup as the backend for an access front end (instead of an Access backend file)

    2) The Access frontend updates at the same time both the Azure SQL Database and the local SQL Server Database, 

    (Taking note that the Azure SQL Database is updated live by both the Administrators and the Staff, hence requiring multiple IP's)

    3) Instead of using Azure SQL Sync to keep the local SQL Database and the Azure SQL Database synchronised, 
    that the Administrator toggles to the local database if they wish to say do merely reporting, and using one of the tools on this forum:
    http://www.UtterAccess.com/forum/index.php...ween+local+prod


    In this manner the local and online copy of data is always sync'ed. And there is less data being synced to the local copy, thus reducing costs.
    And the performance for the administrator would be quicker when they wish to merely retrieve information, by working of an up to data local copy.

    Now of course we would still need a sync at a less frequent level to the local copy, perhaps on a daily basis, for any changes made by the staff to the Azure SQL Database.

    Is such a setup possible, or not practical, or rather logical?

    Thank you.

    Next I have been reading to understand SQL Server more.

    I have successfully launched SQL Server Management Studio, 

    and now can see the local databases under the SQLEXPRESS

    I would like to create a local copy of the back end of Access to be an SQL Server Database first before uploading to Azure SQL Database.

    As I am awaiting the Azure support team to assist me with the SSMS for Access, and which is giving me time to continue Front End development

    I tried at first following 

    https://www.quackit.com/microsoft_access/microsoft_access_2016/howto/how_to_link_an_access_database_to_sql_server_in_microsoft_access_2016.cfm

    attempting to use both the Driver: SQL Server  and also the ODBC  Driver for SQL Server 13

    They both came up with an error message when i tried testing the connection.

    The certificate chain was issued by an authority that is not trusted

    So I read further, and is it such that I should be creating a new user account as indicated first here

    https://stackoverflow.com/questions/360141/how-to-connect-to-local-instance-of-sql-server-2008-express

    Now that we have determined that Access Office 2016 is 32 Bit version but Microsoft SQL Server Management Studio is 64 bit, will there be a difference in which ODBC driver is created?, ie. 32 or 64... or is my question non sensical.

    Now I know why the IT consultant gave us a reasonable quote to set this up... hence my attempt to explore my skills !

    Thank you once again kindly.

    Simon



    Thank you in Advance

    marți, 17 iulie 2018 08:39
  • Hello,

    You can have your local database as SQL Server local instance used for reporting and have the Azure SQL Database for data entry as long as you keep them in sync with Azure SQL Data Sync.


    Having to write to two databases from your front end will increase transaction time and may impact application performance.


    Be careful with Access Forms that have many combo boxes if each combo box represent a query to the database. Another suggestion is to avoid using row versioning with timestamp fields.


    One more thing, please create another thread for different questions, this forum thread already contains too many questions and answers.


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    marți, 17 iulie 2018 14:17
  • Thank you very kindly Alberto.

    Thank you in Advance

    miercuri, 18 iulie 2018 02:04
  • Hello,

    Thank you for visiting Azure forums!


    Hope to see you asking more questions soon.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    miercuri, 18 iulie 2018 02:19