MS Access

[RonB]

Sir

Can the connection string format of myodbc 3.5 be used in vba code within MS
Access or does one only have the option to set up an odbc data source.

I have a test Access db in which I can create table links to a MySql db
located on a web server using an odbc data source.
However, when I try to use the connection string method, I get the 3170 ISAM
error.  The format of the connections string matches all the examples I can
find on the web.

Also, if I try to to set a reference in VBA by browsing, I am told that a
reference cannot be made to the selected file.


I'm new to MySql.  Hence, I have downloaded the latestest versions.



Thank you for any help that can be given.




Regards

Ron Bolton

t:0117 9790233
m:07801 352638

www.baselogicsoftware.co.uk



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lists@...

[Jess Balint-2]

Ron -

Yes, it is possible to use a "DSN-less" connection through Access and we
use it in some of our test-cases. Can you provide more information on
the error you received? Maybe the relevant portion from the ODBC trace
log?

Jess

On Wed, Jun 11, 2008 at 09:30:22PM +0100, Ron wrote:

> Sir
>
> Can the connection string format of myodbc 3.5 be used in vba code within MS
> Access or does one only have the option to set up an odbc data source.
>
> I have a test Access db in which I can create table links to a MySql db
> located on a web server using an odbc data source.
> However, when I try to use the connection string method, I get the 3170 ISAM
> error.  The format of the connections string matches all the examples I can
> find on the web.
>
> Also, if I try to to set a reference in VBA by browsing, I am told that a
> reference cannot be made to the selected file.
>
>
> I'm new to MySql.  Hence, I have downloaded the latestest versions.
>
>
>
> Thank you for any help that can be given.
>
>
>
>
> Regards
>
> Ron Bolton
>
> t:0117 9790233
> m:07801 352638
>
> www.baselogicsoftware.co.uk
>
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:    http://lists.mysql.com/myodbc?unsub=jbalint@...

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lists@...

[Whisler, David]

I switched from DSN to DSN-less with my MS-Access front-end and Oracle
back-end database apps a year ago and it works really well.  So much
easier to manage than DSN's.

Basically, I put the following text in a text file and reference it when
creating a new table-link in MS-Access to a table on my Oracle Server.


[ODBC]
DRIVER=Oracle in OraHome92
SERVER=dbserv2
DBQ=dbserv2

Of course you'd need to change the "Driver" variable to be the name of
the MySQL driver and the "Server" and "DBQ" variables to the name of
your MySQL instance.



David K. Whisler
Oracle & MySQL Database Adminstrator & Developer
The William States Lee College of Engineering
UNC Charlotte


-----Original Message-----
From: Jess Balint [mailto:jbalint@...]
Sent: Thursday, June 12, 2008 1:53 PM
To: Ron
Cc: myodbc@...
Subject: Re: MS Access

Ron -

Yes, it is possible to use a "DSN-less" connection through Access and we
use it in some of our test-cases. Can you provide more information on
the error you received? Maybe the relevant portion from the ODBC trace
log?

Jess

On Wed, Jun 11, 2008 at 09:30:22PM +0100, Ron wrote:
> Sir
>
> Can the connection string format of myodbc 3.5 be used in vba code
within MS
> Access or does one only have the option to set up an odbc data source.
>
> I have a test Access db in which I can create table links to a MySql
db
> located on a web server using an odbc data source.
> However, when I try to use the connection string method, I get the
3170 ISAM
> error.  The format of the connections string matches all the examples
I can
> find on the web.
>
> Also, if I try to to set a reference in VBA by browsing, I am told
that a

> reference cannot be made to the selected file.
>
>
> I'm new to MySql.  Hence, I have downloaded the latestest versions.
>
>
>
> Thank you for any help that can be given.
>
>
>
>
> Regards
>
> Ron Bolton
>
> t:0117 9790233
> m:07801 352638
>
> www.baselogicsoftware.co.uk
>
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:

http://lists.mysql.com/myodbc?unsub=jbalint@...

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=dkwhisle@...


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lists@...

[RonB]

Jess

Thank you for your email.


The driver seems to be myodbc3.dll plus other files of the family.


My connections string is:
        LINK_MYSQL = "DRIVER={MySQL ODBC 3.51 Driver};
Server=http://www.###.co.uk; UID=###;PWD=###;Option=16387;database=###"

It fails on the following VBA code:

            DoCmd.TransferDatabase acLink, "ODBC Database" _
                           , LINK_MYSQL _
                           , acTable, "tblTest1", "tbltest1"

Error =  Run-time error 3170 : Could not find installable ISAM


Creating the link via Link Manager with a DSN ODBC Data source is fine.  I
can open the table and enter data etc.




I have tried adding the dll as a Reference in the VBA edit window by
browsing to it, but get the message:
               
        Can't add a reference to the specified file.


I have tried to register it into the registry with similar results.
However, I assume it is already registered since I have set up a DSN entry.



I'm sorry I can't include any trace information.
I've tried setting the trace up via the tab on the data source window but no
file was created in the folder I selected.
Is there some other way to do this?



As an aside if it may have any baring.

As I am an application developer using MS products, I do have two versions
of Visual Studio, three versions of .net framework and all the Office 2003
suite installed on my laptop, plus various other software.


I am running Windows XP sp2,, Access 2003 sp2, VBA 6.5



Regards

Ron Bolton

 

-----Original Message-----
From: Jess Balint [mailto:jbalint@...]
Sent: 12 June 2008 18:53
To: Ron
Cc: myodbc@...
Subject: Re: MS Access

Ron -

Yes, it is possible to use a "DSN-less" connection through Access and we
use it in some of our test-cases. Can you provide more information on
the error you received? Maybe the relevant portion from the ODBC trace
log?

Jess

On Wed, Jun 11, 2008 at 09:30:22PM +0100, Ron wrote:
> Sir
>
> Can the connection string format of myodbc 3.5 be used in vba code within
MS
> Access or does one only have the option to set up an odbc data source.
>
> I have a test Access db in which I can create table links to a MySql db
> located on a web server using an odbc data source.
> However, when I try to use the connection string method, I get the 3170
ISAM
> error.  The format of the connections string matches all the examples I
can

> find on the web.
>
> Also, if I try to to set a reference in VBA by browsing, I am told that a
> reference cannot be made to the selected file.
>
>
> I'm new to MySql.  Hence, I have downloaded the latestest versions.
>
>
>
> Thank you for any help that can be given.
>
>
>
>
> Regards
>
> Ron Bolton
>
> t:0117 9790233
> m:07801 352638
>
> www.baselogicsoftware.co.uk
>
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:    http://lists.mysql.com/myodbc?unsub=jbalint@...

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lists@...

[RonB]

I have finally solved my problem.

'ODBC' needs to be inserted into the connection string.


My cfinal onnections string is:
        LINK_MYSQL = "ODBC; DRIVER={MySQL ODBC 3.51 Driver};
Server=http://www.###.co.uk; UID=###;PWD=###;Option=16387;database=###"