Access Data Engine Can't Open FoxPlus file.

[gbcpastor]

Access Data Engine Can't Open FoxPlus file.

Our IT guy has installed a new software product on a machine that we create DBF's for. The new software uses ACCESS for data and we keep getting invalid database errors.

Our IT guy then converts the file to "FOXPLUS" somehow and it will then open. When I try to write the output files using "TYPE FOXPLUS" I still get the same errors.

What is the difference between type "FOXPLUS" and "FOX2"?

I really need to write tables that access can open.

 

[Mike Lewis]

This is all documented in the Help for the COPY TO command:

FOXPLUS
Visual FoxPro memo files have a different structure than Microsoft FoxBASE+™ memo files. If your source Visual FoxPro table contains a memo field, include the FOXPLUS clause to create a table that can be used in FoxBASE+. The Visual FoxPro memo field cannot contain binary data because FoxBASE+ does not support binary data in memo fields.

FOX2X
Creates a new table that can be opened in earlier versions of FoxPro (versions 2.0, 2.5, and 2.6).

So I expect the solution to your problem would depend on whether the tables have memo fields.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, the DBF file holds it's format specification in Byte 0, as described by the help:

https://www.vfphelp.com/vfp9/_5wn12pc0x.htm

Using COPY TO to create TYPE FOXPLUS and TYPE FOX2X, I get dbf files that both start with 3, which by the help reference means

0x03 FoxBASE+ / FoxPro /dBase III PLUS / dBase IV, no memo

If a memo is involved I get 131 for a foxplus with memo and 245 for a fox2x with memo. So there is a difference in that case.

Excel can also cope with normal vFP dbfs, if you use the VFP odbc driver to read them, not just load them as file type. That covers up to VFP6, using OLEDB provider you can also read in VFP9 dbfs.

And on the other side you could easily export data into a csv file and let Excel read that. Or look into creating XLSX files from within VFP:

http://praisachion.blogspot.com/2015/12/copytoxlsx-29.html

Chriss

 

[gbcpastor]

Hey Mike. I was hoping you would respond. I have been using "Copy to Newfile type FoxPlus" as the copy out command but that still is not working. I'm getting an error that says it's an invalid database.

 

[Mike Lewis]

Where are you getting that error? In the COPY TO command, or somewhere further downstream?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[gbcpastor]

There error is coming from a software called RAPTOR 10. It uses the access data engine to open data files. As a result, it chokes on Visual Foxpro files even when I copy them out using FoxPlus type.

 

[Chris Miller]

Sorry, I don't know how I got to Excel.

But likewise, ACCESS itself can use more than the JET engine to read data files. And there is a VFP ODBC driver supportng up to VFP6 DBFs and a VFP OleDB Provider supporting all VFP DBFs. As you can use Access, you can use it to import data and provide it to Raptor in Access formats.

Chriss

 

[Mike Lewis]

And just to be clear, is RAPTOR giving the same error when you copy to TYPE FOX2X?

Also, have you got access to Foxpro 2.x (for DOS or Windows), or to any other software (other than VFP) that can read Foxpro 2 files? If so, it would be helpful to know if that software can read the file that is causing RAPTOR to choke.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And just to be clear, is RAPTOR giving the same error when you copy to TYPE FOX2X?

Sorry. That was probably a stupid question. If FOX2X worked OK with RAPTOR, you wouldn't have needed to post your question in the first place.

But it would still be interesting to know if you have any other software which can read the same FOX2X file?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[gbcpastor]

Mike, I actually opened the fox2 file in a hex editor and checked the header and it was correct for what would be needed for the access data engine. That's why I'm having a hard time understanding this. I may have to go with a simple CSV output. Not the best option but may be the only viable option.

 

[Chris Miller]

checked the header and it was correct for what would be needed for the access data engine

Well, what is this first byte but a promise? Another example of insufficient compatibility is Excel 2007 outputs an Excel 97 compatible binary XLS format, yet VFP can't append from such files, but Excel 97 can open it. The question is: What kind of compatibility tests are actually made, and, well, the major intent of fo2x files is to be readable by legacy foxpro, not by the jet engine or other drivers that are capable to accept some dbf format.

You overlook the option to act from VFP and put data into anything else Raptor can easily access. It's simple to put data into Access databases, no matter if old mdb files or modern accessdb databases, because VFP can use ODBC and OLEDB providers and work on remote databases, also you can access all VFP dbf formats VFP9 supports and that is a lot including legacy formats - with the VFP OleDB provider.

One question I have is why your IT guy isn't here to ask what can be done, there are tons of possibillities aside of the nonworking ones you already tried and retried in every way. And actually VFP is bad in creating CSV as soon as you have to eport Memos.

Access can also make use of both ODBC or OleDB, it's just not possible to have a linked table via OleDB provider, but you can program VBA which uses ADO, which can use the VFP OleDB provider. You're focussing too much on the Jet engine as the only viable option. Of course you imit yourself to what that can do. You're underestimating your options. And since you're fine with making a dbf copy to fox2x format, you should be fine to not have a linked table access (i.e. updates on this table also are updates of the original dbf file and so data is actually shared, not exported/imported), so why not make use of something - anything - that Raptor can see. There should be tons of possibilities and insisting on the one you always used is not an option that brings you forward, obviously. What and how exactly VFP creates fox2x dbfs is not open source, i.e. there is no VFP code that you could mend to make it a jet compatibe dbf format, but who cares if there are other options? Get ralistic. Nobody here will dig into the file formats to know which bytes would need to be changed, I doubt it would even be just a fix of some bytes, you'''d need to write a whole export and then it's much easier to do that to export data inclduing memos to CSV.

Chriss