Importing DBF files to MDB files


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Importing DBF files to MDB files

  1. #1
    Colin Moore Guest

    Importing DBF files to MDB files


    Hi All,

    I need to convert DBF files to an Access mdb file or a Excel xls file. I
    have done lots of database programming but I have never worked with FoxPro
    or DBase (DBF). I'll explain why I need to convert, mabye you have another
    solution.

    there is a very old system running here that collects data using a program
    that puts this data into DBF format, there is now a new system that has to
    work with the old system, the new system is programmed in RPG/400. The reason
    for that is for the security of the AS/400. The overall goal is to take
    datafrom the old system and combine it with data from the new system to create
    nice reports for the HR department. The problem is that RPG can only read
    flat files. I would export the dbf files mannually except we need this to
    happen at least once a day to generate the reports, so we are going to automate
    the program to run once a day.

    I hope I didn't confuse you, any help would be appreciated.

    Colin Moore
    IS Dept.
    Ext. 188

  2. #2
    marc Guest

    Re: Importing DBF files to MDB files


    why not link the tables to an access database. Use access's link table manager.

    Marc


    "Colin Moore" <moorecj@larsens.ns.ca> wrote:
    >
    >Hi All,
    >
    >I need to convert DBF files to an Access mdb file or a Excel xls file.

    I
    >have done lots of database programming but I have never worked with FoxPro
    >or DBase (DBF). I'll explain why I need to convert, mabye you have another
    >solution.
    >
    >there is a very old system running here that collects data using a program
    >that puts this data into DBF format, there is now a new system that has

    to
    >work with the old system, the new system is programmed in RPG/400. The

    reason
    >for that is for the security of the AS/400. The overall goal is to take
    >datafrom the old system and combine it with data from the new system to

    create
    >nice reports for the HR department. The problem is that RPG can only read
    >flat files. I would export the dbf files mannually except we need this to
    >happen at least once a day to generate the reports, so we are going to automate
    >the program to run once a day.
    >
    >I hope I didn't confuse you, any help would be appreciated.
    >
    >Colin Moore
    >IS Dept.
    >Ext. 188



  3. #3
    Paul Clement Guest

    Re: Importing DBF files to MDB files

    On 12 Jul 2001 05:59:48 -0700, "Colin Moore" <moorecj@larsens.ns.ca> wrote:


    Hi All,

    I need to convert DBF files to an Access mdb file or a Excel xls file. I
    have done lots of database programming but I have never worked with FoxPro
    or DBase (DBF). I'll explain why I need to convert, mabye you have another
    solution.

    there is a very old system running here that collects data using a program
    that puts this data into DBF format, there is now a new system that has to
    work with the old system, the new system is programmed in RPG/400. The reason
    for that is for the security of the AS/400. The overall goal is to take
    datafrom the old system and combine it with data from the new system to create
    nice reports for the HR department. The problem is that RPG can only read
    flat files. I would export the dbf files mannually except we need this to
    happen at least once a day to generate the reports, so we are going to automate
    the program to run once a day.

    I hope I didn't confuse you, any help would be appreciated.

    Colin Moore
    IS Dept.
    Ext. 188

    Here is some relatively straightforward code for importing a dBase file into an Access table:

    Sub ImportdBaseToAccess()

    Dim cnn As New ADODB.Connection
    Dim sqlString As String

    ' Open the Connection
    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\My Documents\db1.mdb;" & _
    "Jet OLEDB:Engine Type=4"

    sqlString = "SELECT * INTO [Table3] FROM [dBase IV;DATABASE=C:\My Documents\dBase].[dBase1]"
    cnn.Execute sqlString

    cnn.Close
    Set cnn = Nothing

    End Sub


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    Join Date
    Mar 2005
    Posts
    37
    Quote Originally Posted by Paul Clement View Post
    On 12 Jul 2001 05:59:48 -0700, "Colin Moore" <moorecj@larsens.ns.ca> wrote:

    &#164;
    &#164; Hi All,
    &#164;
    &#164; I need to convert DBF files to an Access mdb file or a Excel xls file. I
    &#164; have done lots of database programming but I have never worked with FoxPro
    &#164; or DBase (DBF). I'll explain why I need to convert, mabye you have another
    &#164; solution.
    &#164;
    &#164; there is a very old system running here that collects data using a program
    &#164; that puts this data into DBF format, there is now a new system that has to
    &#164; work with the old system, the new system is programmed in RPG/400. The reason
    &#164; for that is for the security of the AS/400. The overall goal is to take
    &#164; datafrom the old system and combine it with data from the new system to create
    &#164; nice reports for the HR department. The problem is that RPG can only read
    &#164; flat files. I would export the dbf files mannually except we need this to
    &#164; happen at least once a day to generate the reports, so we are going to automate
    &#164; the program to run once a day.
    &#164;
    &#164; I hope I didn't confuse you, any help would be appreciated.
    &#164;
    &#164; Colin Moore
    &#164; IS Dept.
    &#164; Ext. 188

    Here is some relatively straightforward code for importing a dBase file into an Access table:

    Sub ImportdBaseToAccess()

    Dim cnn As New ADODB.Connection
    Dim sqlString As String

    ' Open the Connection
    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\My Documents\db1.mdb;" & _
    "Jet OLEDB:Engine Type=4"

    sqlString = "SELECT * INTO [Table3] FROM [dBase IV;DATABASE=C:\My Documents\dBase].[dBase1]"
    cnn.Execute sqlString

    cnn.Close
    Set cnn = Nothing

    End Sub


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)
    Hi Paul....
    Sorry for reply on old post, but is interessing me.
    Ths code you have posted recopy all records from dbf to access table, but it create a new table3 in access mdb...
    If just have a table named TEST1 and i want to recopy records from dbf into this tablem, without create new table?


    NOTE:

    -)in access mdb table i just have named 13 fileds Prova1, prova2...prova13 and the access table is cleared and not have records.
    -)the fileds in dbf not have the same name of fields in access table
    Tks.

    Sorry for my bad english, i'm italian from Napoli:-)
    Last edited by luca90; 09-19-2009 at 05:05 PM.

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Maybe if you changed "Table3" to "TEST1" in the sample code....
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Mar 2005
    Posts
    37
    Quote Originally Posted by Hack View Post
    Maybe if you changed "Table3" to "TEST1" in the sample code....
    ok...
    But i just have a table in my access mdb a the table is named TEST1...
    The code in this post create a new table with records of dbf.
    I want to add the records of dbf into existing access table named TEST1...

    Note:
    before to append/transfer records from dbf into TEST1 i delete all olds records from this table
    Last edited by luca90; 09-22-2009 at 10:02 AM.

  7. #7
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    Quote Originally Posted by luca90 View Post
    ok...
    But i just have a table in my access mdb a the table is named TEST1...
    The code in this post create a new table with records of dbf.
    I want to add the records of dbf into existing access table named TEST1...

    Note:
    before to append/transfer records from dbf into TEST1 i delete all olds records from this table
    To append data, you must use INSERT INTO , instead of SELECT INTO.


  8. #8
    Join Date
    Mar 2005
    Posts
    37
    Quote Originally Posted by gibra View Post
    To append data, you must use INSERT INTO , instead of SELECT INTO.

    sintyax error when execute the sql:

    Sub tre()

    Dim cnn As New ADODB.Connection
    Dim sqlString As String

    ' Open the Connection
    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\epf\db1.mdb;" & _
    "Jet OLEDB:Engine Type=4"

    sqlString = "INSERT INTO [test1] FROM [dBase IV;DATABASE=C:\epf\].[Gaf_arc.dbf]"
    cnn.Execute sqlString

    cnn.Close
    Set cnn = Nothing

    End Sub

  9. #9
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    there are different way to use INSERT INTO.

    Please refere to SQL guide

    from VB6:
    - CTRL+H (to open Immediate window)
    - type 'INTO' then press F1

    this should open the the reference guide page, which display:

    INSERT INTO
    SELECT...INTO

    Read with attention...

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by gibra View Post
    To append data, you must use INSERT INTO , instead of SELECT INTO.
    Good eyes....I completely missed that.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  11. #11
    Join Date
    Nov 2013
    Location
    New Delhi
    Posts
    1
    Quote Originally Posted by Colin Moore View Post
    I need to convert DBF files to an Access mdb file or a Excel xls file. I
    have done lots of database programming but I have never worked with FoxPro
    or DBase (DBF).
    See I have no idea about any manual technique or a process to perform this conversion. But I can recommend here SysInfoTools DBF Recovery software as I have been using it since I first faced database corruption. This software has an advanced saving feature that enables users save the recovered data from corrupt DBF files to either DBF format or MDB format. I also use this software as a DBF to MDB Converter. Try it once or read about it at sysinfotools.
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center