John wrote:
Hi
I have a fairly complex access frontend/backend app which I need to upsize
to SQL Server. At this stage I would prefer to use access as frontend. I
have the following questions;
1. Should I keep using the mdb as frontend or switch to an adp (access
project) instead? Any reasons?
MDB, apart from the problems reported here about ADPs, there'll be less
code changes required + the ability to utilise local tables and keep
some queries as Access queries if they run well enough without having to
convert to views (see 2), also see 3.
2. I have used IIF (immediate if) frequently in access queries. When
converting queries to SQL Server, is there an easy way to replace the IIF
function?
No, there are other functions and constructs that you can use in place
such as case constructs, e.g.
Select field1, case field2 when 0 then 'foo' else 'bar' from sometable.
Some queries may run well against the linked tables without resorting to
converting them to views. IME anything with 2 or more joins results in
lots of prepared sp calls and can be inefficient.
3. Is it possible for access frontend to connect/link to two separate SQL
Server dbs, main and archive at the same time?
Since it's just linked tables (in MDB), they can come from anywhere,
different databases, different servers, even different database engines.
I don't think you can do that with an ADP, it (AFAICS) only attaches to
one database ata time although you can write views in that database that
select data from another database so the same could be achieved that
way, e.g.
select * from databasename.ownername.tablename
or another server if set up (see BOL: sp_addlinkedserver)
select * from servername.databasename.ownername.tablename
--
[OO=00=OO]
