have some issues query from excel 2007, selecting a VFP table

[titoneon]

Hi,
I am trying to create a query, from excel by connecting to a vfp table, but it seen that i don't have a proper vfp drivers for it
so i tried to add it but don't see how to get to it, i thou that in windows 7 pro 64bit, i was able to browse and find and select the vfp drivers so then, i can create a new DSN and assign the driver there, can anyone allow me a link where i can find that driver and how to install it so i can create a new DSN and be able to select the corresponding driver ?
Thanks

 

[Mike Lewis]

Are you looking for an ODBC driver, or for an OLE DB provider?

The ODBC driver only works if your VFP database was created in VFP 6.0 or earlier. (And not if it tables were added, deleted or had their structures altered in a later version.)

Assuming you are are using a more recent version of VFP, don't bother with the ODBC driver. Instead, go for the VFP OLE Provider. If you haven't already done so, you can download it from the Microsoft site.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Hi Mike,
Well actually this computer user, has OS windows 7 pro 64 bit and running Ms Excel 2007, he wants to be able to use the Ms query capabilities in excel to create a query by accessing a vfp table that was created in VFP 5.0, ok when i am trying to access that table, i am not able to access that vfp table cause i am missing a driver, i was trying to create a New "USER DSN" and then link that one to the correct vfp driver, but it is not in that machine, so i guess all i need to do is download/install the VFPOLEDBSETUP.MSI and the VFPOLEDB.MSM in that machine, and i guess then i will be able to ADD a USER DSN and associate to it the vfp driver that i guess i will be able to select from the drivers tab, correct ?
Thanks

 

[Olaf Doschke]

1st problem: If you install VFP itself, that doesn't install VFP drivers for other languages, as you don't need them from the VFP perspective.
2nd problem: MS doesn't offer the VFP ODBC driver for download anymore. It's a version 6 driver, as Mike already said, so it's out of support for a very long time, already.
3rd problem: If Excel is from the 64bit Office, you won't see 32bit drivers, even if you installed them separately.

> i guess all i need to do is download/install the VFPOLEDBSETUP.MSI and the VFPOLEDB.MSM in that machine, and i guess then i will be able to ADD a USER DSN and associate to it the vfp driver that i guess i will be able to select from the drivers tab, correct ?

No, DSNs are an ODBC concept, you can't create OLEDB DSNs. OLEDB Providers also don't appear in the drivers tab of the ODBC manager. Indeed the OLEDB Provider is the latest and only driver you get, but it's not usable from concepts basing on ODBC.

MS Query is ODBC based, you can't use it with OLEDB Providers. In Excel, one menu item above the ms query option there is the option for OLEDB, but the OLEDB Provider won't appear there, if it's excel 64bit.

Some say the oledb provider will only be installed correctly, when starting it from cmd.exe started with administrative privileges. I say that is nonsense, as any setup will require you to allow elevating with admin login anyway. You may still try that, but it also will not help, if Excel 64bit is used.

Bye, Olaf.

 

[GriffMG]

Mike I didn't know that about the ODBC driver - not working with databases after VFP 6.

I knew it was not a 'safe' option for updating tables etc, because it's not multi threaded (I think).

With regard to the OLEDB - this is pretty reliable and easy to install, I always do it logged in
as an administrator - so there is no doubt regarding the access privileges - but I am not sure you need to.

For 64bit versions of Windows, you do have to be sure that the vfpoledb.dll is in a folder like this:

C:\Program Files (x86)\Common Files\System\Ole DB\vfpoledb.dll

That seems to work for me.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

Griff,

even logged in as admin you don't start processes with admin privbileges, that UAC in action, since Vista. Admins are not admins. Even as admin you always are asked to confirm setup actions, that's the only convenience being logged in as admin, you don't need to repeatedly enter your password in elevation dialogs.

The only reason the install may fail is, if the Setup starts further processes, because they are again not elevated. In contrast, if you start something from cmd.exe started as admin (that will display in the title bar) all further processes started from there or started by started processes will also have administrative elevation.

The VFP6 ODBC Driver won't be able to knwo tables with autoinc, varchar, vabinary and other new field types, that's a fact. You are able to access vfp7,8,9 generated tables, unless they use newer features. But that's surely not problematic here, as titoneaon already said it's VFP5 tables.

My advice would be installing Excel 32bit, then use the ODBC driver. It might not be an Option for titeons customer, as Excel 6bit indeed has the Advantage to work on xlsx files larger than 2GB. But there is no reason to use Excel 64 bit otherwise, if you are still attached with the 32bit world, eg These VFP5 tables.

Bye, Olaf.

 

[Mike Lewis]

Ernesto,

If you don't manage to solve this problem, keep in mind that you have another option. Instead of using Excel to query a VFP table, you can go in the other direction. You can use Automation in a VFP program to move data from your table into a worksheet.

That might not fit in with what you are trying to achieve, but it might worth considering as a possibility.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads