vfp to ms sql

You have a number of ways to access your MySQL tables from VFP.

Remote View
Within a VFP Database, create a Connection which will utilize your DSN
Then create a Remote View (updateable or not) to your MySQL table(s) using the Connection
Then have your VFP application use that Remote View

SQL Pass-thru
Open the previously described VFP Database
Execute a SQLConnection() to acquire a ConnectionHandle utilizing the Connection defined within the VFP Database
Build a SQL Command which is consistent with the MySQL database language
Use the ConnectionHandle in a SQLExec() to execute your SQL command

DSN-less
Use a fully defined Connection string (look at:  http://www.connectionstrings.com/mysql )
Acquire a ConnectionHandle using SQLSTRINGCONNECT()
Build a SQL Command which is consistent with the MySQL database language
Use the ConnectionHandle in a SQLExec() to execute your SQL command

There are a lot of detailed examples available through a Google search for:
    vfp sqlexec()
    vfp sqlconnection
    vfp connection
    vfp dsn-less connection

Or other similar search strings

Good Luck

The connection is a ms sql connection. I really need a more dummies approach

To Connect
nSQL = SQLConn()

FUNCTION SQLConn
cSQL = "DRIVER=SQL Server"
cSQL = cSQL + ";SERVER=1.2.3.4"
cSQL = cSQL + ";TRUSTED_CONNECTION=No"
cSQL = cSQL + ";DATABASE=MyMSSQLDatabase"
cSQL = cSQL + ";UID=username"
cSQL = cSQL + ";PWD=password"
? "Connecting ..."
nSQL = SQLSTRINGCONNECT(cSQL)
IF nSQL > 0
	? "Connected"
ELSE
	? "Not Connected"
ENDIF
RETURN nSQL

Select allOpen in new window

To get records from a table

= SQLEXEC(nSQL,"SELECT * FROM Customers WHERE state='CA'" ,'customersincalifornia')

Select allOpen in new window

Above short samples should be completed by more comprehensive information...

A list of SQL Pass-Through functions:
http://msdn.microsoft.com/en-us/library/ad8d6efe(v=vs.80).aspx

If you decide to use remote views:
http://msdn.microsoft.com/en-us/library/hf52bkf7(v=vs.80).aspx

Another way is to define Cursoradapter:
http://www.redware.com/handbooks/foxprocursoradapter.html
http://www.code-magazine.com/Article.aspx?quickid=0301062

And appropriate pages are on FoxPro Wiki, as well:
http://fox.wikis.com/wc.dll?Wiki~SQLPass-Through
http://fox.wikis.com/wc.dll?Wiki~CursorAdapter
http://fox.wikis.com/wc.dll?Wiki~RemoteViews~Wiki
http://fox.wikis.com/wc.dll?Wiki~RemoteViewFAQ~VFP

And finally you may study some books from Hentzenwerke which are perfect resource of knowledge for FoxPro community. The SQL data access is discussed in http://www.hentzenwerke.com/catalog/csvfp.htm and even when it is written for SQL 7 the info is still valid.

BTW, I am using SQL Pass-Through as the cleanest way of SQL data access for programmers. Everything other (remote views and Cursoradapters) brings one or more levels of internal complexity to the data retrieval process which is always more difficult to maintain.

You could also use ADO to access SQL data:
http://msdn.microsoft.com/en-us/library/ms917355.aspx
http://msdn.microsoft.com/en-us/library/aa905875(v=sql.80).aspx
but if I remember it well Microsoft will stop OLE DB support in next MS SQL Server version.

If you don't want to browse and read through above mentioned articles and help topics: It's a three liner. You can use your DSN via SQLConnect() and then need SQLExec and SQLDisconnect.

The other approaches, especially Cursoradapters, have their advantages over SQLExec, but need more learning first. It pays.

But for a quick start just these three lines will give you data in a foxpro cursor:
lnH=SQLConnect("DSNName")
SQLExec(lnH,"Select * From Table","curResult")
SQLDisconnect(lnH)

This isn't limited to MSSQL, you can use any DSN to any database offering an ODBC driver. You use that databases SQL dialect, so here T-SQL.

The disadvantage of SQLExec incomparson with other techniques is, it needs more lines to prepare a result cursor to enable foxpro to write back changes to the remote database server, which is easier using a CA builder or it's properties, but needs more preparation.
The three liner above gives you a feel of what you get from SQL Server and how fast or slow that may be in comparison to USE some.dbf.

You will need to redo most of your programming, it's not just simply done by wrapping sqls into SQLExec calls. You work disconnected, you fetch some data and then do stuff client side and then need to store back changes.

Bye, Olaf.

If you really need a detailed instruction 'manual' you might want to look at:
    Client-Server Applications with Visual FoxPro and SQL Server 7.0
    http://www.hentzenwerke.com/catalog/csvfp.htm

Some of the information is a little dated due to version changes since publication time, but in general, it gives you the details of how to get your VFP application working with a SQL Server 'backend'.

Good Luck