472,327 Members | 1,252 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Access MySql ODBC strategy

Bob
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 subjects enrolled in studies related to respiratory
diseases. Both lab data and questionnaire information are collected.
We use W2K as PC operating systems, MS Access XP, XP Developer, and
Visual SourceSafe. Our databases use a front end back end strategy.
Users (about 15-20) double click a Perl script, which copies the
latest version of the front end to their local drives. The front ends
are link to the back end, which resides on the server.

A new project involves migrating data presently in SIR into MySql,
plus some tables that are currently in the Access back end. I have
downloaded and installed the MySql ODBC driver.

Questions:
1. How should we deploy and use ODBC?
2. Does each user PC have to have the driver installed?
3. In Administrative Tools data sources (ODBC) which do we
create: User DSN, System DSN, or File DSN?
4. In #3, does this need to be done on each user PC?
5. Reference reading on the subject?

Thanks for looking.

Bob
University of Arizona
Respiratory Sciences Center
Nov 13 '05 #1
3 2296
On 28 Jun 2004 12:23:00 -0700, or**@resp-sci.arizona.edu (Bob) wrote:

Deep breath :-)
1: As with any ODBC driver, use the ODBC applet in Control Panel /
Administrative Tools.
2: Yes.
3: Read up on what they mean, then choose. My guess is that you will
choose System DSN.
4: Yes. Although there are ways to create a DSN programmatically. A
somewhat advanced topic.
5: Start with Google.

-Tom.
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 subjects enrolled in studies related to respiratory
diseases. Both lab data and questionnaire information are collected.
We use W2K as PC operating systems, MS Access XP, XP Developer, and
Visual SourceSafe. Our databases use a front end back end strategy.
Users (about 15-20) double click a Perl script, which copies the
latest version of the front end to their local drives. The front ends
are link to the back end, which resides on the server.

A new project involves migrating data presently in SIR into MySql,
plus some tables that are currently in the Access back end. I have
downloaded and installed the MySql ODBC driver.

Questions:
1. How should we deploy and use ODBC?
2. Does each user PC have to have the driver installed?
3. In Administrative Tools data sources (ODBC) which do we
create: User DSN, System DSN, or File DSN?
4. In #3, does this need to be done on each user PC?
5. Reference reading on the subject?

Thanks for looking.

Bob
University of Arizona
Respiratory Sciences Center


Nov 13 '05 #2
You must install the ODBC driver on each machine, like all ODBC drivers.
You do not have to use the Administrative tools, you have two other choices
that I'm aware of:
1) Use a FileDSN (which I'm 90% sure you have to use anyway) and since File
DSN is a text file, just write it to each users directories. The file can
even be written from a startup form - a splash screen form.
2) You can create a linked table programatically - I'm doing it from a
splash screen that preceeds the main startup form. It's not hard if you have
an example. The Access VBA is a help, but it still took me a half day to
make it 100% reliable. The advantage is that there is nothing external that
exposes the user name and password, if you want to imbed that, and
deployment to the users is much easier. Just have the user install the MySQL
ODBC driver, and the Access app will work with no further setup. And no one
needs to know anything about the Linked Table Manager, either.

The way to start with MySQL is at their website. There's a good ODBC
reference manual, and there's some info about Access there and in the main
manual. Both are available as a download, but I prefer to use the on-line
version because it has search capability.

If you need an example, I have a test .mdb I can send you. Please reply
directly to my email.
"Bob" <or**@resp-sci.arizona.edu> wrote in message
news:f1**************************@posting.google.c om...
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 subjects enrolled in studies related to respiratory
diseases. Both lab data and questionnaire information are collected.
We use W2K as PC operating systems, MS Access XP, XP Developer, and
Visual SourceSafe. Our databases use a front end - back end strategy.
Users (about 15-20) double click a Perl script, which copies the
latest version of the front end to their local drives. The front ends
are link to the back end, which resides on the server.

A new project involves migrating data presently in SIR into MySql,
plus some tables that are currently in the Access back end. I have
downloaded and installed the MySql ODBC driver.

Questions:
1. How should we deploy and use ODBC?
2. Does each user PC have to have the driver installed?
3. In Administrative Tools - data sources (ODBC) - which do we
create: User DSN, System DSN, or File DSN?
4. In #3, does this need to be done on each user PC?
5. Reference reading on the subject?

Thanks for looking.

Bob
University of Arizona
Respiratory Sciences Center

Nov 13 '05 #3
You must install the ODBC driver on each machine, like all ODBC drivers.
You do not have to use the Administrative tools, you have two other choices
that I'm aware of:
1) Use a FileDSN (which I'm 90% sure you have to use anyway) and since File
DSN is a text file, just write it to each users directories. The file can
even be written from a startup form - a splash screen form.
2) You can create a linked table programatically - I'm doing it from a
splash screen that preceeds the main startup form. It's not hard if you have
an example. The Access VBA is a help, but it still took me a half day to
make it 100% reliable. The advantage is that there is nothing external that
exposes the user name and password, if you want to imbed that, and
deployment to the users is much easier. Just have the user install the MySQL
ODBC driver, and the Access app will work with no further setup. And no one
needs to know anything about the Linked Table Manager, either.

The way to start with MySQL is at their website. There's a good ODBC
reference manual, and there's some info about Access there and in the main
manual. Both are available as a download, but I prefer to use the on-line
version because it has search capability.

If you need an example, I have a test .mdb I can send you. Please reply
directly to my email.

me************@tampabay.rr.com
"Bob" <or**@resp-sci.arizona.edu> wrote in message
news:f1**************************@posting.google.c om...
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 subjects enrolled in studies related to respiratory
diseases. Both lab data and questionnaire information are collected.
We use W2K as PC operating systems, MS Access XP, XP Developer, and
Visual SourceSafe. Our databases use a front end - back end strategy.
Users (about 15-20) double click a Perl script, which copies the
latest version of the front end to their local drives. The front ends
are link to the back end, which resides on the server.

A new project involves migrating data presently in SIR into MySql,
plus some tables that are currently in the Access back end. I have
downloaded and installed the MySql ODBC driver.

Questions:
1. How should we deploy and use ODBC?
2. Does each user PC have to have the driver installed?
3. In Administrative Tools - data sources (ODBC) - which do we
create: User DSN, System DSN, or File DSN?
4. In #3, does this need to be done on each user PC?
5. Reference reading on the subject?

Thanks for looking.

Bob
University of Arizona
Respiratory Sciences Center

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a...
3
by: James Bird | last post by:
Hello In order to perform a hetrogeneous join (I think that's what they're called) between MySQL and another database, I've created an Access...
10
by: callmebill | last post by:
I'm getting my feet wet with making Python talk to MySQL via ODBC. I started on Windows, and it went smoothly enough due to the ODBC stuff that...
8
by: Dominic Martin | last post by:
Hi, I need to setup an ODBC link over our Internet connection between MS Access (local) and a MySQL server (Remote - the local PC has the MySQL...
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....
3
by: Stuart McGraw | last post by:
I am having a problem creating Access tables linked to a Mysql database. I get the widow to select a Data Source, click on the (system) DSN I...
2
by: SKB | last post by:
Hi, I am absolutely new to this area. I am getting the following difficulty : Access denied for user 'ODBC'@'localhost' (using password: NO) when...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly...
4
by: Vanessa | last post by:
Hi there I am an Access developer, and I have written applications for a 30 telephone call center, using the standard multiuser jet engine, it...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

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.