In November, 2003, I moved ykfp.org to a new web host. Almost nobody noticed, but that was my plan, to migrate site and domain registration without any changes visible to the user.
The new host has much better database capabilities. This web host, like my previous one, is running MySQL, a free open source SQL database that is very popular on the web and much cheaper than Oracle or Sybase. But on this new host, there are can be several different databases and each can have several users each with different access privileges. This host also allows ODBC connections so with an ODBC driver configured with one of our user names and passwords can use the perhaps more familiar MS Access program as a front end to the web databases, much like a Virtual Private Network. I'm thinking this new capability could change the way we present the MS Access tables we currently keep in the YKFP data warehouse. And I'm thinking there may be more applications where a group of MS Access tables can be shared and managed by a group of users with read/write privileges from different offices of YKFP or agencies across the state and viewed by a wider audience that are given a database user name with read only privileges.
Previously I'd made lame attempts at Perl programming to write CGI scripts to access the web based MySQL host or modify existing Perl scripts (see http://ykfp.org/cgi-bin/mdm9.cgi.or http://ykfp.org/cgi-bin/spch_search2.pl) This new host also provides the most commonly used script to manage MySQL on the web, MySQLAdmin, written in PHP. See http://www.ykfp.org/dh_phpmyadmin/mysql.ykfp.org/ , user name selectonly, password satus2 .
To try this ODBC connection to my two test MySQL tables, you need to install a ODBC driver on your PC called Connector-ODBC that you can get at http://www.mysql.com/downloads/connector/odbc/. Recently, I have had some installations of the MySQL ODBC driver fail with a messsage "requires Visual Studio 2015 x64 redistributable. In this case, download and install this component from this source https://www.microsoft.com/en-us/download/details.aspx?id=52685 before continuing with the Connector-ODBC driver.
Once the ODBC driver is installed on your PC, you configure it to talk to our MySQL service. See http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation.html. On XP, you find this set up under Control Panel, Administrative Tools, Data Sources (ODBC). Click on add, and find the MySQL ODBC 5.1 Driver, probably at the bottom of your list.
On Windows 10 , 64 bit, look under Control Panel, Administrative Tools, ODBC Data Sources (64-bit). Click on Add and find the MySQL ODBC 5.3 ANSI or Unicode Driver on the list.
Name the new connection to something that you'll remember and configure the connection as shown in the screen shot below:
These are the connection settings for a read only ODBC link to my web data tables to prevent accidental corruption of the tables. ( user name selectonly, password satus2) However, a different user name and password are available to give you full editing access to the real data for those that would be database contributors and data managers. You can test to see if the connection is going to work through your internet connection and speed by clicking that button at the bottom of the driver set up screen labeled "Test". If you don't see anything in the database pulldown for choices, something isn't working, and the "Test" button will fail too.
Once the data source tests OK, hit the OK button to save the connection,then you can start up MS Access with a new or existing database.
To bring the MySQL data tables into MS Access 2013, you go to "External Data" tab on the top ribbon, Import or ODBC database. Import brings a copy from MySQL into your MS Access database, which would be OK if you just wanted to download your own copy. Link makes a live link to the MySQL tables so you'd always be looking at the most recent data, and if you were given a connection with full permissions, the ability to edit and add data directly to the shared MySQL table. Click OK.
Link and Import choices trigger a new dialog box to find the data source. Select Machine Data Source to see all the ODBC sources you have set up on your PC. .
When you choose the ODBC Databases, then click the Machine Data Source tab, you should see a list of possible choices that should include the one you set up and named during the ODBC configuration:
The ODBC connection then goes and brings back a list of tables in the MySQL
database.
I'd try tblPROCounts first since it is small. Then , if that works,
really test your connection by linking to the much largerrecord tblYakRSPChAgeSexLe
table.
Access should show your linked table with a arrow pointing at world globe icon. You should be able to browse and select records, and you may be able do use them in queries and forms with links to local tables. With a password for full read-write MySQL permissions, you would be able to edit items and add records.
For Office 2007 Access, you go to the External Data tab, More, ODBC Database, Import or Link as described above for Office 2003 and a Select Data Source window pops up. Under the Machine Data Source find the Data Source Name that you just set up in Administrative Tools, Data Sources in windows that look just like the the ones above in the explanation for Office 2003. As you select tables, you will be asked to select a key that uniquely identify each record. In this database, it is usually ID or Date.
I keep a MySQL database of flow and temperature records from selected sites by screen scraping the BOR hydromet site with automatically scheduled tasks on our web server host. To use these tables in Access, use the same procedure as above and use server name bor.ykfp.org, user name selectonly, password satus2, for a read only connection.
We were impressed how well this ODBC connection worked at our Klickitat field office with their satellite dish system, but now it's even better now that the Klickitat Office has T1 connection. I'd like to know if PC's at WDFW and Fed. offices can make this connection through their firewalls. Of course, your own mileage may vary. Let me know how well it works. I don't think linking to 28000 records will work over dial-up connections.
For many years I used a freeware VB module to get MS Access tables into MySQL tables, but sometimes I would find that some version problems between new versions of Access and the VB Module. It seems that there were some Access item types that the module or my version of MySQL didn't know about. More recently, I found an inexpensive program Navicat that has never failed me in resoving those version issues. The $95 for problem free export to MySQL has been well worth it. MySQL also provides a package of database utilities MySQL Workbench.