asked on

Making a subquery on a .dbf DB file from within a Query on a Microsoft SQL Server DB

I'm writing an app in vb6 to convert a db from .dbf format to a new SQL Server 2000 db and I'm having trouble with the data transfer...

To insert the data from the .dbf into the SQL Server I'm trying to use an INSERT INTO statement where the SELECT statement queries the particular .dbf file that is holding my data.

ie:
-- the connection to the SQL Server DB is already open...

INSERT INTO myTable (myNewfield1, ..., myNewFieldn)
SELECT oldField1, ... , oldFieldn
FROM myDBFTable

where myDBFTable is located in C:\dbfs\myTable.dbf for example. I've managed to do this exact thing from an Access DB, but I can't get it to work for the .dbf

This is my first go at something like this, any suggestions?
gpw

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gpw

ASKER

When I try it like this:

SELECT CREF,TI,NAME,P1+P2+P3+P4,TEL,TELCELL,FAX,EMAIL,VATREF,CBANK,CBBCH,CBTP,CBAC,ENTERED,SUCODE,UPDTIM
FROM OpenRowSet('MSDASQL','Driver=Microsoft Visual FoxPro Driver; SourceType=DBF;SourceDB=C:\TestData\DBFs\Cli.dbf', 'SELECT * FROM Cli')

I get an error message - from the Foxpro dirver that the file I'm looking for does not exist...

kalinma

Is it absolutely necessary to transfer the data via a VB program? Can you use the "Import Data . . ." function in SQL Server to transfer the data?

gpw

ASKER

I need to collate data from three different systems into the new db, one Access db and two separate dbf systems so I need the control I get from writing the app to do this. Although I've eventually got it working using angelIII's code suggestion I have decided to do the inserting on a row by row basis to give better error control.

Thanks guys - I'm learnin every day here!