FoxPro to MySQL using vb6

[dstrange]

I have to convert around 100,000 records from FoxPro tables into Mysql. What would you recommend is the fastest way of doing this? I've taken over a project that as of right now run at over 10 hours for this and it seems way too unnecessary.

Basically the setup is select the specific fields from foxpro and then add them into MySql via a small vb6 app. There are a few small loops within this conversion but it seems there must be a faster method of doing this. Any advice will be appreciated. Thanks!

 

[Golom]

Completely untested ... and I don't use either Foxpro or mySQL

Public Sub CopyFox()
Dim oCn_Fox                     As New ADODB.Connection
Dim oCn_SQL                     As New ADODB.Connection

Dim oRs_Fox                     As ADODB.Recordset

oCn_Fox.Open "Provider=vfpoledb;" & _
             "Data Source=C:\vfp9\Samples\Data\myVFPDB.dbc;" & _
             "Mode=ReadWrite|Share Deny None;" & _
             "Collating Sequence=MACHINE;" & _
             "Password=''"

oCn_SQL.Open "Provider=MySQLProv;" & _
             "Data Source=mySQLDB;" & _
             "User Id=myUsername;" & _
             "Password=myPassword"

oRs_Fox.Open "Select * From FoxTable", oCn_Fox
With oRs_Fox
    Do Until .EOF
        oCn_SQL.Execute "INSERT INTO mySQLTable (fld1, fld2, fld3, ...) " & _
                        "VALUES(" & ![fld1] & ", " & ![fld2] & ", " & ![fld3] & ", ...)"
        .MoveNext
    Loop
    .Close
End With

Set oRs_Fox = Nothing
Set oCn_Fox = Nothing
Set oCn_SQL = Nothing
End Sub

 

[DonQuichote]

You forgot something. Set Autocommit off before the first insert query and give a commit after the last. That will drastically speed up things!

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)