Using Microsoft Access as a Front-end to MySQL

MySQL Connector/ODBC Developer Guide  /   /  Using Microsoft Access as a Front-end to MySQL

6.4.3 Using Microsoft Access as a Front-end to MySQL

You can use Microsoft Access as a front end to MySQL by linking tables within your Microsoft Access database to tables that exist within your MySQL database. When a query is requested on a table within Access, ODBC is used to execute the queries on the MySQL database.

To create a linked table:

  1. Open the Access database that you want to link to MySQL.
  2. On the External Data tab, choose ODBC Database.

    Figure 6.12 External Data: ODBC Database

    Content is described in the surrounding text.
  3. In the Get External Data dialog box that appears, choose Link to the data source by creating a linked table and click OK.

    Figure 6.13 Get External Data: Link To ODBC Database Option Chosen

    Shows the "Get External Data" dialog with two options: Import the source data into a new table in the current database, and Link to the data source by creating a linked table (selected).
  4. The Select Data Source dialog box appears; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC DSN you want to link your table to. To define a new DSN for Connector/ODBC instead, click New and follow the instructions in Section 5.3, “Configuring a Connector/ODBC DSN on Windows”; double click the new DSN after it has been created.

    Figure 6.14 Selecting An ODBC Database

    Shows the Select Data Source dialog with two tabs: "File Data Source" and "Machine Data Source." The Machine Data Source tab is selected and displays three columns: Data Source Name, Type, Description. The selected row has "MySQL 5.7" defined as the Data Source Name, and "System" as the Type.


    If the ODBC data source that you selected requires you to log in, enter your login ID and password (additional information might also be required), and then click OK.

  5. Microsoft Access connects to the MySQL server and displays the list of tables that you can link to. Choose the tables you want to link to (or click Select All), and then click OK.

    Figure 6.15 Link Tables Dialog: Selecting Tables to Link

    Content is described in the surrounding text.


    Notes
    • If no tables show up for you to select, it might be because you did not choose the Database to connect to when you defined or logged in to the DSN. Reconfigure the DSN and specify the Database to connect to (see Section 5.3, “Configuring a Connector/ODBC DSN on Windows” for details), or choose a Database when you log in to the DSN.
    • If your database on Access already has a table with the same name as the one you are linking to, Access will append a number to the name of the new linked table.
  6. If Microsoft Access is unable to determine the unique record identifier for a table automatically, it will ask you to choose a column (or a combination of columns) to be used to uniquely identify each row from the source table. Select the column[s] to use and click OK.

    Figure 6.16 Linking Microsoft Access Tables To MySQL Tables, Choosing Unique Record Identifier

    Shows the "Select Unique Record Identifier" dialog with a list of fields in the selected table. In this example, the table name is cats2 and the unique fields are CatID, CatName, OwnerID, and Birthday. The available buttons are "OK" and "Cancel".

Once the process has been completed, you can build interfaces and queries to the linked tables just as you would for any Access database.

Use the following procedure to view links or to refresh them when the structures of the linked tables have changed.

To view or refresh links:

  1. Open the database that contains links to MySQL tables.
  2. On the External Data tab, choose Linked Table Manager.

    Figure 6.17 External Data: Linked Table Manager

    Shows "Linked Table Manager" under the "External Data" tab in Microsoft Access. Underneath are two panes: The All Access Objects pane shows the available tables, and the second pane shows data from the table. The "cats2" table is selected.
  3. The Linked Table Manager appears. Select the check box for the tables whose links you want to refresh. Click OK to refresh the links.

    Figure 6.18 External Data: Linked Table Manager Dialog

    Shows the "Linked Table Manager" dialog with the cats2 table selected with its associated checkbox selected.


    If the ODBC data source requires you to log in, enter your login ID and password (additional information might also be required), and then click OK.

Microsoft Access confirms a successful refresh or, if the tables are not found, returns an error message, in which case you should update the links with the steps below.

To change the path for a set of linked tables (for pictures of the GUI dialog boxes involved, see the instructions above for linking tables and refreshing links) :

  1. Open the database that contains the linked tables.
  2. On the External Data tab, choose Linked Table Manager.
  3. In the Linked Table Manager that appears, select the Always Prompt For A New Location check box.
  4. Select the check box for the tables whose links you want to change, and then click OK.
  5. The Select Data Source dialog box appears. Select the new DSN and database with it.

WordPress site speedup with compression added code to .htaccess file.

I had a slow Gtmetrix reading on the speed of my website. used htaccess file to compress my files on the server – see https://kinsta.com/knowledgebase/enable-gzip-compression/

added code to my htaccess file and see the difference

After gzip added to htaccess file


.htaccess code added at bottom of file:

<IfModule mod_deflate.c>
# Compress HTML, CSS, JavaScript, Text, XML and fonts
AddOutputFilterByType DEFLATE application/javascript
AddOutputFilterByType DEFLATE application/rss+xml
AddOutputFilterByType DEFLATE application/vnd.ms-fontobject
AddOutputFilterByType DEFLATE application/x-font
AddOutputFilterByType DEFLATE application/x-font-opentype
AddOutputFilterByType DEFLATE application/x-font-otf
AddOutputFilterByType DEFLATE application/x-font-truetype
AddOutputFilterByType DEFLATE application/x-font-ttf
AddOutputFilterByType DEFLATE application/x-javascript
AddOutputFilterByType DEFLATE application/xhtml+xml
AddOutputFilterByType DEFLATE application/xml
AddOutputFilterByType DEFLATE font/opentype
AddOutputFilterByType DEFLATE font/otf
AddOutputFilterByType DEFLATE font/ttf
AddOutputFilterByType DEFLATE image/svg+xml
AddOutputFilterByType DEFLATE image/x-icon
AddOutputFilterByType DEFLATE text/css
AddOutputFilterByType DEFLATE text/html
AddOutputFilterByType DEFLATE text/javascript
AddOutputFilterByType DEFLATE text/plain
AddOutputFilterByType DEFLATE text/xml

# Remove browser bugs (only needed for really old browsers)
BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4\.0[678] no-gzip
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
Header append Vary User-Agent
</IfModule>

my htaccess file:

# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ – [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

# END WordPress

# jc start to set 301 fault page and no www.

RewriteEngine On
RewriteCond %{HTTPS} on [OR]
RewriteCond %{HTTP_HOST} ^www\. [NC]
RewriteCond %{HTTP_HOST} ^(?:www\.)?(.+)$ [NC]
RewriteRule ^ http://%1%{REQUEST_URI} [L,NE,R=301]

# jc end

RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule (.*) /404.php
ErrorDocument 404 /404.php

# jc gzip call
<IfModule mod_deflate.c>
# Compress HTML, CSS, JavaScript, Text, XML and fonts
AddOutputFilterByType DEFLATE application/javascript
AddOutputFilterByType DEFLATE application/rss+xml
AddOutputFilterByType DEFLATE application/vnd.ms-fontobject
AddOutputFilterByType DEFLATE application/x-font
AddOutputFilterByType DEFLATE application/x-font-opentype
AddOutputFilterByType DEFLATE application/x-font-otf
AddOutputFilterByType DEFLATE application/x-font-truetype
AddOutputFilterByType DEFLATE application/x-font-ttf
AddOutputFilterByType DEFLATE application/x-javascript
AddOutputFilterByType DEFLATE application/xhtml+xml
AddOutputFilterByType DEFLATE application/xml
AddOutputFilterByType DEFLATE font/opentype
AddOutputFilterByType DEFLATE font/otf
AddOutputFilterByType DEFLATE font/ttf
AddOutputFilterByType DEFLATE image/svg+xml
AddOutputFilterByType DEFLATE image/x-icon
AddOutputFilterByType DEFLATE text/css
AddOutputFilterByType DEFLATE text/html
AddOutputFilterByType DEFLATE text/javascript
AddOutputFilterByType DEFLATE text/plain
AddOutputFilterByType DEFLATE text/xml

# Remove browser bugs (only needed for really old browsers)
BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4\.0[678] no-gzip
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
Header append Vary User-Agent
</IfModule>

phpMyAdmin and MySQL

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.

phpMyAdmin comes with a wide range of documentation

 

Demo Page – https://demo.phpmyadmin.net/master-config/index.php?lang=en

Hudson valley tech meetup

There were over 151 attended the end of the year event at the Hudson Valley Tech Meetup.

Senate Garage –  6 North Front Street · Kingston, NY

Details

Please join us for our year end tech meetup! There will be food to eat and liquids to drink while innovative, caring humans attempt to raise the bar for compassionate collaboration without disrupting anything!

Read on for a hint or two about what you can expect from Hudson Valley Tech Meetup this yuletide season.

But first, a word about our event sponsor, Ryan & Ryan Insurance Brokers!

Bob Ryan and his father before him have delivered sixty years of outstanding service both to their customers and to the community in which their business was founded. Supporting community groups, activities, and initiatives is how the Ryan family has always chosen to spend their marketing dollars. We are proud to receive support from Bob Ryan and also to thank him for his many years of community investment.

Some of the organizations that receive support from Ryan & Ryan Insurance will also be with us at this month’s tech meetup. Please meet them and learn about their missions.

SPEAKERS:

Jordan Koschei (http://jordankoschei.com/) is a designer, developer and a long standing member of Hudson Valley Tech Meetup. A native of the Hudson Valley he’s currently the Lead Product Designer at Agrilyst (https://www.agrilyst.com/), a venture-backed AgTech startup, as well as the creator of a new product to connect our region’s creative community, the Hudson Valley Talentbase (https://hvtalentbase.com).

Jordan will present HV Talentbase to us while talking about his vision and roadmap for the product, the audience that it serves, and how it can help our community.

We’re really glad to have Jordan back on the HV Tech stage and we’re proud and grateful for all that he does to support the Hudson Valley tech and startup ecosystem.

———————————————————————————–

Mayra Hidalgo Salazar (https://northstarfund.org/about/people/mayra-hidalgo-salazar) is Hudson Valley Program Officer for North Star Fund (https://northstarfund.org/). Originally from Naranjo, Alajuela, Costa Rica Mayra arrived to the United States when she was six-months old and grew up as an undocumented immigrant before becoming an activist organizer, most notably as the project manager for the Trail of Dreams at age 17.

Now Mayra leads North Star Fund’s grantmaking and donor organizing efforts in the Hudson Valley by bringing direct support to people and groups experiencing systemic discrimination so that they can lead strategic efforts to end it.

Founded in 1978, North Star Fund is known for their innovative philanthropic model as a community foundation that delivers financial support directly to activists from marginalized populations pushing for justice around the most pressing issues impacting their communities.

We’re honored to have Mayra with us at Hudson Valley Tech Meetup and we know that you all will benefit from hearing about her work on the front lines of social injustice.

———————————————————————————–

Gerry Valentine (https://gerryvalentine.com/) is a public speaker, executive coach, and writer; who comes from an extensive Fortune 100 background. He advises corporate leaders, entrepreneurs, and entire companies on how to turn challenges into an advantage. He specializes in leadership vision, building resilience, unlocking innovation, and showing leaders how to use the best within themselves to inspire the very best in others.

Gerry gave a lightning talk at the 2017 Catskills Conf that blew the roof off the Ashokan Center. We are very excited to welcome him now to the stage at Hudson Valley Tech Meetup.

This is a unique opportunity to be inspired by this incredible human who splits his time between New York City and New Paltz. We’re proud to give him our stage and we know that you are going to love what he has to say.

———————————————————————————–

COMMUNITY ANNOUNCEMENTS If you have something you want to share at the meetup, please submit your slide on our Google Form (https://goo.gl/forms/S3IaxZrOXmczbkUc2) (1920 x 1080px) before 12:00 PM on the day of the meetup.

———————————————————————————–

LOCAL FOOD & BEVERAGE

Much of the food at this meetup will be provided by one of our favorite local food businesses and also the newest community partner for Hudson Valley Tech Meetup. Big thanks to Bread Alone for sharing their love of artisan bread and baking with all of us!

As usual, we’ll be pouring locally made Keegan Ales (http://www.keeganales.com) courtesy of our friends at Moonfarmer (https://moonfarmer.com).

———————————————————————————–

Volunteers! Do you want to learn how to set up an awesome meetup? Bring your love for community organizing and any experience with sound, lighting and video production to hello@hvtech.org . [NOTE: experience is not required] Thank you.