Version Question
Hi There --
I have an Access 2003 database connected via Visual Foxpro ODBC to a database called dbCAMS.
Everything is working **okay** -- I've got occasional trouble with the largest table in the foxpro database, 500,000 + records, where queries occasionally hang on forever and cause errors.
I was doing a bit of research and read
(https://www.experts-exchange.com/questions/22489420/Union-query-of-Foxpro-tables-from-MS-Access-returns-inconsistent-results.html)
which lead me to wonder if I am using the correct driver to talk to this database. In this article, they mention that if you have a foxpro version 9 database, you should be using the visual foxpro ole db provider as opposed to the odbc driver.
Question: Am I reading this correctly? what's hhe difference between the visual foxpro odbc driver and the visual foxpro ole db provider? Also, how do I determine the version of foxpro being used in dbCAMS? Am I using the correct ODBC link for this database?
My goal is to boost performance and reduce errors in working with this foxpro database. Any advice would be most helpful.
I have an Access 2003 database connected via Visual Foxpro ODBC to a database called dbCAMS.
Everything is working **okay** -- I've got occasional trouble with the largest table in the foxpro database, 500,000 + records, where queries occasionally hang on forever and cause errors.
I was doing a bit of research and read
(https://www.experts-exchange.com/questions/22489420/Union-query-of-Foxpro-tables-from-MS-Access-returns-inconsistent-results.html)
which lead me to wonder if I am using the correct driver to talk to this database. In this article, they mention that if you have a foxpro version 9 database, you should be using the visual foxpro ole db provider as opposed to the odbc driver.
Question: Am I reading this correctly? what's hhe difference between the visual foxpro odbc driver and the visual foxpro ole db provider? Also, how do I determine the version of foxpro being used in dbCAMS? Am I using the correct ODBC link for this database?
My goal is to boost performance and reduce errors in working with this foxpro database. Any advice would be most helpful.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
CarlWarner
Of course you're not using the VFP executable if you're simply using MS Access to try and yank some data out of there. But, certainly there must be a VFP executable that someone is using or has used that would tell you what version of VFP was used to initialize and maintain that data.
Supposedly, Advantage has offered up an ODBC driver that supports most everything (not binary indexes) in VFP9.
You may want to try it if you're dead set on using the ODBC driver from Access to VFP.
Available Downloads for Advantage ODBC Driver Release 9.0 for Windows
http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=12&Product=14&Platform=6
You may want to try it if you're dead set on using the ODBC driver from Access to VFP.
Available Downloads for Advantage ODBC Driver Release 9.0 for Windows
http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=12&Product=14&Platform=6
ASKER
Hi Carl --
I could not get an "open with..." option in opening the executable, but I did see in the same folder as the executable references to VFP 9.0... I found a "foxhhelp9" file for html help support, version 9.0, and I found several dll files in the folder related to version 9.
If this database IS running version 9, then I should be using the VFP OLE DB Provider file as the ODBC connection? Do you set it up in the same way as the standard ODBC's (Data Sources - control panel). How exactly is it set up? I currently have the ODBC driver set up as a System DSN, Free Table Directory.
Thanks a bunch.
I could not get an "open with..." option in opening the executable, but I did see in the same folder as the executable references to VFP 9.0... I found a "foxhhelp9" file for html help support, version 9.0, and I found several dll files in the folder related to version 9.
If this database IS running version 9, then I should be using the VFP OLE DB Provider file as the ODBC connection? Do you set it up in the same way as the standard ODBC's (Data Sources - control panel). How exactly is it set up? I currently have the ODBC driver set up as a System DSN, Free Table Directory.
Thanks a bunch.
From what I gather, Access won't allow you to set it up as easily as an ODBC driver and that the OLE DB Provider for VFP must be handled programmatically starting with an easily built connection string, etc. There is no control panel area for OLE DB Providers and it is treated more like a DSN-less ODBC driver connection.
Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
Visual FoxPro / FoxPro 2.x
This is a compiled connection strings reference list on how to connect to Visual FoxPro / FoxPro 2.x.
http://www.connectionstrings.com/?carrier=visualfoxpro
Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
Visual FoxPro / FoxPro 2.x
This is a compiled connection strings reference list on how to connect to Visual FoxPro / FoxPro 2.x.
http://www.connectionstrings.com/?carrier=visualfoxpro
The easy and crude way to see all OLE DB Providers installed on your PC allowing you to interactively build a connection string to any of those providers:
Right-click the desktop.
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
Right-click the desktop.
Select New>Text Document
Change the name to LookatOLEDB.UDL (note the extension).
Tell Windows "HELL YES!" when it warns about changing the file extension.
Now, DblClick that puppy and check the Providers tab.
You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.
ASKER
Thanks for all the info Carl !
One last question: I have never used a connection string before. Where do you build it? The link you supplied shows me the syntax, but where do you put it?
One last question: I have never used a connection string before. Where do you build it? The link you supplied shows me the syntax, but where do you put it?
I am sorry that I am not proficient enough on the Access side to answer that properly. While ODBC data sources can be linked to through and External Database menu choice, it is my understanding the OLE DB Provider connection strings, as well as the DSN-less ODBC connection strings, must be run programmatically in the Access environment to take effect.
ASKER
Thanks for your help, Carl... I'll research setting up that string in VBA, or will post another question.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
