Ubuntu – Using MS Access to import from thesql

MySQL

I'm newer than a "Newbie" so I'm sure most will get a laugh out of my question(s). I've managed to install Ubuntu 16, Mysql, PHPMyadmin, MySql Workbench and several other applications with the help of all the information via searches. Basically my big problem is that I can't get access to connect to Mysql database. Here are some of the results when I test things.

  1. Telnet

    [5.7.17-0ubuntu0.16.04.1Z<L!k8Mk[>rGZM                                              3:7mysql_native_password
    Connection to host lost
    
  2. Trying get external data in Access on Windows 10 system. I select With SQL Server authentication and enter the Login ID & PW (I created a user for Mysql already)

    Connection Failed: SQLState: '01000' SQL Server Error: 2
    

There was more and when I checked the web I was told to edit /etc/mysql/my.cnf.
Also said to:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Do I substitute 'myuser'@'localhost' for my actual username? and what about 'mypass'?

Best Answer

  • You need to configure a user for remote access. This will provide for you to connect to your database using any client including Microsoft Access.

    First enable remote access to your database by editing the mysql server's configuration file. You will find this in /etc/mysql/mysql.conf.d/mysqld.conf

    Look for this link and ensure it's commented out or remove this line:

    # bind-address          = 127.0.0.1
    

    If you made a change to the file, be sure to restart the mysql server with:

    $ sudo systemctl restart mysql
    

    Now you will have to create a user, or ensure a current user can connect from remote. You can do this with these steps from a terminal command:

    $ mysql -u root -p
    

    Answer the password prompt with the root password and continue with:

    mysql> GRANT ALL ON mydatabase.* TO 'myuser'@'%' IDENTIFIED BY 'mypass';
    

    The variables of that command are mydatabase, myuser, and mypass.

    mydatabase    - The name of the database
    myuser        - The user with access to the database
    mypass        - The password for the user
    

    The command will create the user if it doesn't exist and assign the password "mypass" or whatever you put in that field for the user. If the user already exist it'll just make the "mypass" the password for that user.

    You can test the connection by running this from the commanline from this computer or from any computer.

    $ mysql -h [hostname/or IP address] -u myuser -p
    

    When you press ENTER it will prompt you for your password and connect you to the server if the credentials are correct. You can then run this to load the database:

    mysql> use mydatabase;
    

    The commandline test is what the client application (in this case, Microsoft Access) would be performing automatically. When you configure the client, Microsoft Access (or any other GUI database client, it'll ask you for the these things:

    • host (which you supply with the hostname or ipaddress)
    • database
    • userId
    • password

    The command will create the user if it doesn't exist and assign the password "mypass" or whatever you put in that field for the user. If the user already exist it'll just make the "mypass" the password for that user.

    You can test the connection from by running this from the commanline from this computer or from any computer.

    $ mysql -h [hostname/or IP address] -u myuser -p
    

    When you press ENTER it will prompt you for your password and connect you to the server if the credentials are correct. You can then run this to load the database:

    mysql> use mydatabase;
    

    The commandline test is what the client application (in this case, Microsoft Access) would be performing automatically. When you configure the client, Microsoft Access (or any other GUI database client, it'll ask you for the these things:

    • host (which you supply with the hostname or ipaddress)
    • database
    • userId
    • password

    The default mysql port is 3306. Your client will give you an option to provide the port if you have configured your remote to be different. You'll have to set your router for port forwarding to your mysql server computer. This will be port 3306 or a different one if you configure your server different from the default.

    To test the connection from the commandline in specifying the port you would use:

    $ mysql -h [hostname/or IP address] --port 3306 -u myuser -p
    

    Change the 3306 to match how you have your server configured if you change it from the default.

    As far as your question, "Do I substitute 'myuser'@'localhost'", the myuser can by your name, or whatever name you decide to give it. It's up to you if you want it to be the same name.