Connection String error in Azure Data Factory Linked Service-CodePudding

I am trying to create a Linked Service to a Access Database in Azure Data Factory. The issue I'm having is that I get this error with my connection string.

It seems to be the backslash in the file location, because it goes away when I erase that. But I need those to write the full file path. It even does this when I try an example connection string from Microsoft or ConnectionStrings.com

the example string = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;"

I'm trying to use the JSON, but even trying to use a key vault secret doesn't work.

{
    "name": "MicrosoftAccessLinkedService",
    "properties": {
        "type": "MicrosoftAccess",
        "typeProperties": {
            "connectionString": "Driver={Microsoft Access Driver (*.mdb)};Dbq=N:\Inventory Data\WhrsInvLK_22.mdb;",
            "authenticationType": "Anonymous"
        },
        "connectVia": {
            "referenceName": "IntegrationRuntime4",
            "type": "IntegrationRuntimeReference"
        }
    }
}

CodePudding user response：

Since the \ is part of the connection string and not used to escape some other character, you need to make sure that the \ character in the connection string itself is escaped.

For example, look at the following demonstration. If you are assigning the same exact string to a variable, it will store the value by placing an additional \ to escape the backslash present in the value.

enter image description here

The output would be as following:

enter image description here

So, you can use the following as the value for your connection string:

Driver={Microsoft Access Driver (*.mdb)};Dbq=N:\\Inventory Data\\WhrsInvLK_22.mdb;