Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Connection to a Visual FoxPro Database

Hi everyone,   I'm new in the Power Bi universe, I'm working with this software since 2 months, so I don't have a lot of experience. I need to access a Visual FoxPro database in Power BI. Firstl...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous 

Firstly, have you ever tried Visual FoxPro ODBC Driver?

 

Are the "Microsoft.ACE.OLEDB.12.0" Driver, Power BI Desktop, on-premises gateway installed on the same server as Visual FoxPro database ?

Based on my experience, on-premises gateway should be installed in the same server as the data source ( Visual FoxPro database in your scenario).

 

How do you add data source under the gateway?

Did you select Data source type=ODBC, use the correct connection string and authetication?

Here are some articles you could learn:

Enterprise Gateway with ODBC

Connect to data using generic interfaces in Power BI Desktop

 

Finally, if suggestions above can't slove the problem, please share the error message when gateway not working as expected and some other information as i mentioned.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-juanli-msft,

     

    Thanks for your answer.

     

    Yes, I tried it and if I'm not wrong ODBC isn't working anymore and OLE DB is only available in 32 bit version (I installed it and I could see it on PBI 32 bit but not in PBI 64 bit).

     

    I actually got 3 different servers, SRVAPP1, SRVAPP2 and SRVFILES, I can reach data from both SRVAPP1 and SRVAPP2. My gateway is installed on SRVFILES and my Foxpro database too (I'm not 100% about it, I need to ask the person who manages this database, I'll do it next monday if I don't solve my problem before).

    For the data source under the gateway, I put OLE DB as "data source type" and here is my connection string :


    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=*path*;extended properties=dBASE IV

     

    If I share the error message it'll be in french, I tried to translate it (I can't share everything, *path* is the path to my database folder)

     

    Impossible de se connecter Nous avons rencontré une erreur lors de la tentative de connexion à . Détails : « Nous n'avons pas pu inscrire cette source de données pour les instances de passerelle de ce cluster. Vous trouverez plus de détails sur les erreurs spécifiques de chaque instance de passerelle ci-dessous. »Masquer les détails : Can't connect, we encountered an error while trying to connect to . Details : "We were unable to publish this data source for the gateway instances of this cluster. You'll find more details on the specific errors of each gateway instance below"

    ID d'activité:b7055477-2421-4538-83e8-c588ad8738ad
    ID de demande:4cce458c-8ff6-ef24-48d1-075d32deea0a
    URI du cluster:https://wabi-west-europe-b-primary-redirect.analysis.windows.net
    Code d'état:400
    Code d'erreur:DMTS_PublishDatasourceToClusterErrorCode
    Heure:Wed May 29 2019 10:20:34 GMT+0200 (heure d’été d’Europe centrale)
    Version:13.0.9551.142
    *Gateway_name*:Impossible de se connecter à la source de données undefined.
    Can't connect to the data source "undefined"
    Code d'erreur sous-jacent:-2147467259
    Message d'erreur sous-jacent:OLE DB : « *path* » n’est pas un chemin d’accès valide. Assurez-vous que le nom du chemin d’accès est correct et qu’une connexion est établie avec le serveur sur lequel réside le fichier.
    OLE DB : "*path*" isn't a valid path. Make sure the path is correct and that a connection is made with the server where the file resides.
    DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
    Microsoft.Data.Mashup.ValueError.DataSourceKind:OleDb
    Microsoft.Data.Mashup.ValueError.DataSourcePath:data source=*path*;extended properties="dBASE IV";provider=Microsoft.ACE.OLEDB.12.0
    Microsoft.Data.Mashup.ValueError.ErrorCode:-2147467259
    Microsoft.Data.Mashup.ValueError.Message:« *path* » n’est pas un chemin d’accès valide. Assurez-vous que le nom du chemin d’accès est correct et qu’une connexion est établie avec le serveur sur lequel réside le fichier.
    Microsoft.Data.Mashup.ValueError.Reason:DataSource.Error


    Here is my connection string for another OLE DB connection which works (but sadly it's not a Foxpro database :( ). 

    Provider=PCSoft.HFSQL;initial catalog=*DB_name*;data source=*SRVAPP2*:4900;extended properties=""Language=ISO-8859-1;Password=*:------;""


    Best regards,

     

    Alexandre Wahl