Excel: Visual FoxPro Database Connection results in 1 of the 2 tables not being visible using VFPOLEDB9.0 driver

Hi,

I am try to connect to a database created by a 3rd party application which stores historical info, in order to pull data from it to create custom reports not available in the program itself.  The database to my knowledge is a Visual Foxpro db, combination of .DBF (table) + .CDX (index).  There are 2 DBF's in the folder the program stores them in.  The list of files in the folder are:

RSMPMH3B.cdx
RSMPMH3B.DBF
RSMPMH3B.ser
RSMPMH3B.LCK
RCTHRHST.cdx
RCTHRHST.DBF

To connect I am using Excels > Data Connection Wizard > Other/Advanced > Microsoft OLE DB Provider for Visual FoxPro.  The connection params are:

ANSI = TRUE
Cache Authentication = False
CODEPAGE = 1252
Collating Sequence = MACHINE
DataSource = c:\files\are\in\here\
DELETED  = TRUE
DSN =
Encrypt Password = False
ENGINEBEHAVIOR =
Extended Properties =
Locale Identifier = 1033
Mark Password = False
Mode = Share Deny None
MVCOUNT = 16384
Password =
Persist Security Info = True
REFRESH = 5
REPROCESS = 5
TABLEVALIDATE = 3
User ID =
VARCHARMAPPING = False

The result is that only the RCTHRHST table is visible and selectable.  The question is why would the other table not also be visible to select?

Additional info in case it is relevant: The RSMPMH3B.DBF is 836MB.

Any help with this would be greatly appreciated.
LVL 1
AmbridioxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
The .DBF file extension does not ensure the correct file type and you should try to open the file by Visual FoxPro 9 first. If it opens without error message then you may test OLE DB access.

If you don't have Visual FoxPro available then you may try the functionally restricted run-time version:
1. Download and install VFP 9 SP2 runtime from http://www.foxpert.com/runtime.htm (you would need just these four files from this installation: msvcr71.dll, gdiplus.dll, vfp9r.dll, vfp9renu.dll - more info)
2. Download vRunFox 9 FoxPro application from http://leafe.com/dls/vfp

Execute vRunFox 9 and you'll see Command window in which you may enter FoxPro commands. The command to open DBF file is:

USE d:\path\YourFile

And if no error appears then your file is real FoxPro DBF... and VFP OLE DB provider should open it as well.
If an error appears then post it here please.

Additional commands you may try are:
BROWSE ... shows the open DBF file contents in a grid
SELECT * FROM YourFile WHERE somecondition  ... more advanced way of data retrieval
Help file for additional FoxPro commands and functions is available here: http://vfpx.codeplex.com/  (look for VFP 6 SP2 Helpfile)

836 MB means rather big file (FoxPro limit is 2GB) and the SELECT command can take very long time sometimes. (Result depends on available indexes and possible optimizations.)

Note: .SER and .LCK file extensions are not native to FoxPro. The possibility of the big DBF file encryption also exists - you could look at the DBF in some Hexadecimal editor and post the first block here.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmbridioxAuthor Commented:
Thanks pcelba,

I am in a work environment and have limited access to what can be installed without IT approval, the runtime would be an unlikely shot for them to install or for a hexviewer.  For the note regarding the ".SER and .LCK file extensions are not native to FoxPro.", I did some hunting and .ser appear to be primarily a Java associated filetype as well there is a JavaDBF library for reading and writing Xbase (dBase/DBF) files.  

I'm thinking maybe the application is using this to create/maintain the RSMPMH3B db while the other (RCTHRHST) is an actual VFP db...not sure though about the former. I can however open the RSMPMH3B in excel, it does not appear to be encrypted as I can see all the unreadable markup and readable headers/data in it, just in a completely unusable format.  The reason for the size is that it's continuous 30 days history of real-time data spanning a few thousand folk drilled down to seconds.

I think at this point the best approach is just going to be to use the internal report to pull a data dump into a csv and then just inject that into my own db that I can then do custom queries on.  Was just hoping to skip the extra step since as you know it's a rather large db and will be somewhat time consuming.
AmbridioxAuthor Commented:
Accepted as solution because it answers the question of why the 2nd DB would not show ".SER and .LCK file extensions are not native to FoxPro.", meaning the DBF is not in a VFP DB format so the VFPOLEDB9.0 driver cannot display it.
pcelbaCommented:
Yes, you are right the file can be prepared by whatever utility so the format compatibility is not guaranteed.

I could look at the DBF file format if you send let say the first 2 Kb from it. But you don't have a utility to split the file most probably... but you could ask your IT guys to do it for you... and then send it to may e-mail mentioned in the E-E profile.

Anyway, thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.