Using SQL Server as the backend

[AP]

Hello
I have a department full of Access databases. I am starting to think
about moving some of the larger ones to use a sql server backend. I do
not want to go throught the technical upsizing. I would just like to
have the SQL backend. Is there anything wrong with moving the tables to
SQL and simply linking to them in Access?
Thanks

[Rick Brandt]

Re: Using SQL Server as the backend

AP wrote:[color=blue]
> Hello
> I have a department full of Access databases. I am starting to think
> about moving some of the larger ones to use a sql server backend. I do
> not want to go throught the technical upsizing. I would just like to
> have the SQL backend. Is there anything wrong with moving the tables
> to SQL and simply linking to them in Access?
> Thanks[/color]

In fact creating the tables yourself and then moving the data with queries
or DTS is a much better idea than using the upsizing wizard because you
fully understand and control what is going on. This is an area where I
think a wizard is a terrible choice. Many of the assumptions that the
wizard will make for you will be wrong and you won't be aware of the things
that it doesn't do for you until you have problems.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[AP]

Re: Using SQL Server as the backend

Thanks, are there any performance issues with simply bringing the SQL
tables in as linked table rather than using views and pass through
queries?


Rick Brandt wrote:[color=blue]
> AP wrote:[color=green]
> > Hello
> > I have a department full of Access databases. I am starting to think
> > about moving some of the larger ones to use a sql server backend. I do
> > not want to go throught the technical upsizing. I would just like to
> > have the SQL backend. Is there anything wrong with moving the tables
> > to SQL and simply linking to them in Access?
> > Thanks[/color]
>
> In fact creating the tables yourself and then moving the data with queries
> or DTS is a much better idea than using the upsizing wizard because you
> fully understand and control what is going on. This is an area where I
> think a wizard is a terrible choice. Many of the assumptions that the
> wizard will make for you will be wrong and you won't be aware of the things
> that it doesn't do for you until you have problems.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com[/color]

[Rick Brandt]

Re: Using SQL Server as the backend

AP wrote:[color=blue]
> Thanks, are there any performance issues with simply bringing the SQL
> tables in as linked table rather than using views and pass through
> queries?[/color]

Sometimes. I always start with the linked ODBC tables and the same Access
queries I would have had if I were using Access/Jet tables and then I
evaluate them. Those that perform poorly will need to be redesigned.
Whether that redesign involves just changing the Access query or moving to
passthroughs and/or stored procedures will vary from one query to the next.

In general if you have queries that join linked tables then a view on the
server that does the join for you will perform better that doing the join
locally, but that is not a hard rule. Also to consider is that local joins
might still produce an editable result set whereas a joined view will not
unless you use InsteadOf triggers.

You will likely be surprised at just how few of your queries will need to be
converted. Jet/ODBC does a pretty good job of passing the work to the
server even when you use plain old queries against the links. What is
important is getting the server to do the SELECT work. By that I mean if
the server is deciding which rows to send back and Access has to perform
additional processing on those rows then that is not a problem. What you
don't want is for the server to send you ALL the rows so that Access/Jet can
figure out which ones it actually needs.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com