Posted by Trevor Best on 11/01/05 10:32

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.

[Back to original message]