Copy Table Mysql to MS Access

[royaloba]

Hello guys,

i been searching this for a month still cannot find anything.
is there any way to copy mysql table to ms access?

Best Regards

[AndreRet]

Yes you can by either using the Import Table from access. I personally prefer to do it using code, thus ensuring that no data gets dropped. So, you can either copy MySql data to excell and then convert from there, or read directly out of MySql into access. Send me your table name, field names, database name and I'll see if I can help.

[debasisdas]

you need to use some 3rd party tool for the purpose.

[royaloba]

@AndreRet
i have 2 identical table 1 in mysql and 1 in access. i just want to insert all the data from access to mysql. let say the name of table of in mysql is MySQLTABLE1 and the name of table in Access is AcessTABLE1.

[debasisdas]

Then no need to copy the tables at all. simply open the recordset from access table and insert the same to mysql table using another connection.

[royaloba]

i tried it already but nothing happen
this my code before
DB as adodb.connection ' mysql connection
OP as adodb.connection 'access connection

insert into OP.myaccesstable from DB.mysqltable

can you please supply me the correct syntax for this?

Thanks.

[vb5prgrmr]

See...

http://www.connectionstrings.com

to find the DSN Less connection string for both. Then you will need to use ADO to connect to one or the other (I'm thinking MySQL), and use the minimal driver info for an insert into query.

Good Luck

[AndreRet]

Try the following code:

This is assuming that MySql and Access tables are both on the same system and not a server. For connection strings to a server see http://www.connectionstrings.com/mysql. Add 1 command button control to your form with as many textboxes as you have fields for in the MySql table. What is basically going to happen when you click the command button, all fields of a specific record will be loaded with the data from MySql into the textboxes. This data will then be added to your access table. The textboxes will be cleared and the next record will be loaded, and so on until you have reached your end of file record. Also add a Progress Bar Control to the form to keep track of how much data has been copied.

'Add a module to your project and paste the following code into it.

Public Sub ClearTextBoxes(frm As Form)

Dim Control As Control

 For Each Control In frm.Controls
        If TypeOf Control Is TextBox Then
            Control.Text = ""
        End If
       
    Next Control
End Sub

'In your form code window add the following.

Option Explicit

Private WithEvents cnMySql As ADODB.Connection
Private WithEvents cnAccess As ADODB.Connection
Private WithEvents rsMySql As ADODB.Recordset
Private WithEvents rsAccess As ADODB.Recordset

'In your form load event add the following.

cnMySql.Open "Provider=MySQLProv;Data Source=MySqlDatabase;User Id=myUsername;Password=myPassword;" 'Where MySqlDatabase is the name of your MySql database. Again refer to the above link for other connection strings on MySql
cnAccess.Open "provider = microsoft.jet.oledb.4.0;persist security info=false;data source = C:\MyAccesDatabase.mdb" 'The name of your database.

'Add the following sub

Private Sub LoadMySqlDataAndSaveToAccess()

Call ClearTextBoxes(Me)
Set rsMySql = New ADODB.Recordset
Set rsAccess = New ADODB.Recordset

rsMySql.Open "SELECT * FROM MySqlTable1", cnMySql, adOpenStatic, adLockOptimistic
   
    If rsMySql.BOF = True OR rsMySql.EOF = True Then
        Exit Sub
            Else
    Progressbar1.max = rsMySql.Recordcount
    Progressbar1.Value = 0
    Do While rsMySql.EOF = False
        Text1.Text = rsMySql!FirstFieldName
        Text2.Text = rsMySql!SecondFieldName ' And so on for all the fields in your table
        rsAccess.AddNew
        rsAccess!FirstFieldName = Text1.Text
        rsAccess!SecondFieldName = Text2.Text 'And so on for all the fields in your access table
        rsAccess.Update
        Progressbar1.Value = Progressbar1.Value + 1
        Call ClearTextBoxes(Me)
        rsMySql.MoveNext
    Loop
End If

'In your Command1_Click event add the following:
Call LoadMySqlDataAndSaveToAccess

That should wrap it up nicely for you.