I am currently in the process of converting some Access functionality to T-S
QL.
What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
manipulate data in a SQL table and then export that table to Excel.
Can anyone tell me if this is possible.Youc can create a linked server to a FoxPro DBC:
EXEC sp_addlinkedserver
@.srvproduct='',
@.server='VFP01',
@.provider='VFPOLEDB',
@.datasrc='C:\xyz.DBC'
(I'm assuming VFP7.0 or later with the OLEDB driver installed)
Then reference your Fox tables directly:
SELECT * FROM VFP01...table_name
For the export to Excel you'll probably have to use DTS, which is also
another option for the FoxPro data: import it rather than link it.
David Portas
SQL Server MVP
--|||Hi Chubbly,
Just to add, the FoxPro and Visual FoxPro OLE DB data provider is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates .
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Chubbly Geezer" <ChubblyGeezer@.discussions.microsoft.com> wrote in message
news:2AF3439C-E2E5-45C8-B5D2-1FFFF1B6CEC9@.microsoft.com...
>I am currently in the process of converting some Access functionality to
>T-SQL.
> What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
> manipulate data in a SQL table and then export that table to Excel.
> Can anyone tell me if this is possible.
>
>|||Thanks David, almost there I think. However with the following code I am
getting an error message. Also in Enterprise Manager my linked server is
showing no tables.
The VB ADO string I used to connect previously is this:
OperaConn.ConnectionString = "Driver=Microsoft Visual Foxpro Driver; UID=;
SourceType=DBC; SourceDB=i:\operaii\data\comp_W.dbc"
EXEC sp_dropserver 'OPERA_SERVER'
EXEC sp_addlinkedserver
@.server='OPERA_SERVER',
@.srvproduct='',
@.provider='VFPOLEDB',
@.datasrc='c:\temp\Comp_W.dbc'
Select * From OPERA_SERVER...ssale
--
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'OPERA_SERVER' does not contain table 'ssale'. The table
either does not exist or the current user does not have permissions on that
table.
Chubbly
"David Portas" wrote:
> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>|||That's worked actually thanks very much David.
The error I had was caused by the fact I copied the files I beleived I
needed to a temp directory for testing. There were obviously files missing
which I also needed.
Many thanks.
"David Portas" wrote:
> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>
