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
Transfering recordset from access to mysql srver
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfering recordset from access to mysql srver
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.
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Transfering recordset from access to mysql srver
ok for that...HansV wrote: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.
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?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfering recordset from access to mysql srver
That might well be more efficient, but the best way to find out is to experiment!
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Transfering recordset from access to mysql srver
HUMMMMMM...HansV wrote:That might well be more efficient, but the best way to find out is to experiment!
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
....
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfering recordset from access to mysql srver
Writing constant values to a records doesn't seem to be a good way to test transfering records from one table to another...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Transfering recordset from access to mysql srver
wath you think about:HansV wrote: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.
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfering recordset from access to mysql srver
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
Best wishes,
Hans
Hans