0

Currently I am trying to make a connection with a foxpro database using the python win32com module. The Python code currently looks like this:

    import win32com.client
    conn = win32com.client.Dispatch('ADODB.Connection')
    dsn = 'Provider=vfpoledb;Data Source=C:\MyDbFolder\MyDbContainer.dbc;'
    conn.Open(dsn)
    print('ok')

However it says that it could not find the provider; even-tough I have successfully installed the latest version of the Microsoft OLE DB Provider for Visual FoxPro 9.0 from the microsoft website.

'Provider cannot be found. It may not be properly installed.'

I have tried this both with python 32 bit as well as the 64 bit version on different pc's. If you use 32 bit python it works. However if one needs to use 64bit python, it seems that this should as well.

Did anyone get this working without issues?

foxprouser
  • 1
  • 1
  • Take a look at similar (and partially answered) question: https://stackoverflow.com/questions/56062925/register-a-32-bit-com-class-for-use-in-64-bit-python-without-administrator-right . I suppose that using 32bit COM-objects must be possible from 64bit python but not "without issues". – Alex Yu Jan 10 '20 at 10:53
  • To clarify things for other readers: there is no 64-bit VFPOLEDB provider, which means that 64-bit python needs a way to use the 32-bit provider somehow. Since a database container (DBC) is involved, chances of finding a working 3rd party provider are slim (as opposed to something that can read/write DBFs, be it concurrently with other users or not). – DarthGizka Jan 10 '20 at 12:36

1 Answers1

0

One possible workaround for the lack of a 64-bit VFPOLEDB driver might be setting up the VFP database as a linked server in a 32-bit instance of MS SQL Server (Express is free and should work). SQL Server 2014 seems to be the last version for which a 32-bit edition is available. There are plenty 64-bit OLEDB drivers for SQL Server, and they don't care about the bitness of the instance.

There are step-by-step instructions in How to successfully connect to Foxpro database files using MSSQL Linked Server feature and ODBC? over on ServerFault

Note: using Fox data via a linked server is severely limiting and nowhere near as powerful as using Fox directly or via VFPOLEDB. However, sometimes limited access is better than no access at all.

The queries have to use SQL server syntax and they are limited by it. For example, boolean fields get mapped to the bit data type (0 or 1) because SQL Server has no concept of booleans. But inside an OpenQuery call you can use full Fox syntax. Assuming the linked server is called FOX and table StoffPZN has a boolean field op:

select * from FOX...StoffPZN where op = 1;  -- T-SQL rules

select * from openquery(FOX, 'sele * from StoffPZN wher op');  -- Fox rules
DarthGizka
  • 4,347
  • 1
  • 24
  • 36
  • Thank you for your answer. However in our situation, we are not allowed to use MS SQL due to commercial reasons. Is it possible that there is a similar solution with a different database?. – foxprouser Jan 14 '20 at 10:35
  • @ foxprouser: I am not specifically aware of other engines that allow you to use an OLEDB data source as a 'queryable thing', so you'd have to go an a googling spree yourself. However, MS SQL Server *Express* is free and the [license terms](https://www.microsoft.com/en-us/download/details.aspx?id=29693) do not seem to impose any undue restrictions on commercial use. Also, there exist commercial OLEDB and ODBC drivers that would allow you direct access to Fox DBFs to varying degrees, like devart's [ODBC Driver for xBase](https://www.devart.com/odbc/xbase/). Definitely worth a Google and a try. – DarthGizka Jan 14 '20 at 11:14