Transfering recordset from access to mysql srver

[sal21]

I have 2 connection:
CONN is jet alo Access connection
CONN1 is ODBC connector 3.1 MySql connection

In the two databases have a table named with the same name : TABLE1

The two table contain the same numbers of fileds with the same name fields. (in effect is a specular table)

I need a fast way to insert with a query (select .. where... - in one shot only if possible- ) all records from the access table into the MySql table.

Note:
The two connections are just opened
I use vb6 calssic

[HansV]

I don't know of a way to do this in one go.

I think you'll have to open two recordsets, one on Table1 in Conn and the other on Table1 in Conn1.
Then loop through the records in the first recordset, and add each record to the second recordset.

[sal21]

I don't know of a way to do this in one go.

I think you'll have to open two recordsets, one on Table1 in Conn and the other on Table1 in Conn1.
Then loop through the records in the first recordset, and add each record to the second recordset.

ok for that...

But in order of speed if i save the selected records from access in a csv file and import in MySql with the schema.ini, i have a good performance, instead to loop one to one?

[HansV]

That might well be more efficient, but the best way to find out is to experiment!

[sal21]

That might well be more efficient, but the best way to find out is to experiment!

HUMMMMMM...

my idea:

Code: Select all

....
 CONN.BeginTrans
for i=0 to ....ecc
    SQL = "INSERT INTO L0928_SI (FIELD1) VALUES ('8506')"
    CONN.Execute SQL, , adCmdText + adExecuteNoRecords
next i
    CONN.CommitTrans
....

but i have 21 fileds in the table i need to write one to one or... in (FIELD1,FIELD2....) VALUES ('1','2' ...)

[HansV]

Writing constant values to a records doesn't seem to be a good way to test transfering records from one table to another...

[sal21]

I don't know of a way to do this in one go.

I think you'll have to open two recordsets, one on Table1 in Conn and the other on Table1 in Conn1.
Then loop through the records in the first recordset, and add each record to the second recordset.

wath you think about:

Code: Select all

            CONN.BeginTrans
            For X = 0 To UBound(strDBRows, 2)
            K = 0
            RS.AddNew
            For Y = 0 To RS.Fields.Count - 1
            RS.Fields(Y).Value = strDBRows(K, Y)
            K = K + 1
            DoEvents
            Next Y
            RS.Update
            DoEvents
            Next X
            CONN.CommitTrans

[HansV]

I think strDBRows(K, Y) should be strDBRows(K, X), and you don't actually need the variable K - it has the same value as Y:

Code: Select all

            CONN.BeginTrans
            For X = 0 To UBound(strDBRows, 2)
                RS.AddNew
                For Y = 0 To RS.Fields.Count - 1
                    RS.Fields(Y).Value = strDBRows(Y, X)
                    DoEvents
                Next Y
                RS.Update
                DoEvents
            Next X
            CONN.CommitTrans