Access front end MySQL backend conxtion troubles

[mcamp]

Hi all, hope someone can help me!

I cannot get a connection to this MySql database for the life of me I have tried everything.

this is where im at at the moment http://itc.virginia.edu/desktop/web/mysql/msaccess.html. ip, port and credentials are all good but I keep getting the 'Connection Failed: Cant connect to kayla1 on blabla"

Anyone have a clue? Wrong driver maybe??


What im working with:
* trying to link Access tables to a mysql database
* .mdb
* MySql ODBC 5.1 64bit driver
* msjet dll file fix
* access 03 & 07 connections failed. including using named pipes instead of tcp/ip

get a different error when trying to connect in VBA using a different driver.


Code:

Dim strDataBaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
 
Private Sub Command1_Click()
On Error GoTo Command1_Click_Error
Dim b as Long
strDBCursorType = adOpenDynamic  'CursorType
strDBLockType = adLockOptimistic   'LockType
strDBOptions = adCmdText         'Options
 
Set cn = New ADODB.Connection
 
 
cn.Open ConnectString()
 
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
 
    Set rs = New ADODB.Recordset       'Creates record set
 
    strSQL = "SELECT * FROM clientLeads"
 
    rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
 
 
if rs.Eof then
   Goto ExitSub    
else
    For b = 0 To rs.RecordCount -1
    'docmd.openform ""
    Next b
end if
 
ExitSub:
 
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 
On Error GoTo 0
Exit Sub
 
Command1_Click_Error:
    MsgBox "Error " & Err.Number & " " & Err.Description
 
End Sub
 
Private Function ConnectString() As String
Dim strServerName as String
Dim strDatabaseName as string
Dim strUserName as string
Dim strPassword as string
 
 
 
strServerName = "72.167.233.58" 
strDatabaseName = "kayla1"
strUserName = "kayla1"
strPassword ="*******"
 
ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword  & _
                ";OPTION=3;"
 
End Function

Result: Error [ODBC Driver Manager] Data source name not found and no default driver specified


Please help me if u can, Im stumped. I have installed other mysql drivers but no luck. Only Mysql ODBC 5.1 shows up on the connection screen in Access

i just need to remote host this back end

:*(

 

[james_lankford]

try installing this first - just see if you can connect from your machine at all
a free MySQL Manager
EMS SQL Manager for MySQL Freeware
http://www.sqlmanager.net/en/tools/free

if you can't connect with it then the problem is not the odbc

 

[Norie]

Have you checked the server name, database name etc?

Also, did you try the actual server name?

 

[mcamp]

james - I DLed the EMS SQL Manager for MySQL and cannot connect to the mysql database with that either.

I get the following error

Error Code: 2003
Please check the correctness of port and
 host name you've entered and also if the 
remote server is run. It often happens that 
the port through which the connection is 
set with MySQL server (normally, it's 3306) 
is closed for the security reasons by local 
firewall, corporate firewall or remote 
server firewall. The remote server port can
 also be closed by ISP, or TCP/IP protocol 
support is disabled on MySQL server. Please 
check this with your system administrator 
or ISP. In order to avoid this limitation you 
can use SSH and HTTP tunnels.

Also failed with the named pipe kayla1.db.6614179.hostedresource.com, as well as with http tunnels enabled

I CAN however connect to the mysql database from a website on a seperatly hosted webserver.

Why is it I cant connect to this local Access database? My machine??



and yea norie I have checked the server name, dbase name, credentials etc. np connecting from a website

 

[james_lankford]

...Why is it I cant connect to this local Access database? My machine??
...

so the MySQL database is on your local machine ?
the same machine that has MS Access on it ?

try changing the Seerver to localhost

I made a MySQL database on my machine and then went to MS Access and created an ODBC machine DSN with these settings and was able to connect to it

Data Source Name = MySQL kayla1 Data Source Name
Description = MySQL kayla1 Data Source Description
Server = localhost
port = 3306
User = kayla1
Password = booger
Database = kayla1

first I did it with the user
root
and after that worked I added kayla1 to the database and granted all privileges and changed ODBC to use kayla1
that also worked

of course if MySQL isn't local, I don't know what to do.
I don't have another computer to install and test on

 

[mcamp]

thnx for the reply james. yea it is hosted on a third party server, not on my machine unfortunantly. i used all the same settings only instead of local host the ip of the server

and like i said i can connect from other webservers but not from my machine even wierder I cant even connect from the SQL manager. gotta be something with my computer right.

-firewall is also off fyi

gunna try to connect at work today and see what happens. thnx guys!

 

[mcamp]

I also tried to connect to another established mysql database from the mysql manager with no luck, from home and work. But I CAN connect to these databases from a webserver.

whats the principal difference between my machines and a webserver in regards to connecting to mysql databases?

or

does anyone else have any ideas as to how I can remote host this backend??? I have plenty of webspace, but not luck hosting these tables in any fashion.

please help if u can i need some brain storming

 

[Norie]

Have you checked that everything is configured correctly for what you want to do?

Not just on your side, but also the server side?

By the way, why does your connection string have the driver as {MySQL ODBC 3.51 Driver}?

Shouldn't it be {MySQL ODBC 5.1 Driver}?

 

[mcamp]

I DID IT!!! woohoo! only now having some serious errors. Ok so server side apparently I had direct access turned off. stupid mistake. anyways I am connected to the mysql database and all the tables are succesfully linked.

The database was fine for about 5 minutes... then everything broke.

I keep getting an "Error in loading DLL" message. Every button, and every native function in all of Access gives me that error. I cant even get into the vba to unhide the database window without the message coming up and freezing me out of the application. I cant do anything

However everything was working perfectly for the first 5 minutes.

And if I close that database and open a different database everything works fine.

How did I break it? Any1 have any ideas how I can fix this? Its like I finally get it working then the whole f*king thing breaks, i wanna scream right now! the UNlinked Backup database works fine

** error still exists after fresh reboot, and is specific to this database

 

[Norie]

How did you fix it?