Connection to a Visual FoxPro Database

[Anonymous]

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. Firstly I tried using the basic ODBC and OLE DB Drivers, but they are both in 32 bit and I need to work with the 64 bit version of Power BI. Then I found the "Microsoft.ACE.OLEDB.12.0" Driver, which works, I can read my data in Power BI, but I can't properly use the data gateway and I need it.

I installed my driver on my server ( exactly like I did for all the others drivers I use) and when I try to create the data source for the gateway I have an error. I tried to read my data by intalling Power BI Desktop on the server and it worked, so I don't think that I have a connection problem between my computer and the server. I'm searching for a solution since 2-3 weeks and I didn't find any, the only good advice I found is to check if the ports are open, but I've no idea of which ports... (https://youtu.be/Y9Dc7jd5VaI)

I saw that there is a lot of drivers like Advantage Database from iAnywhere or Devart ODBC Driver but none of them work for me, maybe I do something wrong.

I hope that someone can help me and that everybody understood my problem, English isn't my first language, I did my best to explain this.

Best regards, 

Alexandre Wahl

[gabriel94mx]

Hi,

I have the same problem and i can't make the connection, do you have any news about this post?

[Baye]

Hi!

Same here, trying desperately to connect BDF files to PowerQuery or PBI.

I've tried everything possible in 64b without success. Any updates about this post?

Best,

B.

[gustavoleo]

@Anonymousthe only way that I found it works is with external driver go.helpbi.com/dbf

[gustavoleo]

@Anonymousthe only way that I found it works is with external driver(ODBC and 64-bits)  go.helpbi.com/dbf 

[v-juanli-msft]

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]

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