upsizing A2K to SqlServer

I am planning to convert an existing Access database which has a back
end
(data tables and relationships only) on a server and a copy of the front
end
(form, queries, reports) on each of about a dozen workstations.

I intend to convert the back end to SqlServer and wish to use the
upsizing
wizard. I am acquiring SSW Upsizing Pro! 2000 which seems to be
recommended in other discussions.

I have some questions in mind:
1 how will I change the SqlServer tables e.g. add/delete a field
to/from a
table, or add/delete a table?
2 how are relationships handled in SqlServer? (I need cascading
deletes)

Do I need to obtain any other tools, such as SqlServer itself to be able
to do
things like this?

At this stage I want the simplest possible conversion. The database
works
perfectly well at present but intermittent network problems mean that it
would be better to have the back end as SqlServer. I can delay
implementing
other benefits until a later stage.

Advice as to which good books to read would be useful. I have developed
a
number of Access databases and am competent in writing VB (and other
languages) but have minimal knowledge of SqlServer.

Ron Devenish

*** Sent via Developersdex http://www.developersdex.com ***

re: upsizing A2K to SqlServer

Devonish wrote:[color=blue]
> I am planning to convert an existing Access database which has a back
> end
> (data tables and relationships only) on a server and a copy of the front
> end
> (form, queries, reports) on each of about a dozen workstations.
>
> I intend to convert the back end to SqlServer and wish to use the
> upsizing
> wizard. I am acquiring SSW Upsizing Pro! 2000 which seems to be
> recommended in other discussions.
>
> I have some questions in mind:
> 1 how will I change the SqlServer tables e.g. add/delete a field
> to/from a
> table, or add/delete a table?[/color]

You can either use SQL Server's Data Definition Language (for example,
CREATE TABLE, DROP TABLE, and ALTER TABLE) and run these in Query
Analyzer, or you can amend tables graphically in Enterprise Manager
(SQL Server's front end). SQL Server comes with an extremely good help
system called Books OnLine (commonly abbreviated to BOL).
[color=blue]
> 2 how are relationships handled in SqlServer? (I need cascading
> deletes)[/color]

Again, use DDL, or build the relationships graphically in the Data
Diagram window, which is very similar to Access. You can amend
cascading Deletes as they are implemented in SQL Server as Triggers. If
you have these constraints set up on your Access database, the wizard
can bring them across for you.[color=blue]
>
> Do I need to obtain any other tools, such as SqlServer itself to be able
> to do
> things like this?[/color]

Yes. How do you suppose you're going to manage this otherwise?
[color=blue]
> At this stage I want the simplest possible conversion. The database
> works
> perfectly well at present but intermittent network problems mean that it
> would be better to have the back end as SqlServer. I can delay
> implementing
> other benefits until a later stage.
>
> Advice as to which good books to read would be useful. I have developed
> a
> number of Access databases and am competent in writing VB (and other
> languages) but have minimal knowledge of SqlServer.[/color]

You will probably find that there's more work amending the front end to
connect to SQL Server than there is in the upsizing, which is normally
pretty straightforward. However, it would be a good idea to script the
database once it's been created and do a desk check to see what's been
done.

Good luck

Edward

re: upsizing A2K to SqlServer

Devonish wrote:[color=blue]
> I am planning to convert an existing Access database which has a back
> end
> (data tables and relationships only) on a server and a copy of the front
> end
> (form, queries, reports) on each of about a dozen workstations.
>
> I intend to convert the back end to SqlServer and wish to use the
> upsizing
> wizard. I am acquiring SSW Upsizing Pro! 2000 which seems to be
> recommended in other discussions.
>
> I have some questions in mind:
> 1 how will I change the SqlServer tables e.g. add/delete a field
> to/from a
> table, or add/delete a table?[/color]

As Teddy said, the DDL or use Enterprise Manager, this works similar to
modifying Access/Jet tables.
[color=blue]
> 2 how are relationships handled in SqlServer? (I need cascading
> deletes)[/color]

DRI (Declarative Referential Integrity), cascade deletes and updates are
supported.
[color=blue]
> Do I need to obtain any other tools, such as SqlServer itself to be able
> to do
> things like this?[/color]

Might help :-) You could try with just MSDE but without the front end
tools you're gonna have to learn a lot of SQL.
[color=blue]
> At this stage I want the simplest possible conversion. The database
> works
> perfectly well at present but intermittent network problems mean that it
> would be better to have the back end as SqlServer. I can delay
> implementing
> other benefits until a later stage.[/color]

These intermittant problems, what outcome do they produce in the pure
Access app? Corrupt back-end? If so then you'll find SQL Server very
robust in that dept. If just the annoyance of being disconnected from
your tables then I'm afraid SQL Server will not help here. You'd still
get disconnected from that and Access is hopeless at recovering from
network glitches, whether it be Jet or SQL back end.
[color=blue]
> Advice as to which good books to read would be useful. I have developed
> a
> number of Access databases and am competent in writing VB (and other
> languages) but have minimal knowledge of SqlServer.[/color]

Depending on your level you can go from anything like "SQL for Dummies"

(http://www.amazon.com/exec/obidos/tg...29735?v=glance)
to Joe Celko's "SQL For Smarties"
(http://www.amazon.com/exec/obidos/tg...2045?v=glance),
which is recommended reading.
or there's a few by Rebecca Riordan
(http://search.microsoft.com/search/r...ebecca+riordan)
I have one of her books, highly recommended even if we did used to argue
tooth and nail here :-)

For starters, Books Online that comes with SQL Server is essential
reading :-)

--
[OO=00=OO]

re: upsizing A2K to SqlServer

Thank you Edward and Trevor for helpful advice.

Ron Devenish

*** Sent via Developersdex http://www.developersdex.com ***