Upsizing questions

[John]

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

 

[John Bell]

Hi

As I mainly deal with SQL Server then I am not the most knowlegable
regarding access! As you have cross posted to several groups you may have
got better answers in those!

Upsizing from access is not always easy and straight forward. There is the
upsizing wizard, and you should at least give it a test to see how close it
gets!!!

You may want to read
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnapg/html/apg01.asp

See inline:

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?

If you are not using the access database then I believe the adp the best
solution.

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?

Most IIF statements can be replaced by the CASE statement see
http://msdn.microsoft.com/library/d...ry/en-us/tsqlref/ts_ca-co_5t9v.asp?frame=true

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

Possibly! Certainly if you use stored proceduers then you can use access the
alternate database using 3 part naming e.g

SELECT col1, col2 FROM otherdb.dbo.archivetable

John

 

[Steve Jorgensen]

....

If you are not using the access database then I believe the adp the best
solution.

Not to put too fine a point on it, but I think that suggestion is completely
crazy. If the project were being built from scratch, there might be some tiny
bit of justification for using an ADP, though I would still argue against it.

The number of things that cannot be made to work the same in ADPs as MDBs and
the number of new tricks and work-arounds that must be learned is vast - much
higher than the "mere" problem of learning good C/S design, and the Microsoft
SQL Server SQL variant.

Possibly! Certainly if you use stored proceduers then you can use access the
alternate database using 3 part naming e.g

SELECT col1, col2 FROM otherdb.dbo.archivetable

That's great, so long as the SQL Server you are connecting to has a path and a
driver to the other database, and you know what that path is relative to the
server. When you just need to do an ad-hoc query from a database on the
-local- machine, it's much more straightforward to do so from an Access MDB
using the From <table> in "<db-path>" syntax or create linked tables to the
back-end.

 

[John Bell]

On Sun, 31 Jul 2005 07:47:32 +0100, "John Bell"
<[email protected]>
wrote:

...

Not to put too fine a point on it, but I think that suggestion is
completely
crazy. If the project were being built from scratch, there might be some
tiny
bit of justification for using an ADP, though I would still argue against
it.

The number of things that cannot be made to work the same in ADPs as MDBs
and
the number of new tricks and work-arounds that must be learned is vast -
much
higher than the "mere" problem of learning good C/S design, and the
Microsoft
SQL Server SQL variant.

I am happy to accept that there may be something in an ADP that you can't do
that an MDB can, but to not upgrade because of you have to learn something
new is not one.

That's great, so long as the SQL Server you are connecting to has a path
and a
driver to the other database, and you know what that path is relative to
the
server. When you just need to do an ad-hoc query from a database on the
-local- machine, it's much more straightforward to do so from an Access
MDB
using the From <table> in "<db-path>" syntax or create linked tables to
the
back-end.

From a SQL Server point of view this does not make sense. Introducing a new
type of database and driver into the design was not raised by the OP, but
would not be an issue if there was already an OLE-DB or ODBC driver for it.

If the databases are on different servers then four part naming and linked
servers can be used. With an MDB and linked tables there would be no issue
regarding connections, but without knowing the current application code and
the way in which the two database are used, it is hard to say if that would
be a scalable solution. It is conceivable that the client application will
never need more than one connection and everything could be done in views
and stored procedures, but that is speculating what the client code does! An
upsize to SQL may involve re-engineering to use the features of the new
RDBMS fully, therefore your upgrade may take several revisions.

John

 

[Craig Alexander Morrison]

<<<I am happy to accept that there may be something in an ADP that you can't
do
that an MDB can, but to not upgrade because of you have to learn something
new is not one.>>>>

To learn something "new" that is being deprecated is folly.

ADP's were a good idea but they have failed to upgrade/fix them and I do not
believe there are any plans so to do.

Indeed to use SQL Server 2005 from Access you have to use SQLS2000
compatibility mode or so I last heard before dumping SQLS2005 for DB2.

 

[Steve Jorgensen]

I am happy to accept that there may be something in an ADP that you can't do
that an MDB can, but to not upgrade because of you have to learn something
new is not one.

Learning something new is wone thing. Having to completely re-engineer and
re-debug an app that was previously working in order to make it work with
something new is another thing altogether. Add to that the fact that most of
the new thinks you have to learn are kludges to force the ADP not to think for
you and get the wrong answer or to not do supposedly good things like use
TIMESTAMP columns because they don't work right, to remove table name prefixes
from ORDER BY clauses because break your subforms, ...

From a SQL Server point of view this does not make sense. Introducing a new
type of database and driver into the design was not raised by the OP, but
would not be an issue if there was already an OLE-DB or ODBC driver for it.

If the databases are on different servers then four part naming and linked
servers can be used. With an MDB and linked tables there would be no issue
regarding connections, but without knowing the current application code and
the way in which the two database are used, it is hard to say if that would
be a scalable solution. It is conceivable that the client application will
never need more than one connection and everything could be done in views
and stored procedures, but that is speculating what the client code does! An
upsize to SQL may involve re-engineering to use the features of the new
RDBMS fully, therefore your upgrade may take several revisions.

OK, so now if the user has to read data from a database file on their local
drive,insteaad of using a file browser to find the database, they must either
copy the database to a drive on the server, or make sure it's on a shared
drive, and link to it using a network path. Why should the user have to even
know where the server is, or how to deal with network file sharing?

 

[John Bell]

Hi

Learning that ADPs have been depriciated is new to me! Thx.

John

 

[John Bell]

Hi

When using SQL server there is no need to access the filesystem to
search for a database. As far as the location of the application it is
no different to his current configuration.

John

 

[Steve Jorgensen]

Hi

When using SQL server there is no need to access the filesystem to
search for a database. As far as the location of the application it is
no different to his current configuration.

John

If your application needs to read an Access file someone gives you, that's a
file. To read it, you have to access the file system. If you're querying it
via SQL Server, then SQL Server needs to know where it is and what file path
to reach it by - from the SQL Server, not from the client.

 

[John Bell]

I still can not see why this should be needed as the OP wanted to
upgrade and there was no mention of a requirement for a linked server
or to periodically import data from access.

John