Use access with MYSQL (1 Viewer)

[Bee]

Hi,

Does anyone have an example of a front end made in access with MYSQL backend that they would like to share?

Any help will be very much appreciated. A link to a good tutorial will be excellent.

Thanks,
B

 

[The_Vincester]

No examples, but it's turning out to be easier than I thought to do...

The key is to go to the www.mysql.com page and download the ODBC driver. Once you do that you can set up your tables in mySQL (which is not too bad at all with a decent knowledge of databases). From there you can link the tables using ODBC and "use" the tables.

I'm just getting started myself, but it's not nearly as hard as I thought it would be.

Good luck!

 

[Bee]

No examples, but it's turning out to be easier than I thought to do...

The key is to go to the www.mysql.com page and download the ODBC driver. Once you do that you can set up your tables in mySQL (which is not too bad at all with a decent knowledge of databases). From there you can link the tables using ODBC and "use" the tables.

I'm just getting started myself, but it's not nearly as hard as I thought it would be.

Good luck!

I will try that when I have time. Thank you.

 

[mikebaldam]

Access Front End - MySQL Backend

Heres the easiest way I've dun it... if youre having hassle email me n ill send u a simple sample one...

http://www.funbucket.co.uk/forum/viewtopic.php?t=1681

 

[speakers_86]

What is the benefit of using Access as a front end for an online backend? Why not develop the front in php in on online web page?

If Access is the front end, and the user is in the middle of working, then looses the internet connection, are their any types of problems that may happen?

If you use this approach, would the link to the mysql need to be configured on every computer?

 

[Banana]

About same benefit as gained by using SQL Server, Oracle, DB/2, PostgreSQL:

a) More data can be stored beyond 2 GB.
b) Better support for concurrency.
c) Access to server-side code, simplifying the application logic where appropriate.


Benefits specific to MySQL:

a) Free in every sense of the word. (There's SQL Server Express but it comes with a cap)
b) Better control over transactions

WRT internet connection, regardless of the backend being used, Access 2003 basically dies when the connection is lost, giving you a spew of stupid 'Disk or network error' dialogs. This is not trappable, unforunately. However, if the internet connection has a hiccup, the ODBC driver usually handles it and does better, so even with that problem, Access can be more stable with a ODBC backend compared to a Access backend, and WAN connectivity is feasible.

You need to install MySQL's ODBC driver on every computer. In my last implementation, I distributed a installer that did that for users and thus automated the configuration.

 

[speakers_86]

a) More data can be stored beyond 2 GB.

I like that one! Thanks for the response banana. I am toying with website design, I have a lot to learn.

 

[speakers_86]

a) Free in every sense of the word. (There's SQL Server Express but it comes with a cap)

Except for the cost of domain and hosting, assuming you put the information online, right?

 

[Banana]

Depends.

If you have hardware for it, you can run MySQL on your own hardware and only need to have domain. Or you can pay a hosting company to maintain the MySQL server for your. Your decision.

It should be also added that if MySQL is entirely for internal use (e.g. there's no public-facing website that will interact with your database), then you don't even have to bother with the domain; just use IP addresses in which you pay a bit extra to your ISP to obtain a static IP address.

So, it depends on how much you are willing to maintain it yourself, what you need it for, etc. etc.

One point I meant to stress in my OP (but you probably knew that anyway), is that with a MySQL backend (as well any other DBMS) it is possible/feasible to have multiple front-end clients connect to it (e.g. a Access front-end for internal use, website store for public, and so forth). While you can indeed use Access as a backend to a website, the concurrency and performance is much suited for that kind of diverse uses.

Good luck.

 

[speakers_86]

It seems like nobody knows much about the attachment type in A07, but do you know how mysql would react if someone tried to use this field type?

Edit-nevermind. Is there a similar field type in mysql that can accept attachments? How could users add pictures or files?

 

[Banana]

A caution:

OLE Object/Attachment/BLOB field types can't be said to be equivalent, though they do similar functions. I would want to avoid mucking about with slight differences in the headers and metadata that may be wrapped around the field types and deal with BLOB directly from MySQL.

Here's a thread...see if this helps somehow.

 

[AJordan]

I've managed to read several MySQL white papers, and have converted one of my databases to MySQL. Now I have an access front end and a MySQL backend, I understand how to connect to MySQL (importing and linking the data to access) and under the Machine Data Source i set up a Data Source (ie localhost) and everything works fine.

My only question now is how would I connect to this data from another computer, how is this configured. I don't have a static IP address, but if this works I've found out I can get one. So Since my IP Address "Should" stay the same for atleast 15 mins to test, I need help configuring to a remote connection.

 

[Banana]

The only change you need to make to your connection string is to match the IP where the computer is located.

You need to find out what is the IP of the machine that has MySQL running and use that instead of the localhost.

Be aware - there are two different contexts to consider - you could have a "private IP" which is the IP that's assigned to a computer within the LAN, or a "public IP" which is the IP that world sees/thinks your computer is coming from. This is possible because computers are behind a router, so to the world, it appear as if there is one machine when in fact all computers behind a router are sharing the same public IP but have different IP address.

Anyway, the point is that if MySQL is another computer in same network, then you use the private network (look at ipconfig to get the IP address of the MySQL). If it's in a different network, then you need public IP and most likely you need to also set up secure connection as well configuring the router to forward all packets to the machine hosting MySQL.

Hope this helps.

 

[mikebaldam]

though if you can use a domain address (not ip) it will always point to the right IP address (they do change from time to time as servers are moved)

 

[AJordan]

Banana, I must say your help on this has been greatly appreciated. I have also come to understand a MySQL a lot better and will continue my knowledge in using it. I am still having trouble connecting but it's probably user error.

Here is the error
(HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'
(10060)

It appears that the two computers I am using to test both have the same IP Address, so maybe this is why I can't connect using the IP address. You mentioned the Private IP Address, but I'm unsure how to find this. I'm familiar with IP config and even checking for the IP through the network connections tool, but I am not sure what to look for.

Also while testing this I turned the Mcafee firewall options of on both computers and even created exceptions (within windows security panel) to enable outbound and inbound access to port 3306.

 

[Banana]

The ipconfig will tell you the private IP if you look at "IPv4 Address". Private IP addresses are always in those blocks:

10.x.x.x
172.16.x.x-172.31.x.x
192.168.x.x

Depending on which brand of router you are using, your machines will be using one of those formats.

Any other IPs are either public or not applicable. 127.x.x.x is an example of IP reserved for loopback and would be used for say, localhost connection rather than actual outbound connection. It's handy for testing whether the machine is actually accepting the connection.

Within a network, no computer should have same IP address. That would be A Bad Thing™. The one you looked at probably is a public address because as I explained earlier, when machines are behind a router, router "hides" all those machines and expose them as a single IP address to the world. When you are connecting remotely, you use the public IP. If they are in same network, you use private IP.

Did this help?

 

[Steve R.]

I am toying with website design, I have a lot to learn.

I am doing the same. I have a LINUX (Ubuntu) computer at home running MYSQL connected to a home LAN. I also have a WindowsXP computer where I use Firefox to connect to the webpage (database). I've just got to the point where I can do a very simple webpage data dump from MYSQL with PHP. It's going to be a long slow crawl to learn PHP, MYSQL, HTML, and CSS.

I just finished this past weekend moving the database from Access to MYSQL on the LINUX computer.

The advantage of this configuration is that, for now, I don't need an ISP.

I recently bought two books. They are appropriately intimidating.

Beginning HTML, XHTML, CSS, and JavaScript

Learning PHP, MySQL, and JavaScript: A Step-By-Step Guide to Creating Dynamic Websites

A couple of additional thoughts. On my LINUX computer, I have Open Office. Open Office offers BASE which would be akin to ACCESS and offers connectivity to MYSQL. I know very little concerning BASE, but it may be something to think about. Open Office Forum. I just located the user documentation Tuesday night. The OpenOffice.org Wiki

For my HTML editor, I am using SCREEM, I have no idea if it is a superior product or not, it came with UBUNTU.

Well I hope that we can all learn from each other.

 

[AJordan]

Ok, im getting close I realized the problem was the IP address I was using. The IPv4 I was using was for the Hamachi client I had already installed.

Here is the error I am getting now
Connection Failed: [HY000] [MySQL][ODBC 5.1 Driver]Host 'MyPC' is not allowed to connect to this MySQL server


LOL, how do I authorize myself?

_______
I figured it out, for user I am using root. When I first configured MySQL I did not check to enable root access from remote machines. I reconfigured and now I am able to connect to the DB on my other computer. Many thanks to everyone that helped out!
Is there a way to create other users other than root?

 

[Banana]

According to this page...

If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

Host ... is not allowed to connect to this MySQL server
You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.

If you do not know the IP number or host name of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. Then change the '%' in the user table row to the actual host name that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given user name.

On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc, or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.

Basically you need to create an entry in the host table to allow your username to log in from the client's IP.

To put it in an concrete example, here's how I have my user table set up:

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost | 
+------+-----------+
1 row in set (0.00 sec)

What this means that MySQL server will only accept connection from only user named 'root' from host 'localhost' (e.g. the same computer the MySQL is running on). It will not accept anyone else anywhere. If I wanted to allow my another computer on the same network to access to the MySQL, using the same user name, I have to add a new entry in the table:

mysql> grant select on test.* to 'root'@'192.168.0.%';
Query OK, 0 rows affected (0.62 sec)

Note that while I used the same user, 'root', I assigned a wildcard IP meaning that the root can connect to the MySQL any where as long the IP is between 192.168.0.1 to 192.168.0.255, effectively within the network only.

The user table now looks like this:

mysql> select user, host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| root | 192.168.0.% | 
| root | localhost   | 
+------+-------------+
2 rows in set (0.00 sec)

You also need to grant the appropriate permissions (e.g. SELECT, INSERT, UPDATE, etc.)

More info on GRANT statement.

HTH.

 

[AJordan]

I figured it out, for user I am using root. When I first configured MySQL I did not check to enable root access from remote machines. I reconfigured and now I am able to connect to the DB on my other computer. Many thanks to everyone that helped out!
Is there a way to create other users other than root?

Thanks for checking that info out for me, it will come in handy, the quick work around had to do with reconfiguring my version of MySQL communty server to allow root access from another machine (simple check box). Again thanks a lot for everyone who helped out