Jet ODBC to mySQL DSN-less Connection

[BillCo]

I need to Connect to an online mySQL database from Access using JET
ODBC with no saved DSN.

Anyone out there who as made this work and can give example of how
it's done? I've seen lots of posts saying it's possible, but no code.

This has to be Jet ODBC, because the users are technophobes and I
wouldnt expect them to download and install a myODBC driver - or
create a working DSN for that matter. My visiting the sites and
configuring each computer isnt an option.

There will be no heavy duty data transfer, I'm just using the mySQL db
to allocate resources between remote locations.

So if there are other alternative ways to connect to a mySQL db from
Access that would work for this situation, I'm all ears!

I'll continue searching and will post the solution, if I find one - in
the mean time any help would be HUGELY appreciated.

Thanks in advance

[Albert D. Kallal]

I need to Connect to an online mySQL database from Access using JET

ODBC with no saved DSN.

There is a sql server example here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

>

This has to be Jet ODBC, because the users are technophobes and I
wouldnt expect them to download and install a myODBC driver - or
create a working DSN for that matter. My visiting the sites and
configuring each computer isnt an option.

You sadly mistaken here. To use JET odbc with mysql, you need
the MySql drivers...else how is JET supposed to know how to
read database it never seen? It is up to the vendor to provide a
ODBC driver. The reason it done this way so that oracle,or
Informix, or MySql, or sql-server vendors simple write a
ODBC driver, and then presto -- jet can then read that
database it never seen, or used, or even how the data
base works!

So, I not sure where you got the idea that no new database
drivers are not going to be needed, nor installed. You most
CERTAINLY have to install those MySql drivers.

Check out the MySql newsgroups and support for some
example connection strings to MySql using odbc, those
connection examples will work with ms-access...

>I'll continue searching and will post the solution, if I find one - in
the mean time any help would be HUGELY appreciated.

Check with the MySql support people for example DSN less
connection strings. They are the ones that should be providing
you with this information. It sounds like their support is not
helping you much. There is tons of examples using sql server,
but the MySql folks will give you the conneciton strings
needed if you ask them....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Gord]

Once you have the MySQL ODBC driver installed (and there's no getting
around that, as Albert has explained) you can try using the sample
connection strings here:

http://www.connectionstrings.com/?carrier=mysql

On Feb 14, 7:09 am, "Albert D. Kallal" <[e-mail]>
wrote:

I need to Connect to an online mySQL database from Access using JET
ODBC with no saved DSN.

There is a sql server example here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

This has to be Jet ODBC, because the users are technophobes and I
wouldnt expect them to download and install a myODBC driver - or
create a working DSN for that matter. My visiting the sites and
configuring each computer isnt an option.

You sadly mistaken here. To use JET odbc with mysql, you need
the MySql drivers...else how is JET supposed to know how to
read database it never seen? It is up to the vendor to provide a
ODBC driver. The reason it done this way so that oracle,or
Informix, or MySql, or sql-server vendors simple write a
ODBC driver, and then presto -- jet can then read that
database it never seen, or used, or even how the data
base works!

So, I not sure where you got the idea that no new database
drivers are not going to be needed, nor installed. You most
CERTAINLY have to install those MySql drivers.

Check out the MySql newsgroups and support for some
example connection strings to MySql using odbc, those
connection examples will work with ms-access...

I'll continue searching and will post the solution, if I find one - in
the mean time any help would be HUGELY appreciated.

Check with the MySql support people for example DSN less
connection strings. They are the ones that should be providing
you with this information. It sounds like their support is not
helping you much. There is tons of examples using sql server,
but the MySql folks will give you the conneciton strings
needed if you ask them....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

[Tim Marshall]

BillCo wrote:

I need to Connect to an online mySQL database from Access using JET
ODBC with no saved DSN.

In addition to Albert and Gord's responses, note that the ADO methods
for DSNless connections make the use of MS Access reports difficult, if
not impossible. IOW, you can't set a report recordsource to and ADO
recordsource
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[BillCo]

Thanks Albert - that clears things up considerably! No way around
MyODBC, huh? That makes sense. Getting users to install ODBC drivers
really isnt an option - so I guess I'm back to the drawing board!

Thanks to all for your help

[Albert D. Kallal]

"BillCo" <co**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

Thanks Albert - that clears things up considerably! No way around
MyODBC, huh? That makes sense. Getting users to install ODBC drivers
really isnt an option - so I guess I'm back to the drawing board!

You could consider using web services, or even the wininet library to use
http gets from the web site....

however, this would requite you to code on the web side also. If you using
wininet, then you can
gab, and communicate with the web site (if you write stuff on the web site
to handle this).

You could also consider web services...but, then the end user will have to
install the soap tool kit add in for
ms-access, and run .net services on the web site.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Tim Marshall]

BillCo wrote:

Thanks Albert - that clears things up considerably! No way around
MyODBC, huh? That makes sense. Getting users to install ODBC drivers
really isnt an option - so I guess I'm back to the drawing board!

I don't know - if I can get the people in my environment who are still
living in pre-desktop comput er times to do this, you must have a crowd
of australopithecus there...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[BillCo]

I don't know - if I can get the people in my environment who are still
living in pre-desktop comput er times to do this, you must have a crowd
of australopithecus there...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

australopithecus ...you have no idea - I'm actually dealing with
lucy! But what's worse, the user that is terrified of the computer and
completely freezes up when you try to explain things (no matter how
slow, simple and calm you go) - or the guy who _thinks_ he knows what
he is doing when he updates random drivers?

I think I've settled on going around the offices and installing the
componants myself - turns out that they need other stuff too.