Solved

Import fields from Mysql to MS Access

Posted on 2015-02-06
7
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 51

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 38

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 38

Expert Comment

by:PatHartman
ID: 40594117
PS - Gus - Append queries add rows.  Update queries update columns of existing rows.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 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