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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

use access to connect and modify mysql online database

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
rtod2
Asked:
rtod2
  • 3
  • 3
  • 3
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
rtod2Author Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
rockiroadsCommented:
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
 
rtod2Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
rtod2Author Commented:
I agree.  I need to log this one in my notes and so I haven't accepted yet!  Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now