How do I export a datatable to a .dbf file?

[Christhemist]

I have a datatable that I want to export to a .dbf file type. Everything I've found on the internet has not been helpful at all

[Christhemist]

Figured it out, this is my code:

Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)

            dBaseConnection.Open()

            'New table
            Dim SQLCreateCommand As String
            'SQLCreateCommand = “CREATE TABLE NewTable ” & “(subdir TEXT(50))”
            Dim NewString As String = dt.ToString
            SQLCreateCommand = "SELECT * INTO NewTable From " & TableName
            Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(SQLCreateCommand, dBaseConnection)

            dBaseCommand.ExecuteNonQuery()
            dBaseConnection.Close()

[Christhemist]

Several years later, I have found a better way of doing this using the FastDBF lib reference found here: https://github.com/SocialExplorer/FastDBF

My previous solution required that a dbf with the same column structure already exits. The solution below creates a dbf with the columns that exist in the DataTable:

Private Sub ExportListButton_Click(sender As Object, e As EventArgs) Handles ExportListButton.Click

        Dim ExportedDbf = New DbfFile(Encoding.GetEncoding(1252))
        ExportedDbf.Open(Path.Combine("C:\WORK\A_TEST", "Combined.dbf"), FileMode.Create)

        For Each col In CombinedDataTable.Columns
            ExportedDbf.Header.AddColumn(New DbfColumn(col.ToString, DbfColumn.DbfColumnType.Character, 50, 0))
        Next

        Dim Counter As Integer = 0

        For Each row In CombinedDataTable.Rows

            Dim ColumnCounter As Integer = 0
            Dim NewRec = New DbfRecord(ExportedDbf.Header)

            For Each col In CombinedDataTable.Columns

                NewRec(ColumnCounter) = CombinedDataTable.Rows(Counter)(col).ToString
                ColumnCounter = ColumnCounter + 1
            Next

            ExportedDbf.Write(NewRec, True)
            Counter = Counter + 1

        Next

        ExportedDbf.Close()

    End Sub

[fabiopoles]

I just red your interesting post.
I have following situation: an Access 97 machine needs to take data from an Access2019 db. Unfortunately there is no way to upgrade the A97 machine.
I would like to write a VB code to create from A2019 a .dbf file to link then inside A97.
First question: do you see any better solution for the A97 machine?
If "No" how can I implement your above solution? I am not a programmer and I am neither aware how to install the FastDBF lib in Access2019.
Thanks for your kind help in advance.

[Shaggy Hiker]

I think you would be better off starting a new thread with this question worded a bit differently. The solution shown above is an explicit programming solution. Installing a library likely isn't going to help you any if you aren't a programmer, as it likely won't do the work on its own. That particular library gives programmers tools to use for working with a database, and doesn't appear to have any utility for non-programmers. If you aren't looking for a programming solution, you might start a thread in Office Development or General PC, and lead off with not looking for a programming solution. On the other hand, if you are comfortable with a bit of coding in something like VBA, then Office Development would be the right place.

[ChrisE]

here 2 Sql for Import/Export to a AccessDB

'Export from Access table to .Dbf
Dim sSQL As String
 sSQL = "Select * Into [Test.dbf] In 'c:\test' 'dBase III;' From TableXYZ"

'Import

sSQL = "Select * Into Table1 From [Test.dbf] In 'c:\test' 'dBase III;' "

try dbase III or IV

hth

[ChrisE]

Hi fabio,

no need to send a PM, you can ask here

here a way to execute the Sql

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=D:\Db2010.accdb")
        con.Open()

        'Import from .dbf to Access table:
        'sSql = "Select * Into Table1 From [Kunden.dbf] In 'D:\Testfolder' 'dBase III;' "
        'ExecuteSQL(con, sSql)


        'Export table from Access to .dbf:
        sSql = "Select * Into [Test.dbf] In 'D:\Testfolder' 'dBase III;' From tbl_Artikel"
        ExecuteSQL(con, sSql)

        con.Close()
        con = Nothing
    End Sub

  Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                ByVal sSQL As String, _
                                Optional ByRef ErrMessage As String = Nothing, _
                                Optional ByVal TransAction As  _
                                OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

hth

[patel45]

Hi ChrisE, your code runs very well for accdb to dbf, but not viceversa, I don't receive error messages, but no accdb file.
Maybe you have to change the connection string ?

[ChrisE]

Hi ChrisE, your code runs very well for accdb to dbf, but not viceversa, I don't receive error messages, but no accdb file.
Maybe you have to change the connection string ?

I did just check and it works fine

what .dbf File are you trying to Import
a note .. the Dbase file should not have >8 characters
try it and Export a Accesstable to dbase with the name 'myTestDbasefile'

        sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase III;' From tbl_Artikel"

if you look in the TestFolder folder you will see the dbase file but it will look like this 'MYTESTDB.DBF'


or try in the Sql dbase IV

  sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase IV;' From

hth

[patel45]

OK CrisE, I did not understand the following:
when I convert accdb to dbf I create the dbf file, when I convert dbf to accdb the accdb file must already exist, I add a table, I don't create a new file.

[ChrisE]

OK CrisE, I did not understand the following:
when I convert accdb to dbf I create the dbf file, when I convert dbf to accdb the accdb file must already exist, I add a table, I don't create a new file.

the Access DB must exist, with the Sql you create a new Table in the Access DB

[patel45]

Thank You for your time and your teachings