MySQL and Access via ODBC

A client of mine had a request to connect Microsoft Access to MySQL via an ODBC connector. The MySQL ODBC setup is not very common, but is entirely possible. MySQL does provide an ODBC connector for other database clients.  The setup is a bit tricky. Unfortunately, there’s not a lot of information and documentation online for this type of setup. After some trial and error, I got it to work.

First of all, here are some details. The MySQL server runs on Ubuntu 12.04. The ODBC client, MS Access 2010, runs on a Windows 7 machine. Assuming MySQL is already installed, we just need to make sure the MySQL server ODBC library is installed first.

sudo apt-get install libmyodbc

Now, let’s create an ODBC Data Source. Not sure if this step is required or optional. I did it anyways.

sudo nano /etc/odbc.ini

Add the following entries:

[ODBC Data Sources]
testodbc = MyODBC 5.2.5 Driver DSN
 
[testodbc]
Driver       = MySQL
Description  = MySQL ODBC 5.2.5 Driver DSN
Server       = localhost
Port         =
User         = user
Password     = password
Database     = test
Option       = 3
Socket       =

Let’s secure your MySQL database by answering the following questions.

sudo mysql_secure_installation

Answer the following questions:

Enter current password for root. Enter password.
Change root password? [Y/n] n
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

Allow remote login on MySQL

# Edit mysql configuration
sudo nano /etc/mysql/my.cnf
 
# Comment out "skip-networking"
# Add "bind-address = 192.168.0.10"
# Enter your own IP address
$ bind-address = 192.168.0.10
# Save file and exit. 
 
# Login to MySQL 
$ sudo mysql -u root -p 
 
# Grant privileges 
mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
mysql> flush privileges;
mysql> exit;
 
# Restart MySQL
$ sudo /etc/init.d/mysql restart
# Test connection
$ telnet 192.168.0.10 3306

Download the latest MySQL ODBC Connector and install.

http://dev.mysql.com/downloads/connector/odbc/

Open Microsoft Access

  1. Create “new blank database.”
  2. Select “External Data” and click on “ODBC Database.”
  3. Select “Link to the data source by creating a link table.”
  4. Create a new DSN
  5. Choose “MySQL 5.2 ODBC Driver.”
  6. Enter IP, Port 3306, Username, Password, and Database.
  7. Save DNS for later use.

You should have connected to the MySQL by now.

User Access To Web Root

When setting up your Ubuntu server, you can allow regular Linux accounts to access your web root directory via FTP or secure shell. In this case, you want users to access /var/www directory. One of the first things you’ll need to do is give permissions to the Apache user www-data.

Change the group name to /var/www.

sudo chgrp www-data /var/www

Make it writeable.

sudo chmod 775 /var/www

Set the GID for www-data for all sub-folders.

sudo chmod g+s /var/www

Finally, add your user to www-data group.

sudo usermod -a -G www-data username

This will now allow you to use a regular user account via FTP or SSH with write permissions to the /var/www directory. This is much more secure way than using the www-data user account.

Fix Apache FDQ Error

If you’re working on Ubuntu and running Apache, you probably have seen this error when rebooting Apache. It says “could not reliably determine the server’s fully qualified domain name.” There’s a simple fix to this configuration issue.

You’ll need to edit the /etc/apache2/httpd.conf config file:

sudo nano /etc/apache2/httpd.conf

and add this entry:

ServerName localhost

Reboot Apache

sudo /etc/init.d/apache2 restart

The error is now gone.

Setup Wireless On Raspberry Pi

One of the first things you should do once you have your Raspberry Pi is setup a wireless network adapter. This will allow you to be cable free. To configure, you will need one of several approved USB wireless adapters. I’m using an Airlink 101 Wireless N 150 Ultra Mini-USB Adapter that I purchased at Amazon for just $14. I chose the Airline 101 because it was affordable, and it works very well with the Raspberry Pi.

10 steps to setup wireless on Raspberry Pi:

  1. Plug in the Airlink 101 adapter.
  2. Power up the the Raspberry Pi.
  3. To see if the wireless device is recognized, type lsusb.
  4. To see if the kernel driver is installed, type lsmod.
  5. Edit the /etc/network/interfaces file.
  6. Edit the /etc/wpa_supplicant/wpa_supplicant.conf file.
  7. Reboot.
  8. Check with ifconfig.
  9. Check with ping.
  10. All done.

Here are the commands, instructions and their respected screen outputs.

$ lsusb
Bus 001 Device 004: ID 0bda:8176 Realtek Semiconductor Corp. RTL8188CUS 802.11n WLAN Adapter
 
$ lsmod
8192cu                485042  0
 
$ sudo nano /etc/network/interfaces
# Add the following lines:
auto wlan0
allow-hotplug wlan0
iface wlan0 inet manual
wpa-roam /etc/wpa_supplicant/wpa_supplicant.conf
 
$ sudo nano /etc/wpa_supplicant/wpa_supplicant.conf
# Add the following lines:
network={
ssid="The_SSID"
proto=RSN
key_mgmt=WPA-PSK
pairwise=CCMP TKIP
group=CCMP TKIP
psk="The_WPA_SHARED_KEY_"
}
 
$ sudo reboot
 
$ sudo ifconfig wlan0
 
$ ping yahoo.com

Raspberry Pi

I ordered a couple of Raspberry Pis from Newark Element 14 two weeks ago. I’m ecstatic to say the least. I can’t wait to get my hands on the Raspberry Pi. The estimated shipment date is November 19. It will probably take 4-5 days for the shipment to arrive home.

I’m not sure yet what I plan to do with the Raspberry Pi yet. I will probably start with a media center for streaming music, videos, movies, and radio stations. The other Raspberry Pi, I will most likely use it for other projects. November 19 can’t get here soon enough.

Debian will probably be my OS of choice. What kind of services do I plan to install? Maybe, a web server, FTP server, PHP and MySQL database. I could install TeamSpeak or Ventrilo server as well. Who knows. There are tons of possibilities. X10 home automation also is another possibility.