Solved

use access to connect and modify mysql online database

Posted on 2010-11-11
9
358 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
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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

911 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

18 Experts available now in Live!

Get 1:1 Help Now