Microsoft SQL Server forum - Upsizing questions

Author

Upsizing questions

John

2005-07-31, 3:23 am

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?

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?

3. Is it possible for access frontend to connect/link to two separate SQL
Server dbs, main and archive at the same time?

Thanks

Regards

Erland Sommarskog

2005-07-31, 3:23 am

John (John@nospam.infovis.co.uk) writes:
> 1. Should I keep using the mdb as frontend or switch to an adp (access
> project) instead? Any reasons?

You ask in a newsgroup like comp.databases.ms-access about this one.

> 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?

CASE WHEN <this condition> THEN <that value>
WHEN <this other condition> THEN <that other value>
..
ELSE <when nothing else fits>
END

But beware that this is far from the only difference. Access appears
to have quite a few pecularities (as SQL Server has its). One that
often bites Access programmers is:

SELECT a + b + c AS d, d + e AS f FROM tbl

That is, once you have defined a name for a column, you can use it
later in the query.

This does not conform with ANSI SQL, and neither does not work with SQL
Server. Instead you use derived tables.

SELECT d, d + e
FROM (SELECT a + b + c AS d FROM tbl) AS tbl2

> 3. Is it possible for access frontend to connect/link to two separate SQL
> Server dbs, main and archive at the same time?

That's another question that you should ask in a place where they
know Access.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

serge

2005-07-31, 8:24 pm

> 1. Should I keep using the mdb as frontend or switch to an adp (access
> project) instead? Any reasons?

I have not been developing in Access for about 2 years now. In 2002 I
converted an Access mdb to ADP using Office 2000 then Office XP
with SQL Server 7.

Back then and now I am pretty sure nothing major changed (I might be wrong)
as most of the experienced Access developers would not bother switching
from MDB to ADP. They did not see any advantages of doing so.
Now I would not argue with that point as back then I did a lot of research
on the same question but I decided to switch to ADP. The reasons I did
so is because since I was switching to SQL Server as the backend, I
preferred to have my Access database directly connected to a SQL
Server backend instead of relying on ODBC and Linked tables.
I had to learn ADO too to make the data communication as I was
using unbound forms everywhere (which basically some people would
say it would defeat the purpose of using Access altogether and would
recommend using VB instead).

I would suggest you stay with mdb for the time being and not waste
your time with ADP. If you find the time to redo your frontend then
I recommend you use VB .NET instead of Access.

I also suggest you post the same questions here on
comp.databases.ms-access and see what the latest story is on
ADP/MDB and VB.NET.
I haven't followed the Access community for a couple of years now
so I am not sure where Microsoft Access is heading towards in the
future.

> 3. Is it possible for access frontend to connect/link to two separate SQL
> Server dbs, main and archive at the same time?

Sure, if you use VB Code you can open as many connections as you want
to other SQL Server databases. You don't need to think you are limited
by the ODBC Linked Tables connection to one SQL Server database.
Nothing prevents you from creating a new connection using code, accessing
the data and then closing the connection.

Good Luck with your research and decision.

VIPS

2005-08-01, 7:25 am

Response from VIPS:

1)If you are familiar with Access front, then use Access front rather
than ADP.
But using adp will give you close compatibility with SQL server, and
allows you the option of using triggers or RDI, views etc..

2)You cannot use IIF. But you can create a stored proc and use the IF
statement to achieve same result. If you want to use IIF, then select
MDF rather than ADP when upsizing.

3)It is absolutely possible to link to multiple SQL Servers.

John wrote:
>
>
> 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?
>
> 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?
>
> 3. Is it possible for access frontend to connect/link to two separate SQL
> Server dbs, main and archive at the same time?
>
> Thanks
>
> Regards

Sponsored Links