Migrating from Access

Hi

I have been tasked with migrating data from a large MS-Access DB to a
client server database. I have SQL Server and MySQL as options. more
importantly the client wishes to still use their old Access forms
front end for a while after the data migration.

Can anybody comment on whether this import is easier into MySQL or SQL
Server. I have read some of the MS docs on this so called "Upsizing"
and have been made aware of some of the many pitfalls. Just wondering
whether MySQL has anything to speed up the import. Has anybody got an
estimate of the time needed for such a "basic migration".

Thanks,
Chad

> I have been tasked with migrating data from a large MS-Access DB to a
> client server database. I have SQL Server and MySQL as options. more
> importantly the client wishes to still use their old Access forms
> front end for a while after the data migration.

MS Access is a filesystem database. It is fundamentally different from a
server database. Although access can mimic a server by sending SQL to
itself and then parsing it again, this is off course not very nice in
performing.
If the forms are built with linked tables, the performance is probably
not an issue anyway.

> Can anybody comment on whether this import is easier into MySQL or SQL
> Server. I have read some of the MS docs on this so called "Upsizing"
> and have been made aware of some of the many pitfalls. Just wondering
> whether MySQL has anything to speed up the import. Has anybody got an
> estimate of the time needed for such a "basic migration".

I would not call switching from your own car to using public transport
"upsizing", even though buses are bigger. Using MySQL or SQL Server is a
matter of taste and experience (and money).

For the migration, you can download MyODBC (for MySQL) and link the
MySQL tables in Access. This is probably not the fastest way, but it is
easy and gives you all the control you need, without having to switch to
using new programs. And if you haven't heard of "pass-through" queries
yet, read the MS Access helpfile on them.

I can't estimate the time it takes you to set up a server and to learn
to work with it. After that, the estimate is even more difficult: do the
access forms still perform sufficiently? Should that be part the migration?

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

>
> For the migration, you can download MyODBC (for MySQL) and link the
> MySQL tables in Access. This is probably not the fastest way, but it is
> easy and gives you all the control you need, without having to switch to
> using new programs. And if you haven't heard of "pass-through" queries
> yet, read the MS Access helpfile on them.
>
> I can't estimate the time it takes you to set up a server and to learn
> to work with it. After that, the estimate is even more difficult: do the
> access forms still perform sufficiently? Should that be part the migration?

The forms for sure need major improvement.
Can anybody recommend a suitable open source replacement technology
for the forms that will work with MySQL?

Willem Bogaerts wrote:
>> I have been tasked with migrating data from a large MS-Access DB to a
>> client server database. I have SQL Server and MySQL as options. more
>> importantly the client wishes to still use their old Access forms
>> front end for a while after the data migration.
>
> MS Access is a filesystem database. It is fundamentally different from a
> server database. Although access can mimic a server by sending SQL to
> itself and then parsing it again, this is off course not very nice in
> performing.
> If the forms are built with linked tables, the performance is probably
> not an issue anyway.
>

Actually, to be correct, Access is an access mechanism to databases.
For instance, it also works with MySQL as an ODBC database. What you
are describing is the MS Jet Engine.

But I've never tried using Access's linked tables with MySQL. It might
work, however.

>> Can anybody comment on whether this import is easier into MySQL or SQL
>> Server. I have read some of the MS docs on this so called "Upsizing"
>> and have been made aware of some of the many pitfalls. Just wondering
>> whether MySQL has anything to speed up the import. Has anybody got an
>> estimate of the time needed for such a "basic migration".
>
> I would not call switching from your own car to using public transport
> "upsizing", even though buses are bigger. Using MySQL or SQL Server is a
> matter of taste and experience (and money).
>

More than that. They both have their own advantages and disadvantages.
You need to look at the features you need and see which database
satisfies them better. If both will do what you need, then you can look
at cost, etc.

> For the migration, you can download MyODBC (for MySQL) and link the
> MySQL tables in Access. This is probably not the fastest way, but it is
> easy and gives you all the control you need, without having to switch to
> using new programs. And if you haven't heard of "pass-through" queries
> yet, read the MS Access helpfile on them.
>

Or use the MySQL Migration Toolkit, which was created just for
requirements like this.

> I can't estimate the time it takes you to set up a server and to learn
> to work with it. After that, the estimate is even more difficult: do the
> access forms still perform sufficiently? Should that be part the migration?
>

Agreed. It depends on things from the database itself to the experience
of the person doing the work.

You might want to think of getting someone who has conversion
experienced involved in this project. It's not all that difficult, but
there are lots of ways to go wrong, also.

> Good luck,

Agreed.

And I would also add - what about the user's programs? How are those
going to be converted?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================