Solved

use access to connect and modify mysql online database

Posted on 2010-11-11
9
348 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:rtod2
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
I agree.  I need to log this one in my notes and so I haven't accepted yet!  Thanks.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 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

14 Experts available now in Live!

Get 1:1 Help Now