MsSql, MySql, Migrating from Access.

3 May 2007 10:15 AM

tc

I have an app that I'm re-writing for MySql and MsSql, the old version
connected to an Access database. It is more than likely that existing
customers will want to upgrade to the later version. I don't want to rely
on 3rd party apps for Migration, so.... I'm more than happy to write the
connection code and transfer the data myself, but I don't know how to ensure
that all Auto Number fields in the Access database transfer correctly, as in
SQL a counter field cannot be written. The Access database will probably
not start at 1 for all Auto Number fields, there may also be missing numbers
in the Auto Number fields as records will have been deleted. Can anyone
help?

3 May 2007 11:38 AM

Kerry Moorman

tc,

Can you migrate all the data and then make certain columns in the target
databases identity, etc? That works in Access, but I'm not sure about your
target databases.

Also note that the Upsizing wizard in Access seems to do this when moving a
database from Access to SQL Server.

Kerry Moorman

Show quoteHide quote

"tc" wrote:

> I have an app that I'm re-writing for MySql and MsSql, the old version
> connected to an Access database. It is more than likely that existing
> customers will want to upgrade to the later version. I don't want to rely
> on 3rd party apps for Migration, so.... I'm more than happy to write the
> connection code and transfer the data myself, but I don't know how to ensure
> that all Auto Number fields in the Access database transfer correctly, as in
> SQL a counter field cannot be written. The Access database will probably
> not start at 1 for all Auto Number fields, there may also be missing numbers
> in the Auto Number fields as records will have been deleted. Can anyone
> help?
>
>
>

3 May 2007 1:51 PM

Patrice

For MS SQL see the doc for the SET IDENTITY_INSERT option. It allows to
enable explicit writting of the identity (counter) field for a particular
table.

Don't know for MySQL but it could have a similar option...
---
Patrice

"tc" <t***@idcodeware.co.uk> a écrit dans le message de news:

es8LKwWjHHA.1***@TK2MSFTNGP05.phx.gbl...
Show quoteHide quote

>I have an app that I'm re-writing for MySql and MsSql, the old version
>connected to an Access database. It is more than likely that existing
>customers will want to upgrade to the later version. I don't want to rely
>on 3rd party apps for Migration, so.... I'm more than happy to write the
>connection code and transfer the data myself, but I don't know how to
>ensure that all Auto Number fields in the Access database transfer
>correctly, as in SQL a counter field cannot be written. The Access
>database will probably not start at 1 for all Auto Number fields, there may
>also be missing numbers in the Auto Number fields as records will have been
>deleted. Can anyone help?
>