Solved

Import fields from Mysql to MS Access

Posted on 2015-02-06
7
93 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 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 37

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 37

Expert Comment

by:PatHartman
ID: 40594117
PS - Gus - Append queries add rows.  Update queries update columns of existing rows.
0
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

728 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