Solved

use access to connect and modify mysql online database

Posted on 2010-11-11
9
383 Views
Last Modified: 2012-08-13
This question came up as a result of a security question re: MS Access.  Since this is something I have always wanted to know how to do, I thought I would pursue it further.  Here is my new question restated.

How do I use MS Access 2010 to connect to, and modify an online mysql database?
0
Comment
Question by:rtod2
[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
  • 3
  • 3
9 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34117260
1) Download and install the MySQL ODBC connector. There are several, but most use the one here: http://dev.mysql.com/downloads/connector/odbc/5.1.html

2) Link your tables to the MySQL database. You'll need to be sure the remote server accepts remote connections, and that you can connect through the correct ports. If you own the server, you can configure these things. If this is a hosted database, you'll need to insure the host allows you to connect remotely (most do). Contact your hosting company to insure this.

3) Manage your data.

That's the simple view, of course, and there are many, many other facets to this. MySQL supports Views (basically server-based queries) that can be used to shape data before you get it into Access (note: Views are often ReadOnly, so be aware of that). MySQL also supports Stored Procedures, which are basically code units that can perform work against the data. Using SPs can dramatically increase performance, since you basically pass off the processing needs to the server, leaving your workstation to manage other tasks.

Note too that Server databases support table-based "triggers", which are sort of like macros that run whenever data in manipulated in your server tables. This can be useful if you need to log updates/deletes, for example.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34117276
Note: You'll need to "build" a DSN to the remote MySQL server, or use DSNLess connections. Since you're just learning, I'd do the DSN route at first:

1) Fire up the "Link Tables" dialog. I don't have 2007/2010 on this machine, but it's on the "Data" ribbon I believe.
2) Select "ODBC Database" in the "Files of Type" dropdown. This will start the DSN wizard.
3) Follow the prompts in the wizard, selecting the proper Server. Note you'll most likely want to connect via a valid domain address (like mysql359.server.com, or whatever your hosting company tells you to use). Your hosting company may also tell you the Port to use when connecting; if so, make sure this matches your settings when building the DSN.
4) Once the DSN is built (and AFTER using the 'Test Connection' button to insure connectivity), you'll be presented with a list of Tables and Views. Select only those you need to work with - you'll likely see a LOT of 'sys' type tables, and you don't need to connect to them. This can really slow down your load times, so only link the ones you need.

Once you do that, you should see your linked tables in the Access container
0
 

Author Comment

by:rtod2
ID: 34117582
Please excuse the stupidity of my questions as there is likely to be more than one to follow.

1) ODBC connector - Download and install the MySQL ODBC connector. There are several, but most use the one here: http://dev.mysql.com/downloads/connector/odbc/5.1.html

2) Link Tables wizard - Data ribbon > Link Tables > Select Files of Type ODBC Database > Wizard will start


I am using the latest OS and 2010 Office product.  Are you sure that a 'third-party' ODBC connector is required?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 65

Expert Comment

by:rockiroads
ID: 34118055
I mentioned briefly in your last question about using odbc, LSMConsulting has given you excellent detailed info. I will not add further to that except a few things about the odbc side.

In my MySQL setup I had previously, I used a tool called Navicat (check it out http://www.navicat.com/en/download/download.html) IMO it is one of the best, if not the best gui tool for mysql database management. Its the only tool I use for accessing mysql. There is a Lite version which is free of course. Well enough of blatant advertising :)

In order for non mysql applications to use mysql you have to create a dsn via odbc (there are other methods but the simplest way for msaccess is to use odbc). The driver you need in order to create one is the odbc connector. (link given by LSMConsulting). Very easy to setup - here is an example http://www.thewebhostinghero.com/tutorials/mysql-odbc-howto.html

Once you set it up, use Navicat to verify the dsn works fine. Using Navicat you can create and maintain tables, setup and run queries etc against your mysql database. If this works then create linked tables using odbc. The easiest route for you to take.
0
 

Author Comment

by:rtod2
ID: 34118194
Wow, great information I am getting here!  This is exciting stuff!!

So rocki, navicat is paid software (with a trial of 'course).  Would it require a second app or just it for creating and accessing tables, forms, and reports?
0
 
LVL 84
ID: 34119142
<Are you sure that a 'third-party' ODBC connector is required? >

Yes, this is required, and I'm not sure I'd call an ODBC connector a "3rd party tool" ;). These are connection libraries put out by the vendor of the db that are used to actually connect with the platform. There is also an ODBC connector for Microsoft SQL Server, for Oracle, etc etc etc.

Navicat is a tool to manage your Database - in other words, to review table strucutres, add Views, add Indexes, etc etc - in other words, something you can use to perform DBA duties. It is not a UI to your actual data (although you can build and test queries using Navicat). You would need Access (or some other UI) to actually build a program your users would run to work with the MySQL data.

Navicat can be installed on your workstation and used to connect to the remote MySQL database. I don't use MySQL currently, so don't have it installed, but I do work with MSSQL daily (in remote locations) and I use SQL Server Management Studio. The concept is the same - using a local tool to connect to a remote database for the purpose of DBA duties.

Note your hosting company many also provide you with an online version of this (or some other tool). You can use either, but I find the local tools much easier to work with than the online stuff.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34121512
Navicat is for your use only and the Lite version is free. It allows you to look at your table data and manipulate it via tables much like you can in msaccess. Similar to access in terms of opening up tables, creating queries but no reports or modules. You can use it to create any stored procedures you want though. I use it as my database management tool for mysql and nothing else. It is a easier and friendlier tool that the mysql command line client.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34121544
The Navicat info I gave is just additional info to help you with mysql and testing the odbc connector. So try not to get sidetracked too much from the original question and what LSMConsulting has replied with. I am recommending Navicat because it is free and it is an excellent database management tool. But when you start your user application, you can use MSAccess very easily using the odbc connector and linked tables.
0
 

Author Comment

by:rtod2
ID: 34121799
I agree.  I need to log this one in my notes and so I haven't accepted yet!  Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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 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 …

759 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