ms access front end with mysql database issues

[bchaney]

i have a ms access 2003 front end made and in place and it talks to, through odbc connection, mySQL database. when it's setup and running on the desktop that i made it on it runs fine and without glitches. however, i want to be able to put this application (ms access *.mde file) on a shared hard drive and have users use this application from multiple locations (this is a small database project i started for work).

the problem i'm running into is that when i open the file from another computer on the same network it says odbc connection failed. now i went to mysql's site and downloaded all the drivers to run the server links. however i'm stuck when i'm configuring the myodbc driver. i'm not sure what to put in the server box for the config. i know the name of the computer and the ip address is set up for dns (changes everytime computer power is cycled). can someone fill me in on what i should be putting there in that box so i can remotely access mysql database? i know it must be something simple. the solution is slipping me. lol

Thanks

 

[Banana]

1) I wouldn't use a shared front-end. This causes more problems than necessary.

2) Each computer needs to have their own copy of MyODBC.


So, you'd want to distribute the MDE and installer for MyODBC to your users to install on their computer.

Consider using DNS-less connection so you don't have to use DSN which is not easy to share with users. Google on this keyword for some samples on this.

HTH.

 

[bchaney]

if everyone in the work environment has ms access 2003 then i'd just have to copy multiple mde files right? is that what you mean by distribute?

getting a copy of myodbc for every user isn't too difficult. i was asking about my problems setting it up. when setting up the driver for use with the application it asks me what the server name is. now on the local machine it's an ip address. what do i put in the field there to see the server across the network? i tried various mixes of machine name with ip address of machine to ip address of the server and i can't figure out the proper string to put there to see the server.

can you give me some pointers or show me websites that would help me in this? everything i've seen so far has been setting up the driver on the local machine and not a remote machine like what i got.

Thanks

 

[Banana]

There's few ways to do it.

Easy to do but not as flexible:
1) Instead of using a name, use an IP for server. This will work only if the server machine has a static IP (and it should even if you use name anyway). You may need to get your network administrator involved and verify that the static IP is assigned. Then depending on what is the requirements, either use the private IP or public IP. Generally, any machine has both private IP which is usually in form of 10.x.x.x, 172.168.16.x-172.168.31.x, and 192.168.x.x and a public IP which can be anything else except for the private IP block, 0.0.0.0, 127.x.x.x. If you only want it to be accessible internally and you don't need to have any external users, use the private IP. But if you do have external users *and* security is not a problem for you, use public IP. (Note that for security/confidentiality reasons, some business prefer to require a VPN login or similar so you'd still use the private IP even from external but you need a VPN / SSH / rlogin / whatever software)

2) If you want to use name rather than IP then you typically just use the machine name (in Windows, that'd be the "Computer Name", as found on properties if you right-click on "My Computer") for internal network. For external, you have to have a domain name associated with your public IP that your server is using. So if your domain is basically "www.mycompany.com" and your network is configured to forward port 3306 (or whatever port you choose for MySQL) to your server, then that's your server name.

HTH.

 

[bchaney]

okay i did what you said for option number 2. now i'm getting host is not allowed to connect to this mysql server. i put in the full computer name from my computer properties window. it has a domain name with it. is that important? the full name is maintenanceloft.companyname.local and that's when the error pops up. i also tried just maintenanceloft but still same message.

Thank you for your help so far.

 

[Banana]

Just to be explicit: We're talking about a private network? No external users? Also when you say domain, do you mean a web domain or an Active Directory domain? Those are very different things and thus should not be confused.


Generally, you only need your computer name if you are in the same domain. If you/other users are in different domain, then you need to use fully qualified domain name. But names won't do you any good if there's no way to know which computer is named so. Typically, if you have Active Directory, it should also already handle the identifying the machine by names.

You can validate this by doing a ping (open a cmd.exe, type "ping <your machine name>" and seeing if you get a response. If it can't find the machine then your client program can't either. This then indicates that no other machines know what server's name is and doesn't know how to find the address. You'd troubleshoot this by looking at the local DNS server (may be on the domain controller if you only have one server running Active Directory).

Note further that if your server doesn't have the appropriate port opened, it may reject any attempts to connect so you also need to check this.

 

[bchaney]

oh okay. yeah i did the ping with just my computers name and it pinged fine. as far as i know it's a private network. so we know now its an active directory and that i can ping the computer from a remote source just by using the machine name. i'll check the port with IT here.

Following some of your advice from the other responses i also found that mysql's user should be set to remote. i can't figure out if my user is set to that or not. could that be causing some issues to?

 

[Banana]

Generally, when users log in to mysql, they have the form of:

user@theirdomain.nameoftheirmachine

or

user@theirwebpublicdomain.com

or

user@192.168.0.100


Depending on how strict you want to be, you can either create a login like

user@%

to allow an user named "user" to login from anywhere, or

user@domain.%
user@192.%

to restrict login to only certain domain/IP block. Or go for full shebang:

user@domain.machinename

which locks the user to only this one machine and they can't log in anywhere else.

Which is appropriate is entirely up to you.

 

[bchaney]

i talked to IT and the port 3306 is not blocked and confirmed we are active directory. so i brought up all the ports that were open and listening through command prompt but 3306 isn't listening. it's established but not listening.

 

[Banana]

Two ways to positively confirm you can contact MySQL:

a) In the Bin directory, use MySQLping (in a cmd.exe)
b) Use telnet to telnet to machine using port 3306. If you get gibberish, you know you've contacted MySQL successfully.

Do you get any error? What is the message?

 

[bchaney]

cmd.exe i couldn't ping mysql. used:

c:>ping mysql
c:>mysqlping

neither worked

i also tried telnet to open port 3306. error message is:

Microsoft Telnet> o 3306
Connecting To 3306. . .Could not open connection to the host, on port 23: Connect failed

 

[Banana]

Sorry, I didn't remember the command right. But even so, you need to be in the bin directory because I don't think MySQL command line utilities are in the PATH by default. So have your cmd.exe cd to the bin diretory in the MySQL folder.. something like this:

C:\Program Files\MySQL Server X.X\bin\>mysqladmin -ping <machine name>

(I'm guessing on the path - double check the path.

As for your telnet... you should not be seeing "port 23"... the syntax I would use is:

telnet machinename 3306

 

[bchaney]

okay that did something now. my syntax for telnet was (so you can check):

microsoft telnet> o maintenanceloft 3306

and it brought this up:

z(then some kind of diamond shape)Host 'maintenanceloft.companyname.local' is not allowed to connect to this MySQL server.

so i guess this means that it does see mysql but mysql isn't letting it communicate right? i did this for both the local machine and a remote machine both same results

for the command prompt part of it here is my path and result:
c:\program files\mysql\mysql server 5.1\bin> mysqladmin -ping maintenanceloft
mysqladmin: connect to server at 'localhost' failed
error: 'access denied for user 'odbc'@'localhost' (using password: yes)

which is wierd because my user or password doesn't match those values.

Thanks so much for your help. i really appreciate it. this problem is the only thing holding me up.

 

[Banana]

No - it's working perfectly.

The reason you are seeing this is because by default, it tries to log in as 'odbc'@'localhost'.

You need to pass in the user and password. To test this:

c:\program files\mysql\mysql server 5.1\bin> mysql -h maintenanceloft -u <your user name> -p

then type in the password when it prompts for it. If you can log in, then you've confirmed that it's no longer a MySQL login issue but now a ODBC issue which is usually simple to resolve.

 

[bchaney]

okay i did all of that. i entered in my password at the prompt but it returned host is not allowed to connect to this mysql server. i did all of this through the command prompt on the local machine that has mysql server installed.

i'm still tryin to figure out what my user is set up for in mysql to make sure remote is enabled. from what i'm reading online (now that i know the terminology to look up, lol) that seems to be the common cause for what i'm experiencing.

i can open up the mysql command line and log in just fine. i just tried that after the command prompt attempt. both times same machine just two different command prompts.

 

[Banana]

As I explained earlier, if your mysql server does not have appropriate entries in the users table, it won't allow users to log in.

If you want to log in from any host, you need to issue an ADD USER '<username>'@'%' ... (Look up the manual for the complete syntax). Or you can use domain with wildcard for a bit more restriction.

 

[bchaney]

alright thanks again for your help in making sure it was just a parameter issue (pretty much i screwed something up, lol). i'll post again after i talk with mysql and look up their manual. at least that way the final result will be here for someone else if they have a similar problem.

 

[bchaney]

alright i think i got it working. it was a setting issue on my end. this site helped me out to properly set up a user account step by step.

http://crunchbang.org/archives/2008/04/17/create-mysql-user-accounts-from-the-command-line/

i was continuously creating user accounts in the improper syntax:

user@192.168.%.% is wrong
'user'@'192.168.%.%' is the right way

then use a grant all command in mysql command line to grant all privileges to the user (for testing obviously, you wouldn't want remote users full access to mysql)

this allows access through only this class on our domain. i tried both ways and the second allowed me to connect through other computers.

once i put the server name or ip address into the server field, put the new remote user and password in, selected database field and hit test, the connection worked.

thanks again for all the help. i hope this helps other users.

 

[Banana]

Thanks for sharing.

I'm surprised that it accepted user@localhost - I thought that was considered a error and would have had nudged you toward 'user'@'localhost' syntax. But maybe it's just my memory being fuzzy.

Glad you're sorted.