VFP 7 .prg needs to read from SQL 2005 table 1

[sqleptical]

I've recently switched from using VFP tables to SQL 2005. I am now looking for a way to point my VFP 7.0 .prgs to the SQL tables instead of FoxPro.

For FoxPro, I simply pointed using the following command:

use "L:\tables\cif2.dbf" share

What do I need to replace this with to have the .prg read from a SQL 2005 query? Is there any other code I need to use to close the connection that I did not need with FoxPro?

Currently I am having to export the tables from SQL to FoxPro in order to run them, but would like to avoid these extra steps. I looked through FAQs & other topics which gave me some ideas, but nothing that has worked. Thank you!

 

[JRB-Bldr]

There are a number of approaches.

One approach that I use is to set up a Database containing Remote Views utilizing ODBC Connections into the 'alien' database. I can make the Remote View query as complex as I want as long as the SQL commands are supported in the 'alien' database environment (some VFP SQL commands are not).

Then when you want to work with table:

OPEN DATABASE MyDBC
USE SQLTable IN 0  && SQLTable is name of Remote View
SELECT SQLTable
<work with the table as usual>
USE

One thing to keep in mind is Table Buffering. There are a number of posts on Buffering going into detail on the Pros & Cons of Table or Record buffering. Search for these postings to determine which will work best for you.

Good Luck,
JRB-Bldr

 

[sqleptical]

Thank you, JRB-Bldr. I should have mentioned I did not know how to create a remote view. Once I figured this out, your code worked great!

If anyone needs a tutorial on crieating views the link below helped me:

http://www.spacefold.com/colin/archive/articles/sql_backend/AppendixA.htm

Also some good info on writing VFP apps for multiple back-end databases:

http://www.spacefold.com/colin/archive/articles/sql_backend/backend.htm

Thanks again!

 

[sqleptical]

Thank you JRBBLDR! I should have mentioned that I did not know how to make a remote view. Once I figured this out, your code worked. Thanks again!