Solved

Import fields from Mysql to MS Access

Posted on 2015-02-06
7
91 Views
Last Modified: 2015-03-16
I need to import 5 fields from Mysql into a MDB file. I would like real time sync one way if possible but scheduled import would also do. Is there any third party tools to achieve this. I am able to import tables into the mdb file but not a field from Mysql into a field in the MDB file. If i manually copy and paste the fields it works so surely there is a way to have this synced or imported automatically?
0
Comment
Question by:Pat Shortt
  • 3
  • 2
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40594098
Just link the files from MySQL, then create an append query in Access to read the fields from the table into the fields in question of the Access table.

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40594114
Are you saying that you want to UPDATE 5 fields in a local table with values from a MySQL table?  If so, you would
1. link to the MySQL table.  
2. create a query that joins the two tables on PK to PK.  
3. Change the query type to update.  
4. Select the 5 columns you want to update from the local table.
5.  Fill in the Update to line with the tablename.column name from the MySQL table.
6. Save the query.
7.  Run the query to do the update.

If you want this to run periodically, you should create a new FE with just this update query.  Create a start up form with a Timer event.  Have the timer event run every n minutes or hours or whatever.  The timer event should run the query.  As long as this database is open, it will run the update according to the interval set for the Timer.  However, you have to make sure that the app is open because if it isn't, then the update won't happen.  You could add code to close the database at 2AM every morning.  Then use Windows scheduler to restart the app at 2:10 AM.  At least that will ensure that it runs at least once every day.  Of course, the PC must be powered on.  Or you could run the app from a server that is always powered on.  The problem there is that Office is rarely installed on the server.  You can probably get away with using the Access runtime though.

A better solution if you really want live data is to just link to the MySQL database and join to the relevant table when you need to see the changing data.  Copying it into Access just makes it possible for you to be using old data and making bad decisions.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40594117
PS - Gus - Append queries add rows.  Update queries update columns of existing rows.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Pat Shortt
ID: 40595505
I will give it a go. I don't use Access at all. Any tutorials for "create a query that joins the two tables on PK to PK" - "create a new FE "

MySql and Access will be installed on the same Windows 7 PC. The Access file is from a Door Access control system that will be on at all times.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40595514
0
 

Accepted Solution

by:
Pat Shortt earned 0 total points
ID: 40659236
I used apatar to get it sorted in the end
0
 

Author Closing Comment

by:Pat Shortt
ID: 40667510
Only thing that worked
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question