Solved

Import fields from Mysql to MS Access

Posted on 2015-02-06
7
85 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 49

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 34

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 34

Expert Comment

by:PatHartman
ID: 40594117
PS - Gus - Append queries add rows.  Update queries update columns of existing rows.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 49

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now