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.
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.
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 = ""
'In your form code window add the following.
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()
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
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!FirstFieldName = Text1.Text
rsAccess!SecondFieldName = Text2.Text 'And so on for all the fields in your access table
Progressbar1.Value = Progressbar1.Value + 1
'In your Command1_Click event add the following: