f



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

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

0
10/31/2005 11:34:19 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

4 Replies
4780 Views

Similar Articles

[PageSpeed] 7

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.

0
chfran (32)
10/31/2005 10:17:38 PM
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 :-)
0
nospam4428 (164)
11/1/2005 8:09:08 AM
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.
0
nospam4428 (164)
11/1/2005 8:32:25 AM
On Tue, 01 Nov 2005 08:09:08 +0000, Trevor Best <nospam@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.
0
nospam48 (2784)
11/1/2005 8:36:34 AM
Reply: