Export Recordset to Foxpro Question

[jfield817]

Hi
Is there a quick way to dynamically and programatically Export/Convert a VB 5/6 recordset to a Foxpro table from
within VB ......without using a thir party tool


The recordsets will have varying structures ...
possibly 90 different types of recordsets ....

Thanks
John

 

[bjd4jc]

Yes there is an easy way to do this. You will need a reference to DAO:

Dim db As DAO.Database
dim stSQL As String

set db = OpenDatabase("Foxpro Path Goes Here",False, False, "dBase III;")

stsql = "SELECT * FROM TableName IN 'Your Access DB here' INTO NewTableName"

db.Execute stsql

You can change dBase III to any version of DBF files and I think there is an actual foxpro one too. You can look it up in the DAO help files.

 

[jfield817]

Thank you for ur fast response .....

The recordset I am referring to is created
by calling a stored procedure in a SQL Server
database ...(I should have mentioned that and that would
seem result in a different approach)...

The stored procedure returns data using a "Select"
I "capture" the data in a DAO recordset on the VB side.

Can the recordset be directly sent to a dbf file ...



Thanks
John

 

[bjd4jc]

I am not familiar with SQL Server at all but you will be able to do this logically.

It is too detailed for me to do right now but I can kind of give you an outline of how to do it.

You'll first need to create a table in the FoxPro database (connect how I showed in the prev. post). Then create a new table using either the SQL Create Table statement or the Tabledefs collection of DAO. You can find help on either by searching help.

You can get the SQL Server field types by doing this:
dbSQLServer.TableDefs("TableName&quot.Fields("FieldName&quot.Name
and .Type and so on and so forth.

Then loop through the SQL server table and add each record to the FoxPro table using either the AddNew method or using INSERT statements (more efficient).

Then you will have both tables.

Like I said, I know nothing about SQL Server (very little anyway) so there may be a better way of doing this... more directly.

I hope this helps. If you have specific questions, I am sure I can help.

 

[DonQuichote]

I think that in DAO you can use the createTable method of the database object (as shown by programmer bjd4jc) and use its result (a tabledef) to append to that database object. You can then open a recordset to the table.

There's some digging through helpfiles and examples, but it can be done. ;-)

 

[jfield817]

Everyone thank you very much for ur responses ....

I'm sure I can handle it from here ...

Thanks
John