By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,306 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Jet ODBC to mySQL DSN-less Connection

P: n/a

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

Feb 14 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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


Feb 14 '07 #2

P: n/a
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" <PleaseNOOOsPAMmkal...@msn.com>
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
pleaseNOOSpamKal...@msn.com

Feb 14 '07 #3

P: n/a
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
Feb 14 '07 #4

P: n/a
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


Feb 14 '07 #5

P: n/a
"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
Feb 14 '07 #6

P: n/a
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
Feb 14 '07 #7

P: n/a
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.

Feb 15 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.