Newbie question about ODBC connection to SQL

[weigoldk]

I appologize if this is a double post--I'm having problems seeing seeing new posts.

I am BRAND NEW to FoxPro and need help with an ODBC connection.

I have the following:

1. An ODBC driver that I have working with MS Access.
2. An existing SQL database that is part of a software system used
3. Existing FRT files that were created for the program.
4. MS Visual FoxPro Version 9.0

I need the following
1. How to use FoxPro to make a brand new report using the ODBC driver I have (or help with making a driver if FoxPro can’t use the one I have).
2. Once I have the connection, how do I add in fields.
3. How to modify one of the existing FRT adding in more fields (how do I pick fields from the SQL database) and how to manage the field names (the existing field names don’t match the SQL database fields names).

Thanks in advance for your help and I welcome any clarification in what I wrote above.

 

[JRB-Bldr]

VFP uses the Windows ODBC DSN's.

It can either use an existing DSN or you can create a unique one to meet your specific needs.

Then VFP can utilize it in a couple of ways.

You can set up a Connection within a VFP database which is tied to the DSN. And then use that Connection to create a Remote View into the SQL Server table(s). This is one of the simplist approaches.

Once you have a Remote View in USE, it will behave basically like a 'native' VFP table.

One thing to note is that the field names within SQL Server tables are not limited in length as severely as VFP. Consequently if you use a Remote View within a VFP database, it will have the full field name.
If you create your own VFP query from the SQL Server table(s) the field names will be shortened down to 10 character field names.

Other methods include SQL Pass Through or ADO.

"Once I have the connection, how do I add in fields."
Add the fields?
The SQL Server table has the fields already defined.

Within VFP you can create your own SQL Query of the SQL Server table(s) and add new fields to the query results.

"How to modify one of the existing FRT adding in more fields"
Within the Report Form you merely add a new Textbox object and define it to display the expression you want - that can include field values or other info.

You have asked a number of questions
* How to use a Windows ODBC DSN within VFP
* How to modify a VFP Report Form
* Why do table field names change and what to do about it
* Etc.
and I hope that I have not muddied the water too badly with poor explanation.

Good Luck,
JRB-Bldr

 

[weigoldk]

JRB-Bldr,

Thanks--you didn't muddy the water a bit--it is much clearer now.

You have given me a great start.

Thanks again.

 

[craigber]

In addition to the VFP docs, here are some places to get started. Some of these articles are a bit dated and talk about some technologies that are no longer used (RDS for example), but still contain some good information.

http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_bko01.asp

http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_fpsqlcs.asp

http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_bko02.asp

http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_usevfp.asp

http://msdn.microsoft.com/library/en-us/dnfoxgen/html/adojump.asp

http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_embeddingmsde.asp

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

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[weigoldk]

Thanks Craig,

The only link that worked for me was the one to spacefold.com. I'll be reading that one tonight.

 

[JRB-Bldr]

Here are a few more references which might offer some help...

http://www.devarticles.com/c/a/SQL-Server/How-to-Connect-to-a-SQL-Server-from-Visual-FoxPro/

http://www.microsoft.com/technet/prodtechnol/sql/70/deploy/sqlfoxpr.mspx

http://www.foxdev.com/educs.php

Good Reference Book:

http://www.amazon.com/Client-Server-Applications-Visual-FoxPro-Server/dp/1930919018

Good Luck,
JRB-Bldr

 

[craigber]

Looks like Microsoft AGAIN screwed up links in the recent MSDN design. I'll complain to them about it... again.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog