Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?

[Bon]

Hello all

Would it be possible to migrate the MS Access 2000 to MS SQL Server
2000?

My application is using MS Access 2000 as database and as user
interface such as forms. Now, I want to migrate the backend database
from MS Access 2000 to MS SQL Server 2000. However, I want to keep the
MS Access 2000 interface. Would it be possible?

If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?

Which tool I can use to do the migration? Upsizing wizard or exporting
the Access database and then importing it to the SQL server?

Thanks in advance

Cheers
Bon

[db55]

This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.

[Trevor Best]

db55 wrote:

This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.

If only it were that easy :-)

[Steve Jorgensen]

On Tue, 01 Nov 2005 08:09:08 +0000, Trevor Best <no****@localhost.invalid>
wrote:

db55 wrote:

This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.

If only it were that easy :-)

What Trevor is getting at is that it's easy to take an average Access app,
change the tables into links to tables on the server, and have it technically
function. Unfortunately, many of the design decisions commonly built with a
JET back end are not appropriate for a C/S app, and perform dismally in that
context.

[Trevor Best]

Bon wrote:

Hello all

Would it be possible to migrate the MS Access 2000 to MS SQL Server
2000?

My application is using MS Access 2000 as database and as user
interface such as forms. Now, I want to migrate the backend database
from MS Access 2000 to MS SQL Server 2000. However, I want to keep the
MS Access 2000 interface. Would it be possible?

If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?
Queries are one thing you'll need to look at. In the first instance
Access may do a direct translation to T-SQL (if you're lucky) otherwise
it can prepare a load of SPs and execute them, sometimes this works and
sometime it results in it effectively bringing across the entrire tables
from SQL Server and performing joins locally, which is bad. YMMV.

If you're moving queries to views, take care if the query uses any built
in or VBA functions as these won't exist in T-SQL. You can either write
a UDF or write the query differently.

In Access the use of "where exists (select...)" performs very badly
compared to "where column in (Select column...)", in SQL Server the
exists method is more efficient.

DAO code may behave unexpectedly, e.g. I had this problem
(http://www.besty.org.uk/memory.htm) but that's since been fixed and I
can't reproduce it now. Using ISAM methods in DAO (.Index, .Seek) will
not work.

Data-types shouldn't be a problem, SQL Server has more than Access,
Access will assume its own types when it sees them on the server. A few
caveats apply:

Access Yes/No is equivalent to SQL Bit but make sure you make it
required and default to 0 else unpredictable results can occur. Also
realise the values are different, in SQL Server its 0 and 1, in Access
its 0 and -1 and most of the time translates OK but if using an Access
query its better to use <>0 as criteria than =1 or =-1 or =True just to
be on the safe side.

Datetime data.
SQL Server only allows back to 1753 or some such, Access allows
(incorrectly as it doesn't handle missing days) further back than that.
You may think this might not concern you but the number of people who
put in 1/12/202 instead of 2002 will cause you immediate problems. If
you have that situation you have a problem already but just not realise it.
Also datetimes are stored differently in each and floating point errors
can occur resulting in the dreaded "#deleted" appearing in rows where
this occurs or "data has changed" errors. A timestamp column in the
table cures this.
Which tool I can use to do the migration? Upsizing wizard or exporting
the Access database and then importing it to the SQL server?

I've not come across a perfect one but in the past I've used the
upsizing wizard as it upsized more in the way of indexes, relationships,
etc.