Importing DBF files to MDB files

[Colin Moore]

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

[marc]

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

[Paul Clement]

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)

[luca90]

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:-)

[Hack]

Maybe if you changed "Table3" to "TEST1" in the sample code....

[luca90]

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

[gibra]

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.

[luca90]

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

[gibra]

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...

[Hack]

To append data, you must use INSERT INTO , instead of SELECT INTO.

Good eyes....I completely missed that.

[NatSem]

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.