i been searching this for a month still cannot find anything.
is there any way to copy mysql table to ms access?
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.
you need to use some 3rd party tool for the purpose.
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.
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.
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?
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.
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.