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

[Tom van Stiphout]

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

[Merle Nicholson]

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

[Merle Nicholson]

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