467,915 Members | 1,517 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Access front end to MySQL

I am using Access 2K as a front end to MySQL running on a Linux server.
I am having trouble connectiong to the server. MySQL control centre connects
and I can connect using a DSN data source. But if I connect using the
following code

Dim constr As String
Dim tbl As DAO.TableDef

constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" & SQLServerUser
_
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME
tbl.Connect = constr
tbl.SourceTableName = MYTABLENAME
DoEvents
CurrentDb.TableDefs.Append tbl

I get a Cannot find installable ISAM error message when trying to append the
table. It appears that a connection is made but that I cannot access any of
the tables. I have tried using other connection methods and get the same
results.

If I change the above to
constr = "DSN=MYDSNCONNECTION;DESC=;USER=" & SQLServerUser _
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME

Where MYDSNCONNECTION is the DSN connection set up in the data sources, the
code works and the table is appended.
I would prefer to use the first method since I will not have to create the
DSN on every workstation.
Can anyone help me with this.

Nov 13 '05 #1
  • viewed: 1930
Share:
6 Replies
I think you just need to remove the curly braces around the driver name.

On Mon, 21 Mar 2005 11:39:18 GMT, "Paradigm" <al********@hotmail.com> wrote:
I am using Access 2K as a front end to MySQL running on a Linux server.
I am having trouble connectiong to the server. MySQL control centre connects
and I can connect using a DSN data source. But if I connect using the
following code

Dim constr As String
Dim tbl As DAO.TableDef

constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" & SQLServerUser
_
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME
tbl.Connect = constr
tbl.SourceTableName = MYTABLENAME
DoEvents
CurrentDb.TableDefs.Append tbl

I get a Cannot find installable ISAM error message when trying to append the
table. It appears that a connection is made but that I cannot access any of
the tables. I have tried using other connection methods and get the same
results.

If I change the above to
constr = "DSN=MYDSNCONNECTION;DESC=;USER=" & SQLServerUser _
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME

Where MYDSNCONNECTION is the DSN connection set up in the data sources, the
code works and the table is appended.
I would prefer to use the first method since I will not have to create the
DSN on every workstation.
Can anyone help me with this.


Nov 13 '05 #2
This bit is from the MySQL manual

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"
"SERVER=localhost;"
"DATABASE=test;"
"USER=venu;"
"PASSWORD=venu;"
"OPTION=3;"
The curly braces are always used.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h6********************************@4ax.com...
I think you just need to remove the curly braces around the driver name.

On Mon, 21 Mar 2005 11:39:18 GMT, "Paradigm" <al********@hotmail.com> wrote:
I am using Access 2K as a front end to MySQL running on a Linux server.
I am having trouble connectiong to the server. MySQL control centre connectsand I can connect using a DSN data source. But if I connect using the
following code

Dim constr As String
Dim tbl As DAO.TableDef

constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" & SQLServerUser_
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME
tbl.Connect = constr
tbl.SourceTableName = MYTABLENAME
DoEvents
CurrentDb.TableDefs.Append tbl

I get a Cannot find installable ISAM error message when trying to append thetable. It appears that a connection is made but that I cannot access any ofthe tables. I have tried using other connection methods and get the same
results.

If I change the above to
constr = "DSN=MYDSNCONNECTION;DESC=;USER=" & SQLServerUser _
& ";DATABASE=" & SQLServerDatabase _
& ";SERVER=" & SQLServer _
& ";PASSWORD=" & SQLServerPassword _
& ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME

Where MYDSNCONNECTION is the DSN connection set up in the data sources, thecode works and the table is appended.
I would prefer to use the first method since I will not have to create theDSN on every workstation.
Can anyone help me with this.

Nov 13 '05 #3
Well, perheps, they're OK then, but other examples I've found on the Web don't
show curly braces being used. Did you at least try it without, and see if it
helps?

On Mon, 21 Mar 2005 18:52:41 GMT, "Paradigm" <al********@hotmail.com> wrote:
This bit is from the MySQL manual

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"
"SERVER=localhost;"
"DATABASE=test;"
"USER=venu;"
"PASSWORD=venu;"
"OPTION=3;"
The curly braces are always used.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h6********************************@4ax.com.. .
I think you just need to remove the curly braces around the driver name.

On Mon, 21 Mar 2005 11:39:18 GMT, "Paradigm" <al********@hotmail.com>

wrote:
>I am using Access 2K as a front end to MySQL running on a Linux server.
>I am having trouble connectiong to the server. MySQL control centreconnects >and I can connect using a DSN data source. But if I connect using the
>following code
>
>Dim constr As String
>Dim tbl As DAO.TableDef
>
>constr = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DESC=;USER=" &SQLServerUser >_
> & ";DATABASE=" & SQLServerDatabase _
> & ";SERVER=" & SQLServer _
> & ";PASSWORD=" & SQLServerPassword _
> & ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME
>tbl.Connect = constr
>tbl.SourceTableName = MYTABLENAME
>DoEvents
>CurrentDb.TableDefs.Append tbl
>
>I get a Cannot find installable ISAM error message when trying to appendthe >table. It appears that a connection is made but that I cannot access anyof >the tables. I have tried using other connection methods and get the same
>results.
>
>If I change the above to
>constr = "DSN=MYDSNCONNECTION;DESC=;USER=" & SQLServerUser _
> & ";DATABASE=" & SQLServerDatabase _
> & ";SERVER=" & SQLServer _
> & ";PASSWORD=" & SQLServerPassword _
> & ";PORT=;OPTION=;STMT=;;TABLE=" & MYTABLENAME
>
>Where MYDSNCONNECTION is the DSN connection set up in the data sources,the >code works and the table is appended.
>I would prefer to use the first method since I will not have to createthe >DSN on every workstation.
>Can anyone help me with this.
>
>


Nov 13 '05 #4
Try this link

http://www.able-consulting.com/MDAC/...DriverForMySQL

Bill Ehrreich

Nov 13 '05 #5
I have referred to this site before an I am using code which follows the
example exactly. I have of course tried other combinations.
The problem is that I can connect using a DSN connection but not a DSN less
connection. I always get a Cannot find installable ISAM error message.
Alec

<bi********@netscape.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Try this link

http://www.able-consulting.com/MDAC/...DriverForMySQL
Bill Ehrreich

Nov 13 '05 #6
"Paradigm" <al********@hotmail.com> wrote in
news:lp****************@newsfe3-gui.ntli.net:
I have referred to this site before an I am using code which follows
the example exactly. I have of course tried other combinations.
The problem is that I can connect using a DSN connection but not a DSN
less connection. I always get a Cannot find installable ISAM error
message. Alec

<bi********@netscape.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Try this link

http://www.able-consulting.com/MDAC/...SNLess.htm#ODB
CDriverForMySQL

Bill Ehrreich



Which version of MySQL (and MySQL ODBC) are you using? The password
authentication method changed in the latest version of MySQL. A
workaround is to do this from a MySQL console window (i.e., command-
prompt):

set password for <user> = OLD_PASSWORD('<new password>');

(the syntax above is subject to the vagaries of 2am thinking...but the
key is the OLD_PASSWORD function, which will allow the user to be used
for access tools that haven't been updated to support MySQL 4.1).
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by chicha | last post: by
2 posts views Thread by Murphy | last post: by
10 posts views Thread by Mike | last post: by
3 posts views Thread by Bob | last post: by
9 posts views Thread by Daven Thrice | last post: by
2 posts views Thread by Beda Christoph Hammerschmidt | last post: by
49 posts views Thread by Mell via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.