Conversion of Access application to SQL Server

[Clive Minnican]

Hi there,

I have written an application which uses MS Access for it's database engine.
Due to the large size which the database has become I have decided that it
would be sensible to use SQL Server with the application instead.

I am an extreme SQL Server newbie so I am not really sure what I'm doing
yet! I have successfully downloaded and installed the MS SQLDE 2000 and
service pack 3.

What do I need to do next? Ideally I would like to convert the existing
Access database to MS SQL Server format. Also I would like to know if it is
possible to create an SQL Server database from scratch using a gui
environment similar to Access and if so which software (preferably free) do
I need to achieve this?

Many thanks,
Clive.

[Ryan]

Re: Conversion of Access application to SQL Server

The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan


"Clive Minnican" <clive@mail.com > wrote in message news:<BeLUc.160 3$CT4.510@newsf e3-gui.ntli.net>.. .[color=blue]
> Hi there,
>
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
>
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
>
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
>
> Many thanks,
> Clive.[/color]

[Ryan]

Re: Conversion of Access application to SQL Server

The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan


"Clive Minnican" <clive@mail.com > wrote in message news:<BeLUc.160 3$CT4.510@newsf e3-gui.ntli.net>.. .[color=blue]
> Hi there,
>
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
>
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
>
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
>
> Many thanks,
> Clive.[/color]

[Simon Hayes]

Re: Conversion of Access application to SQL Server

"Clive Minnican" <clive@mail.com > wrote in message news:<BeLUc.160 3$CT4.510@newsf e3-gui.ntli.net>.. .[color=blue]
> Hi there,
>
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
>
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
>
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
>
> Many thanks,
> Clive.[/color]

I believe that Access has an upsizing wizard which attempts to
automatically upgrade Access applications to MSSQL, although like all
platform migration tools it probably has a number of limitations. In
any case, you may get a better response to this in an Access newgroup.

http://www.aspfaq.com/show.asp?id=2182

As for GUIs for MSDE, see here:

http://www.aspfaq.com/show.asp?id=2442

Simon