Export data to a dbf file

[rollst]

I am running a query that selects some fields from a table and saves the values in another temporary table. The problem is that I want to export this table or the result set to a dbf file and then open it for editing in Microsoft Access. I know that SQL-DMO provides a way of exporting data from one table to another but I haven't seen any examples of exporting data to a dbf file. Is there any way of doing that with the SQL-DMO or SQL-DTS (or any other way)? Any help preferably with examples is welcome.

Thanks in advance
George

[Robert Moy]

Hello George:

If I understand you correctly, you do it this way. If copy data from Oracle table to Access with this code using ADO:

Private Sub Command1_Click()
On Error Resume Next
Do Until Adodc2.Recordset.EOF
If Adodc2.Recordset.BOF Then
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
Else
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
End If
Loop
If Adodc2.Recordset.EOF Then
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
End If
End Sub


Please reply back if this is not what you want

[rollst]

Basically, I have a table in SQL Server and all I want to do is to export the data of the table to a file with dbf format. The reason I want to do this is because I want to open the file in Access and create a shortcut to the dbf file to allow viewing and editing of the data. This code with the recordsets will work only if the file has been created. The problem is that I don't know how to create this (dbf) file. Any ideas? Thanks for your help in advance.

George

[Robert Moy]

Hello George:

I am sorry I do not know

Robert

[Tower]

Every start this procedure you need create dbf file?

[Tower]

Maybe you can use DTS Package?
In this Package you can create DBF file, export into this file any data from MS SQLServer.

[rollst]

Yes, I think that is the only way. Any examples or sample code that might help?

[Tower]

You can create DTS in Enterprise Manager.
1. Create DBF connection to needed catalog
2. Execute command for this connection "Create table test (test char(10))"
Comment: this is command create table test and column test in this table
3. Create connection to SQLServer
4. Create transformation data task from SQLServer to DBF table

This is all. If you have any question ask me.

[rollst]

Yes, basically I need to collect some data from one or more tables in SQL (through a query) and save it on a dbf file every time the query is executed. Is there any sample code available that might help?

thanks

[rollst]

I know the way to do it through Enterprise Manager but I need to do that in VB programmatically. I am using VB to connect to the server to run the query but I don't know how to export the result set to a dbf file. Any examples of how to do that in VB with DTS or SQL-DMO?

Thanks

[Un1]

Try this high performance DBF engine: (VB source code include)
http://www.banasoft.com/DownLoad/BNxBase.exe