ODBC Error - Key value in connection string too long

[jcountryman]

Odbc.Query(
    "driver={iSeries Access ODBC Driver};" &
    "system=AS400.OURCOMPANY.COM;" &
    "dsn=dBASE Files"
    ,
    "SELECT * "&
    "FROM AS400DB.JOBSCOPEDB.EPOPERF"
)

I can run this query without an issue from within Excel, but in Power BI, I get this error:

[jcountryman · Accepted solution]

Seems like my problem is that the dsn property needs to be "dBase File" in Power BI— although Power Query from Excel will accept an 's' or not.  "dBASE Fileblargablarg" because it truncates the string to the first ten characters.

SMH.

[Linda_MILF · Accepted solution]

Hi There, 

Just wanted to offer a nice solution I found on another site for the warning "DataSource.Error: ODBC: ERROR [HY000]... The key value in connection string too long.

After resolving it, I propose that you do not assume that it is due to a problem with the connection string, but rather to an error in setting a user name and password for the query. 

This is the solution from 

mikefiorentinoit from Reddit.com

Open spreadsheet

Click on Data

Click on Queries and Connections

Double - Click on any of the connections

In the Power Query editor, click on File, Options and Settings, Data source settings, Click on dsn="?", Click on edit Permissions, Click edit, update user name and password., Click Save, Ok, Close, Click Close and Load, Close Queries and Connections window. Click on main sheet and click update button.

I hope that this answer can be shared with anyone who requires assistance.

[Syndicate_Admin]

For the below syntax (image 1), I get the error as shown in image 2
I am trying to connect to a KLD database and the solution mentioned previously didn't work. Could someone give the exact steps to resolve this error?

Image 1:

Image 2:

Thanks

[jcountryman · Accepted solution]

Seems like my problem is that the dsn property needs to be "dBase File" in Power BI— although Power Query from Excel will accept an 's' or not.  "dBASE Fileblargablarg" because it truncates the string to the first ten characters.

SMH.

[v-piga-msft]

Hi @jcountryman ,

From your second reply, it seems that you have found the cause of this error.

Have you solved the problem? 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry