new to FoxPro...in search of answers

In VFP9 SP2 (which is just went out) the Upsize Wizard is greatly improved.

"The client application has an interface to update the data. Is it possible to keep the same interface but change the backend db to SQL Server "

That part is hard to tell. Depends solely on how application is doing that. Probably not.

Best way to convert Foxpro data to SQL server, IMHO is doing that by hand (no wizards in between). Do not use ODBC driver but OLEDB driver. You'd need to massage structures because not all tables are directly convertible to SQL server (VFP's data structure might be in excess of maximum size for a row in SQL - in VFP it's 65000 which wouldn't be supported in SQL server).

You may start by creating a linked server and checking the tables (using Access in between is a bad idea IMHO).

Yes, correctly designed tables will have primary and foreign keys.

You don't need VFP to be installed to see the data. For seeing data on a windows computer, all you need is notepad + VFPOLEDB publicly available from MSDN vfoxpro site (Access in fact add complexity to the process). SQL server's linked server or OpenRowSet(), OpenDataSource() (latter two both need advanced options and ad hoc queries be enabled) are simply fantastic ways to not only see but get the data for bulk loading. Here is a sample:

EXEC master.dbo.sp_addlinkedserver

@server = N'MYVFPSERVER',

@srvproduct=N'My VFP Source',

@provider=N'VFPOLEDB',

@datasrc=N'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data'

select * from myvfpserver...customer

select * from

OpenQuery(myvfpserver, 'select cust_id, company from customer')

OpenQuery() lets you to do provider specific (VFP here) queries. For a discussion on it check recent threads.

Foxpro have primary and foreign keys but they are not mandatory. Developer might have chose to control all integrity through application code instead. Such data is not considered bad.

You didn't say anything about the language(s) you use but I sense it may be .Net. If so, then you can also use GetSchema() of the OleDbConnection class to query various schema items. And also could use SqlBulkCopy class for uploading data.