Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: Transfering recordset from access to mysql server

    I have 2 connection:
    CONN is ADO jet 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....) all records from the access table into the MySql table.

    Note:
    The two connections are just opened
    I use vb6 calssic
    Last edited by sal21; 03-09-12 at 09:02.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    As these are two separate connections then the only way of inserting into the MySQL table is to read row by row from your application and then insert into MySQL.

    Alternatively what you can do is create link tables in access to point to your MySQL tables from within your database. So let's say you have a table called table1 and its equivalent is table1 in MySQL. We can create a linked table called mytable1 which uses ODBC to connect to MySQL. Then from within Access you can write something like "INSERT INTO mytable1 SELECT * FROM table1"
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by it-iss.com View Post
    As these are two separate connections then the only way of inserting into the MySQL table is to read row by row from your application and then insert into MySQL.

    Alternatively what you can do is create link tables in access to point to your MySQL tables from within your database. So let's say you have a table called table1 and its equivalent is table1 in MySQL. We can create a linked table called mytable1 which uses ODBC to connect to MySQL. Then from within Access you can write something like "INSERT INTO mytable1 SELECT * FROM table1"
    tks...
    But i need to loop "ONE to ONE" the records of access because for a few access records i need to make other operation.

    But not is possible to open the mysql table for writing with:

    rs.open,mysqltable,3,3,adcmtext
    or
    rs.open,mysqltable,1,2,adcmtext

    ????
    Last edited by sal21; 03-09-12 at 14:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •