Converting an Access table into .DBF

[anonymous]

[Originally posted by Mr. D]

Hello.

Is it possible to convert individual tables of an Access .MDB file into corresponding .DBF files through VB code? My program requires that Access tables be converted before sending them thru e-mail to a specified destination. If for instance, I have the following tables (with data already) in my .MDB file:

Table1
Table2

can these be converted into, say, Table1.DBF and Table2.DBF (or any specified filename) which can then be opened and read by a dBase or Clipper databse utility program? If possible, how can the structure or the data type of field names be preserved? How about if the tables have one or more indexes? Can these be converted also into .NTX (Clipper) or .NDX (dBase) files?

Thanks for the info in advance!

[anonymous]

[Originally posted by Stephan]

it's easy !
go to references, add MS Access 8.0 Object Library and create an instance to "Access" and you can use all features like in Access to export all your tables in different formats (dbase, excel, Text and so on)

[anonymous]

[Originally posted by Mr. D]

Thank you for the reply. Actually, I haven't gone that far yet in VB. Will you be able to show me in code the things that you are suggesting?

[anonymous]

[Originally posted by Stephan]

Hi, here my answer ( just one possibility ),
any comments to
stephan.lehmann@planet-interkom.de

also a good tip, look in \SAMPLE-directory VISDATA.VBP


ÿ ' Export Table from MDB-File into
ÿ ' DBF-File-Format
ÿ ' for dBase/Clipper
ÿ
ÿ Dim mdb_Table As String
ÿ Dim dbf_Table As String
ÿ Dim sql_statement As String
ÿ Dim path_target As String
ÿ Dim dbf_target As String
ÿ
ÿ ' declare Database-Object
ÿ Dim MyDB As Database
ÿ
ÿ ' Source MDB-File
ÿ mdb_Table = "C:\allpst\allpstneu.mdb"
ÿ
ÿ ' Table in MDB-File
ÿ dbf_Table = "plan"

ÿ ' Target-directory
ÿ path_target = "C:\test"
ÿ
ÿ ' Target-DBF-File
ÿ dbf_target = "newdbf"
ÿ
ÿ ' SQL-Statement
ÿ sql_statement = "SELECT * INTO [dBase IV;database=" & path_target & "]." & dbf_target & " FROM " & dbf_Table
ÿ 'sql_statement = "SELECT * INTO [dBase IV;database=C:\test].newdbf FROM person"
ÿ ÿ ÿ
ÿ ' open the MDB-Database
ÿ Set MyDB = DBEngine.OpenDatabase(mdb_Table)
ÿ
ÿ ' delete DBF-File before "copying"
ÿ If UCase(Dir(path_target & "\" & dbf_target & ".dbf")) = UCase(dbf_target & ".dbf") Then
ÿ ÿ Kill path_target & "\" & dbf_target & ".dbf"
ÿ End If
ÿ
ÿ ' SQL-Statement and transfering data
ÿ MyDB.Execute sql_statement
ÿ
ÿ ' close MDB
ÿ MyDB.Close
ÿ
ÿ Set MyDB = Nothing

[anonymous]

[Originally posted by Mr. D]

Thanks again for the reply. Your code worked! I was also able to open and view the created .dbf file in my Clipper database utility program. I noticed that no error occured even though I did not put the Microsoft Access 8.0 Object Library reference.

I have one question, though. What if my Access table has an index, say on the first field? In Clipper and/or in dBase, there are separate files for indexes, .NTX for Clipper and .NDX (I think) in dBase. Will these files be created also?

Thanks again! You're a great help!

(I have also sent this response to your email add.)