Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import fields from Mysql to MS Access

Posted on 2015-02-06
7
Medium Priority
?
102 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 52

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 40

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 40

Expert Comment

by:PatHartman
ID: 40594117
PS - Gus - Append queries add rows.  Update queries update columns of existing rows.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

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.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

885 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