473,246 Members | 1,380 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

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
6 2260
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Paradigm | last post by:
Does anyone know how I can use vba in access to run a script that is stored as a file. I can make a connection to mysql database and run sql statements but this sql statement is very long (creates...
17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
2
by: Murphy | last post by:
I am new to Linux and am migrating a SQL Server DB with an Access front end to MySQL and ? What options are available under Linux for development of a client front end without copious amounts of...
3
by: zaphod | last post by:
I want to use MS Accesss as a front end for an existing MySQL database. Since Acceess doesn't have the same data types, eg. ENUM, am I tied to the smaller subset of types supported by Access? I'm...
10
by: Mike | last post by:
I recently had a Microsoft Access 97 database corrupt on me. The setup is a split database (front end/back end) setup with about 10-15 users. This is the first corruption in something like a...
3
by: Bob | last post by:
All, I have read through lots of postings regarding my concerns, but I haven't found what I am looking for. The center (non profit University) where I work collects scientific data about...
9
by: Daven Thrice | last post by:
If I have a fairly big Access MDB, that is relational, and has, say, 100 objects (forms, reports, modules, etc.), what is the path to get this database "online". Is there a way to put the tables...
2
by: Beda Christoph Hammerschmidt | last post by:
MySQL, Access, ODBC, Replication Hello, i have a central mysql database that is accesed by multiple clients running MS Access with ODBC. This works fine if Access has a permanent connection...
5
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a...
49
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code,...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.