Creating a View linked to a VFP table

I have a SQL database and I need to link to a VFP table. I have been told
that a view is what I am needing.
Can someone lead me in the right direction of creating and using a view to a
VFP table?Hi Preacher,
Lee's mentioned a "Local" view but I have a different take on what you're
after.
Are you working in VFP and want to get data from an SQL Server database? You
would use a VFP Remote View for that. Once you've USEd the view it will
behave like a Fox table and you can do things like indexing it and setting a
relation to other open Fox tables or cursors.
Or, are you working in SQL Server and wanting to open a Fox table as if it's
a SQL Server table? For that you need a SQL Server "Linked Server." To do
this you'll need to have the VFP OLE DB data provider installed. It installs
with VFP, and is also downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates.
The actual setup differs a little by which version of the SQL Server IDE
you're working in, but in SQL 2005 you can do this:
Open your SQL Server in the Object Explorer. Navigate down the tree to
Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
Providers list. Right-click and choose New Linked Server... . In the dialog
that comes up fill in something like the following:
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\Northwind\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.
To select data from your linked server use the 4-part naming convention,
although the second and third items will be missing. Code looks like the
following:
Select * From MyLinkedServer...Customers
Select A.Field1*, B.Field1
From SomeSQLTable A
Left Join MyMyLinkedServer...Customers B On
A.Whatever = B.Whatever ......
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>I have a SQL database and I need to link to a VFP table. I have been told
> that a view is what I am needing.
> Can someone lead me in the right direction of creating and using a view to
> a
> VFP table?
>|||That is correct. I am trying to pull from VFP into SQL. I tried your
instructions below, and I can't seem to follow them with SQL 2000 which is
what I am using. What needs to be different?
Thanks.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:%23MJpAW3VGHA.5232@.TK2MSFTNGP11.phx.gbl...
> Hi Preacher,
> Lee's mentioned a "Local" view but I have a different take on what you're
> after.
> Are you working in VFP and want to get data from an SQL Server database?
> You would use a VFP Remote View for that. Once you've USEd the view it
> will behave like a Fox table and you can do things like indexing it and
> setting a relation to other open Fox tables or cursors.
> Or, are you working in SQL Server and wanting to open a Fox table as if
> it's a SQL Server table? For that you need a SQL Server "Linked Server."
> To do this you'll need to have the VFP OLE DB data provider installed. It
> installs with VFP, and is also downloadable from
> msdn.microsoft.com/vfoxpro/downloads/updates.
> The actual setup differs a little by which version of the SQL Server IDE
> you're working in, but in SQL 2005 you can do this:
> Open your SQL Server in the Object Explorer. Navigate down the tree to
> Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
> Providers list. Right-click and choose New Linked Server... . In the
> dialog that comes up fill in something like the following:
> 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\Northwind\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.
> To select data from your linked server use the 4-part naming convention,
> although the second and third items will be missing. Code looks like the
> following:
> Select * From MyLinkedServer...Customers
> Select A.Field1*, B.Field1
> From SomeSQLTable A
> Left Join MyMyLinkedServer...Customers B On
> A.Whatever = B.Whatever ......
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "Preacher Man" <nospam> wrote in message
> news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>|||Hi Preacher,
I'm really sorry I don't have the SQL 2000 IDE available to know how to walk
you through a Linked Server setup using the GUI interface. In any case you
can create it with code like this:
-- Master should be the active database
Use Master
Go
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND',
@.srvproduct=N'Visual FoxPro 9',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"',
@.provstr=N'VFPOLEDB.1'
Did you know that you can use the SQL 2005 GUI with an SQL 2000 database?
Don't be confused by the OpenRowset(), OpenQuery() etc. methods used in the
second article Lee posted in the VFP group.
I despise them. I have to use them with some Oracle data I access. The
4-part naming convention (MyLinkedServer...Customers) is so much more in
keeping with the familiar format of Select statements.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eTlGlQ$VGHA.4308@.TK2MSFTNGP12.phx.gbl...
> That is correct. I am trying to pull from VFP into SQL. I tried your
> instructions below, and I can't seem to follow them with SQL 2000 which is
> what I am using. What needs to be different?