Visual FoxPro SQL Queries - Linking to FoxPro files from SQL Server

Author

Linking to FoxPro files from SQL Server

Teri Welch

2006-03-10, 1:24 pm

Hello,

We are fairly new to SQL Server and and hope someone can help us. From SQL
Server we need to link to FoxPro tables in the accounting (SBT) system. We
started to setup a Linked Server but didnt get very far. For example if we
wanted to link to a FoxPro Product table called PROD we would see the
following files to choose from: PROD.DBF, PROD.CDX, and PROD.ADX). Assuming
the FoxPro files are located in C:\DATA how can we accomplish the linking?

1) Would we want to use the ODBC driver or the Visual Foxpro driver to link?
The FoxPro version is 5 years old.

2) Based on the information given above, what are the specific parameters
needed to create the linked server?

3) Once a link is established, can we filter records from the underlying
FoxPro table because it contains hundreds of thousands of records we dont
need to access.

Thanks,
Teri

Cindy Winegarden

2006-03-10, 1:24 pm

Hi Teri,

Download and install the latest FoxPro and Visual FoxPro OLE DB data
provider from msdn.microsoft.com/vfoxpro/downloads/updates.

I'm using SQL 2005; your dialog may be a little different. For a linked
server in SQL Server use the following:

Linked Servers > New Linked Server ...

Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro"
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Nort
hwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)

Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.

A linked server acts pretty much like any other SQL table - the data is not
"imported" so you won't get all the records you don't want if you filter
them out using your Where clause. To select data use :

Select * From MyLinkedServer...TableName Where Record <> OnesIDon'tWant

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Teri Welch" <trwlch@yahoo.com> wrote in message
news:e2jQf.60378$Ug4.10809@dukeread12...
> Hello,
>
> We are fairly new to SQL Server and and hope someone can help us. From SQL
> Server we need to link to FoxPro tables in the accounting (SBT) system. We
> started to setup a Linked Server but didnt get very far. For example if we
> wanted to link to a FoxPro Product table called PROD we would see the
> following files to choose from: PROD.DBF, PROD.CDX, and PROD.ADX).
> Assuming
> the FoxPro files are located in C:\DATA how can we accomplish the linking?
>
> 1) Would we want to use the ODBC driver or the Visual Foxpro driver to
> link?
> The FoxPro version is 5 years old.
>
> 2) Based on the information given above, what are the specific parameters
> needed to create the linked server?
>
> 3) Once a link is established, can we filter records from the underlying
> FoxPro table because it contains hundreds of thousands of records we dont
> need to access.
>
> Thanks,
> Teri
>
>
>
>

Lee Mitchell

2006-03-10, 1:24 pm

Hi Teri:

Take a look at these articles. It should help you get started:

207595 How to do SQL Server 2000 distributed queries with FoxPro .dbf files
http://support.microsoft.com/defaul...kb;EN-US;207595
199131 How to add a Visual FoxPro database to SQL Server as a linked server
http://support.microsoft.com/defaul...kb;EN-US;199131
203638 How to return information about SQL Server linked servers
http://support.microsoft.com/defaul...kb;EN-US;203638

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>Hello,

>We are fairly new to SQL Server and and hope someone can help us. From SQL
>Server we need to link to FoxPro tables in the accounting (SBT) system. We
>started to setup a Linked Server but didnt get very far. For example if we
>wanted to link to a FoxPro Product table called PROD we would see the
>following files to choose from: PROD.DBF, PROD.CDX, and PROD.ADX). Assuming
>the FoxPro files are located in C:\DATA how can we accomplish the linking?
>
>1) Would we want to use the ODBC driver or the Visual Foxpro driver to
link?
>The FoxPro version is 5 years old.

>2) Based on the information given above, what are the specific parameters
>needed to create the linked server?

>3) Once a link is established, can we filter records from the underlying
>FoxPro table because it contains hundreds of thousands of records we dont
>need to access.

>Thanks,
>Teri

Sponsored Links