MS Access to Azure SQL Database

  • Wecks

    Say Hey Kid

    Points: 672

    A Customer has recently migrated to Microsoft Access from On-Prem,  They wish to run a script that exports data from MS Access to the Azure SQL Database but are having no luck.

    They have a script that works on the On0-Prem but they cant get it to work for SQL Azure Database.

     

    My Questions are

    1. Is this even possible with Azure SQL Database?
    2. If so then how can this be done?

     

  • Grant Fritchey

    SSC Guru

    Points: 395586

    It's possible to write directly to Azure, yes. However, it all depends on how you do it. SSIS is one mechanism. You can also use Powershell or sqlcmd.exe. Make sure your firewall settings in Azure allow the client to connect.

    Other than that, it's hard to tell you what the solution is because you've provided no details on the problem.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Wecks

    Say Hey Kid

    Points: 672

    Not really a problem but there are 5 access databases and one SQL Server on prem.  A job has been created on the SQL Server to export data from these Access databases to the on prem SQL.  This is all in place and working.

     

    Now they have the SQL in Microsoft Azure SQL Database (Not a managed Instance).  The issue is that internal developers can seem to get the script to work and there investigations point to the OPENROWSET Function part of the script as after some investigation it appears as this will not work in Azure SQL.

    Others have written that you can get around this with using Linked servers however Linked servers are only available using a SQL managed instance and only to On Prem SQL.

    So my question is Simply. I have an access database,  I want to export data from it to an Azure Sql Database and automate the process as it needs to happen daily.  How Can i do this and if you can provide more details that would be great some pointers to some scripts etc but it is important that it works with Azure SQL Database not Azure Managed instance or On Prem.

    Many Thanks.

     

  • Andrey

    Say Hey Kid

    Points: 684

    Wecks wrote:

    So my question is Simply. I have an access database,  I want to export data from it to an Azure Sql Database and automate the process as it needs to happen daily.  How Can i do this and if you can provide more details that would be great some pointers to some scripts etc but it is important that it works with Azure SQL Database not Azure Managed instance or On Prem. Many Thanks.  

    If you want to push the data from on-prem to azure sql db, then it should not be a problem - just treat azure sql db as a linked server or remote SQL instance.

    For example, try to connect to Azure DB using sql account first and be sure that firewall on Azure DB is configured properly (allows connections from your subnet).

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716331

    If you still have an on-premise SQL Server, then a linked server to the Azure database can help with your existing job. If you are trying to get rid of on premises SQL Server, this is harder. Access wants to export entire objects, not a query. What you can do is write a script in PoSh that queries Access. At the same time, you can write the script to insert into the SQL Server.

    A couple examples:

    Query access to export data - https://devblogs.microsoft.com/scripting/hey-scripting-guy-can-i-query-a-microsoft-access-database-with-a-windows-powershell-script/

    Write data to SQL Server - https://www.sqlshack.com/6-methods-write-powershell-output-sql-server-table/

  • Grant Fritchey

    SSC Guru

    Points: 395586

    Wecks wrote:

    The issue is that internal developers can seem to get the script to work and there investigations point to the OPENROWSET Function part of the script as after some investigation it appears as this will not work in Azure SQL.

    If you're trying to do cross database queries as part of the migration, yeah, OPENROWSET will absolutely fail. I'd suggest first, not doing that at all. However, failing that, there is a way to do cross database queries, but it's kind of ugly.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply