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 :(
Printable View
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 :(
Figured it out, this is my code:
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()
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:
Code: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
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.
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.
here 2 Sql for Import/Export to a AccessDB
Code:'Export from Access table to .Dbf
Dim sSQL As String
sSQL = "Select * Into [Test.dbf] In 'c:\test' 'dBase III;' From TableXYZ"
try dbase III or IVCode:'Import
sSQL = "Select * Into Table1 From [Test.dbf] In 'c:\test' 'dBase III;' "
hth
Hi fabio,
no need to send a PM, you can ask here
here a way to execute the Sql
hthCode: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
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 fineQuote:
Originally Posted by patel45
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'
if you look in the TestFolder folder you will see the dbase file but it will look like this 'MYTESTDB.DBF'Code:sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase III;' From tbl_Artikel"
or try in the Sql dbase IV
Code:sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase IV;' From
hth
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 DBQuote:
Originally Posted by patel45
Thank You for your time and your teachings