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

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!

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.

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


:D

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?

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.

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.

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?

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.

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?

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. (http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1837750&page=&view=&sb=5&o=&vc=1)

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.

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.

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)

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.

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?

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 (http://www.amazon.com/exec/obidos/tg/detail/-/0470540702/ref=ord_cart_shr?_encoding=UTF8&m=ATVPDKIKX0DER&v=glance)

Learning PHP, MySQL, and JavaScript: A Step-By-Step Guide to Creating Dynamic Websites (http://www.amazon.com/gp/product/0596157134/ref=ox_ya_oh_product)

A couple of additional thoughts. On my LINUX computer, I have Open Office (http://www.openoffice.org/). 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 (http://user.services.openoffice.org/en/forum/). I just located the user documentation Tuesday night. The OpenOffice.org Wiki (http://wiki.services.openoffice.org/wiki/Main_Page)

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.:)

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?

According to this page... (http://dev.mysql.com/doc/refman/5.1/en/access-denied.html)
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 (http://dev.mysql.com/doc/refman/5.1/en/grant.html).

HTH.

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:)

Be aware this is a security risk. If you have this:

'root'@'%'

this means the root user can connect to the server from anywhere in the world and is an open invitation to malicious users. This is why it's usually best to do a GRANT and restrict the IP range or host name accordingly.

I understand completely I just did it this way as an inhouse test between two computers. Before this becomes adopted I will research the information you provided and set up the permission appropriately.

Maybe I should start another thread but I am playing around with the ODBC linked table (in Access 2007) and I tried to manipulate a record (Change a name from John to Jane). However I recieved a nice alert and a message at the bottom left corner of the screen that "This Recordset is not updateable". We will use it mostly for running reports but if we are migrating the entire backend of our DB we will need to be able to update records in a non MySQL environment such as our Access front end.

Ah, yes, probably few more settings to configure.

You may find it beneficial to read this:

Beginner's Guide to ODBC (http://www.access-programmers.co.uk/forums/showthread.php?t=172243)

MySQL also has few specific configurations needed to work with Access - it should be covered at their site on the Connector/ODBC, under 'Configuration' and 'Note and Tips' (they have a section for Access there as well).

HTH.

Thanks, informative read (and thanks for putting it in a pdf format). Inside my two computer set-up, everything works great and as it should. I expanded the network to 3 computers and no problems, and I am happy with performance.

Although I did try to connect to MySQL database via WAN using my public IP address (found at whatismyip.com) and I couldnt get a connection. It could be that the test network I set up was using a cable modem and a router and the router was blocking the connection (not sure). I think I will try by just connecting the modem directly to the PC and see what happens.

No.

You never need to modify the security setting for the client, only the server because that's the place where it's listening for the connection. Therefore the router on the MySQL network has to be configured to forward data on port 3306 (or any port you use instead) to the MySQL's machine.

A better alternative is to use VPN or SSH or similar connection to make your machine "local" to the network so it then can access MySQL. This provides an extra protection and doesn't require you to expose MySQL to the internet.

Looks like I hit the wall. I have tried everything I have read and know to connect to my pc via WAN. I constantly get the unable to connect to 68.34.xx.x . I can connect using the private IP when I'm on the network, but when I get I use another internet connection it just wont connect.

I guess my only option is to find a mysql host that will allow a remote connection.

Did you investigate the routers or the network? This involves more than just your PC.

I have been using a desktop and a laptop. The Desktop is running the MySQL Community Server and I have the ODBC Connector installed on both the laptop and desktop.

When working within the LAN on the laptop I have no problem accessing the MySQL database files located on the desktop PC using the local IP address.

I then tried using an outside internet connection on the laptop to access the database, now using the public address of the network the PC is attached to. Found this address on whatismyip.com. This attempt failed.

Next, I have also configured the Dynex Wireless G Router for port forwarding on 3306.
I have made exceptions for the port within Mcafee and Windows Firewalls on both computers.

I try again to access the MySQL database over ODBC, using the public ip address and again I am unable to connect. I have also tried shutting down all firewalls just to see if that was a problem.

I have not been able to find a lot of information on this subject, but I can’t understand how I can connect through LAN but not WAN.

It still sounds to me you've not configured the routers.

To provide an example.

If I have a home network, and work network and I want to connect to the work network from home, I need to configure the routers or use a VPN software. To provide an example:

Home network:

My Computer (192.168.0.100) <- private IP

My router/modem (192.168.0.1) <- also private IP but is known as 'gateway IP'

My ISP (123.123.123.123) <- the public IP, assigned by ISP.


Then in my work network:

MySQL server (10.0.0.100) <- also a private IP

My router (10.0.0.1) <- a gateway, too.

Work ISP (231.231.231.231) assigned by ISP.


The bridge between two network is of course across the network, meaning one network only see the other's public IP.

If I tried this connection setup:

My home computer (192.168.0.100) --> MySQL Server (10.0.0.100)

what actually happens is this:

My home computer asks router (192.168.0.1) "Where is the 10.0.0.1?", and the router would reply "I don't have any machine here with that address." Thus the connection fails.

Now if I tried to use public IP:

My home computer (192.168.0.100) --> MySQL Server (231.231.231.231)

What happens is this:

My home computer asks the router "Where's the IP 231.231.231.231?", the router will then ask the ISP's DSN server, "Where is the IP 231.231.231.231?", and the ISP DSN will then ask other DSN server until it reaches the work ISP's DSN server which will reply, "here it is." and thus forward the connection request to IP 231.231.231.231.

But the work router (which is listening at 231.231.231.231 public iP) will say, "I do not allow any outside connection, so I'm rejecting and closing this connection."

Thus, you need to access the router directly and tell it to set up port forwarding. The port forwarding would be something like "TCP 3306 Any source IPs ==> 10.0.0.100".
So doing the connection above again, then the work router will be able to say, "Yeah, I have a rule telling me to forward this data to the 10.0.0.100" and it does, finally reaching the MySQL server and interacting with the MySQL.


To configure the router... keep in mind that it is dependent on which router you have, but we'll use a simple router you usually buy from Best Buy as an example. Hopefully you can then figure out how to configure your work router. Anyway, usually, if you buy say a D-Link router, it has a default IP of "192.168.0.1", or maybe "192.168.1.1". If you are uncertain, use the ipconfig and look up "Gateway IP".

Then in the internet browser, you type in "192.168.0.1" for the address bar. It will then connect to the router, and you will be prompted to log in. Usually the default setting is something like "Admin/(blank password", or "Admin/Admin" or "Admin/password". Again, consult your router's manual for the correct default login, if it never has been configured before. Then that's where you set up the port forwarding so it can then redirect all data on port 3306 to MySQL.

Now, you also need to be aware of the risk this places on your work network and for this reason, it is usually good idea to require some kind of secure connection, perhaps by using SSL, or VPN. VPN is different from the above steps because when you open a VPN software & successfully connect to the work router, VPN will then request your work router (10.0.0.1) for a local IP and get one... say, (10.0.0.200). Therefore, your home computer becomes "local" and thus can access MySQL server at 10.0.0.100, without need to go through the public IP.

I hope this helps....

I truly appreciate your help and support and patience, it has been great! I only wish I could just download your knowledge into my brain, lol.

I tried this very thing yesterday (logged on to my dynex router using http://192.168.2.1, and went to the section for port forwarding) I added a new rule "MySQL Server", TCP, Port 3306 and then I needed to enter a private IP address. I don't believe the MySQL Server already had a private IP so I entered ".5" (192.168.2.5) as the IP address for MySQL Server (Desktop was .2, the laptop was .3 another computer was .4).

However, it still wouldnt allow me to connect, I will attempt to rework and see if I can have success.

I have been switching to another ISP source to try to see if WAN would work. Is it possible to do an ODBC connection via WAN while connected to the LAN?

***Just found a website for port forwarding information with my router, I will try their instructions for MySQL Server
http://portforward.com/english/routers/port_forwarding/Dynex/DX-WEGRTR/MySQL_Server.htm

For the port forwarding to work reliably, the dynex router has to give MySQL server same private IP address every time- if the dynex router is capable, you can configure it to assign only 192.168.2.5 to MySQL server based on its MAC address.

What is the error you get now?

I'm pretty sure there's shouldn't be a problem with looping back using the public IP- it's the router's job to appropriately forward the request at port 3306 to the MySQL server, then it's MySQL's job to authenicate that request (which may be now 'yourusername'@'yourpublicIP' )

If you lived anywhere near me I'd buy you a beer!

So I take you got it working now? :)

Glad to be of help, and I will drink my beer tonight in the honors. ;)

Also, just so you didn't forget- be aware about the security ramifications of opening the port to the WAN. If you can work out a solution that requires secure connection, then you are probably better off.

Nonetheless, best of luck!