converting access db to mysql

[chicha]

Hey people,

I have to convert MS Access 2000 database into mysql database, the whole
thing being part of this project I'm doing for one of my faculty
classes. My professor somehow presumed I knew db's and gave me long list
of things to do with that particular database, first thing being that
particular conversion. Truth is that I don't know a first thing about
db's, let alone using mysql... I downloaded mysql form www.mysql.com and
still searching for MS Access 2000 (it doesn't work with 2003 I have,
or I don't know how to make it work).

Any kind of help will be welcomed and highly appreciated!!!

Thanks,

Mario

[Christopher Browne]

Re: converting access db to mysql

Centuries ago, Nostradamus foresaw when chicha <mario@blindsig ht.org> would write:[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the
> whole thing being part of this project I'm doing for one of my
> faculty classes. My professor somehow presumed I knew db's and gave
> me long list of things to do with that particular database, first
> thing being that particular conversion. Truth is that I don't know a
> first thing about db's, let alone using mysql... I downloaded mysql
> form www.mysql.com and still searching for MS Access 2000 (it
> doesn't work with 2003 I have, or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!![/color]

Hmm.

It sounds as though you're interested in sorts of assistance that
would be considered to be academic offenses.

Thanks for the offer to "aid and abet," but no thanks.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;

http://www.ntlug.org/~cbbrowne/multiplexor.html

Rules of the Evil Overlord #40. "I will be neither chivalrous nor
sporting. If I have an unstoppable superweapon, I will use it as early
and as often as possible instead of keeping it in reserve."
<http://www.eviloverlor d.com/>

[Laconic2]

Re: converting access db to mysql


"Christophe r Browne" <cbbrowne@acm.o rg> wrote in message
news:2pu2isFone hqU1@uni-berlin.de...
[color=blue]
> It sounds as though you're interested in sorts of assistance that
> would be considered to be academic offenses.[/color]

This one doesn't sound like "do my homework for me" to me.

It's more like "moving from one db to the other is a preliminary step before
the project can really begin".

Of course, in any project like this, it's always the first step that's the
giant killer.

['69 Camaro]

Re: converting access db to mysql

Hi, Mario.
[color=blue]
> My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql...[/color]

You have set yourself up for failure. The first thing you should do is go
back to your professor and explain that you got carried away by the
excitement of the project assignment, but that you now realize that this
database conversion is way over your head. How can one tell this is way
over your head? Reread the following statement that you made:
[color=blue]
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).[/color]

Access 2000 is the default database format for Access 2000, 2002, and 2003.
This is going to sound harsh, but if you can't figure out "how to make it
work," then either you aren't using any of these versions of Access or don't
know how to open an Access database file with Access. Might you be using
Access 97 or another application in the Office 2003 suite in your attempts
to open the Access 2000 database?

Converting a database from Access to MySQL isn't just selecting a menu item
to launch a Wizard and assigning a path and file name to the new MySQL
database through the Wizard GUI. You can probably export each of the tables
from the Access database to *.CSV files that can be imported into MySQL, but
you've got to do the relational database design and likely the table
creation first in MySQL. None of the queries, forms, reports, macros,
modules or data access pages can be exported from Access into a format that
MySQL can read and convert into a MySQL format for the new database.

The scope of this project is so vast that the type of help you need is for
someone to do the entire project for you. However, since this is a class
project, I doubt you'll get much assistance from the News Groups.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


"chicha" <mario@blindsig ht.org> wrote in message
news:chcfuv$n00 $1@bagan.srce.h r...[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!!
>
> Thanks,
>
> Mario[/color]

[Bernard Peek]

Re: converting access db to mysql

In message <chcfuv$n00$1@b agan.srce.hr>, chicha <mario@blindsig ht.org>
writes[color=blue]
>Hey people,
>
>I have to convert MS Access 2000 database into mysql database, the whole
>thing being part of this project I'm doing for one of my faculty
>classes. My professor somehow presumed I knew db's and gave me long list
>of things to do with that particular database, first thing being that
>particular conversion.[/color]

You can't just convert an Access database to MySQL because there are
lots of things that Access can do that MySQL can't. You may be able to
copy the data from an Access database to a MySQL Server but that's a
trivially small part of a complete conversion.
[color=blue]
> Truth is that I don't know a first thing about
>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>or I don't know how to make it work).[/color]

Access 2003 should be able to open an Access 2000 database file. Check
the file extension for the database file you have, do you have a file
with a .MDB extension or is it something else?
[color=blue]
>
>Any kind of help will be welcomed and highly appreciated!!![/color]

Run away screaming. It's the most helpful thing I can suggest.



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[rkc]

Re: converting access db to mysql


"chicha" <mario@blindsig ht.org> wrote in message
news:chcfuv$n00 $1@bagan.srce.h r...[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!![/color]

If your a quick study, Google can be your best friend.

If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
Someone from Pakistan will do your job for you for that price with glee.

If neither of those works for you, you're probably F%#*ked.

[R Camarda]

Re: converting access db to mysql

You might want to consider exporting the data from Access to a flat
text file. then import the flat text file into mysql. This way, you
can avoid having to find a way to convert directly from one to the
other.

I know mysql can import flat files, as a co-worked did it to load
legacy data from our operational system into a mysql db for a web
page.
hth
Rob


chicha <mario@blindsig ht.org> wrote in message news:<chcfuv$n0 0$1@bagan.srce. hr>...[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!!
>
> Thanks,
>
> Mario[/color]

[chicha]

Re: converting access db to mysql

Thanks guys for your replies!

I see now I phrased my post in a completely wrong way.

First of all, what I'm trying to do here is to get some guidance and
friendly advice to get me started. I HAVE to do this project, I was told
to. For any of you who read somewhere between my lines "please could
somebody do this for free instead of me??", I just must stress that that
was NOT my idea for this post. There are easier and more reliable ways
to cheat at faculty then using newsgroups...

I'm new with db's but not with PC's. What I should have written is that
I got the database made in Access 2000 (.MDB) which doesn't work with
Access 2003. I know this is wrong, and that it should work, but it
doesn't. I spoke with the author/professor assistant who obviously did
it using "Databases for dummies" book and he said he's sure it works but
has bugs(!!!). He doesn't know what bugs though :). That's why he gave
me the whole thing to put it in mysql. When I tried it in 2003, sure as
hell, it didn't work, bug or no bug. It opened and loaded, but didn't
read relations nor data. It works in A2000 because the whole "office of
construction in mech. eng." at my faculty uses it. I'm not gonna tell
that guy he did it all wrong, because he knows it. He knows I know it
too. Saying it loud would mean my academical suicide. I'll rather use
Access 2000 and fix it there.

I mentioned it in the post because I thought someone could offer an
advice. My apologies that I haven't explained the situation more
thoroughly, I thought it was uneccessary. It would save me from you guys
thinking I'm complete idiot or lousy cheat.

Thanks to all of you guys who provided insightfull info and links, I
appreciate it. I'll check it out, and see what can be done.

Cheers,

Mario



rkc wrote:
[color=blue]
> "chicha" <mario@blindsig ht.org> wrote in message
> news:chcfuv$n00 $1@bagan.srce.h r...
>[color=green]
>>Hey people,
>>
>>I have to convert MS Access 2000 database into mysql database, the whole
>>thing being part of this project I'm doing for one of my faculty
>>classes. My professor somehow presumed I knew db's and gave me long list
>>of things to do with that particular database, first thing being that
>>particular conversion. Truth is that I don't know a first thing about
>>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
>> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>>or I don't know how to make it work).
>>
>>Any kind of help will be welcomed and highly appreciated!!![/color]
>
>
> If your a quick study, Google can be your best friend.
>
> If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
> Someone from Pakistan will do your job for you for that price with glee.
>
> If neither of those works for you, you're probably F%#*ked.
>
>
>
>[/color]

[Bernard Peek]

Re: converting access db to mysql

In message <chd16b$jso$1@b agan.srce.hr>, chicha <mario@blindsig ht.org>
writes[color=blue]
>Thanks guys for your replies!
>
>I see now I phrased my post in a completely wrong way.
>
>First of all, what I'm trying to do here is to get some guidance and
>friendly advice to get me started. I HAVE to do this project, I was
>told to. For any of you who read somewhere between my lines "please
>could somebody do this for free instead of me??", I just must stress
>that that was NOT my idea for this post. There are easier and more
>reliable ways to cheat at faculty then using newsgroups...[/color]

OK. Here's a few things to think about.

First, Access is not just a database system. It includes a forms
designer and a report designer, neither of which exist in MySQL. In
addition to moving the data from Access to MySQL you will also have to
design data-entry forms and reports. You will need to choose additional
software to do that.

It is possible to use Access as a front-end with the data stored in a
MySQL database. If you do it right you won't need to redesign any of
your forms or reports to do that. This is probably what you should aim
at as the first step in migration.

MySQL is designed as a client-server system and is usually used with the
data stored on a central server and client programs running on users'
workstations. You can run both client and server software on the same
machine, but you don't have to. If your site has any other MySQL
databases in use then you may find that there are already MySQL experts
available.

A copy of Access 2003 should open an Access 2000 database file without
any problems. If you are having problems with the database file then
switching to Access 2000 probably won't fix the problem. Find a machine
where the system works. Open up the database file and look at the tables
in it. Look for attached tables that use data that is actually stored
elsewhere. That might be in another Access database file or perhaps even
in a MySQL database on a server elsewhere. If your database uses
attached tables then you have to bring a copy of the data source to your
new machine in addition to the database file itself. Check this and let
us know whether there are any attached tables.

[...]
[color=blue]
>I mentioned it in the post because I thought someone could offer an
>advice. My apologies that I haven't explained the situation more
>thoroughly, I thought it was uneccessary. It would save me from you
>guys thinking I'm complete idiot or lousy cheat.[/color]

It's pretty difficult to gather all of the information on a system if
expert users are standing right in front of you. Trying to do it through
a newsgroup is even more difficult.

Speaking of newsgroups, I'm not sure that comp.databases. theory is the
right newsgroup for this. You might want to drop that from the
crossposting list (unless anyone in that newsgroup wants it to
continue.)




--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Albert D. Kallal]

Re: converting access db to mysql

You can certainly move the data to mysql, and keep the ms-access front end.

A large portion, in fact most of the forms and reports will work. However,
do you need to be aware of some differences etc.

And, most code as is will also work.

The main areas to watch are:

You need to have both a primary key, and a timestamp in EACH table on
the mysql side.

You can continue to use the ms-access query builder. For reports,
queries that have multi-table joins should be changed to pass-through, and
that performances VERY well with mysql.

So, this project is do-able. However, you should have a good familiarly with
ms-access, and your initial problems with ms-access need to be solved first.

good luck!


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

http://www.attcanada.net/~kallal.msn

[Larry Linson]

Re: converting access db to mysql

"chicha" wrote
[color=blue]
> When I tried it in 2003, sure as hell, it didn't
> work, bug or no bug. It opened and loaded,
> but didn't read relations nor data. It works in
> A2000 because the whole "office of con-
> struction in mech. eng." at my faculty uses it.[/color]

That seems to indicate that the TableDefs in the database you are trying to
work with are simply links to tables in another "back-end" Access database.
You are assuming a cause when you haven't even identified what the problem
is.

If the "whole office of..." are all working with the same tables, that is
almost certainly the way it would have been set up. Almost certainly, it has
not a thing to do with whether you are using Access 2000, 2002, or 2003 to
work with it.

If the computer where you are trying Access 2003 is not connected on the
same LAN as the "whole office of..." then you are unlikely to be able to fix
that, unless you find and copy the shared tables to the machine/environment
where you are working. If you do that, or if your Access 2003 computer is on
the same LAN, then you may be able to fix the problem as simply as finding
the shared tables and using the Linked Table Manager (on the menu, Tools |
Database Utilities | Linked Table Manager) to relink the TableDefs to the
proper backend database.

Did I sufficiently emphasize the key here is "FIND THE SHARED TABLES AND
LINK THEM"?

I strongly urge you do that before you waste any more of your time and
effort on worrying about Access 2000. If, however, you try to do it in
Access 2000, on a machine that is in the same LAN as the "whole office of
...." and it _does_ work, you cannot assume that it worked just because you
used Access 2000 (see above).

As many have told you, you can convert the data -- BUT you first have to
HAVE the data, which you do not seem to have. Other than just hipshooting a
possible (probably not a workable) solution, I don't see any good reason to
convert even the data to MySQL.

Should I emphasize again, the key is "FIND THE SHARED TABLES"?

But certainly, you can convert nothing BUT the data to MySQL, because it is
a server database and has no support for any of the other features (to wit,
the user interface) of Access.

Larry Linson
Microsoft Access MVP

[Dietrich Schaffer]

Re: converting access db to mysql

chicha <mario@blindsig ht.org> wrote:[color=blue]
> Hey people,[/color]

[...]
[color=blue]
> Any kind of help will be welcomed and highly appreciated!!![/color]

Convert tables -> .*csv -> mysql

I don't know MS Access 2000...
[color=blue]
> Thanks,
> Mario[/color]

Servus,
Dietrich

[Matthias Gutmann]

Re: converting access db to mysql

chicha schrieb:[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!!
>
> Thanks,
>
> Mario[/color]

Hello,

http://www.dbtools.com.br/EN/dbmanager.php

Gruß Matthias

--

https://www.source-code.de

- The Developers Source -

[Larry Linson]

Re: converting access db to mysql

"Matthias Gutmann" wrote
[color=blue]
> http://www.dbtools.com.br/EN/dbmanager.php[/color]

Matthias, I'll bet your tool won't work unless he finds and uses the actual
data tables. Linked TableDefs just are not sufficient to retrieve the data
unless the Links are valid, and there is a strong probability that is
chicha's problem. Once he has them, that appears to be a very useful set of
features.

Larry Linson
Microsoft Access MVP

[Laconic2]

Re: converting access db to mysql


"chicha" <mario@blindsig ht.org> wrote in message
news:chcfuv$n00 $1@bagan.srce.h r...[color=blue]
> Hey people,
>
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
>
> Any kind of help will be welcomed and highly appreciated!!!
>
> Thanks,
>
> Mario[/color]

OK, now that we have a clearer picture of what you're up against maybe we
can break it down into steps:

1. Open the DB up with MS Access.
2. Unload the data into portable format files.
3. Extract the data definitions (at least tables and indexes) into some
form for migration.
4. Create an empty target DB.
5. Define tables, indexes, and whatever necessary infrastructure to contain
the same data.
6. Load the data from the protable format files.

These tasks are not equally easy. You can probably do some of them by the
seat of your pants, just using the general PC skills and savvy you've picked
up elsewhere. For some of them, you probably need help.

As far as step 1 goes. I'd really advise you to get someone with the right
version of MS Access to open the DB. The last thing you need to be doing at
this stage is messing around with version skew or incompatible software.
What about the professor? Can he open the DB with his version of Access?

We'll help you with the remaining steps once you can open the DB.