How to convert my MS Back -End Database To MS SQL Server 2K?

[Nader B via AccessMonster.com]

I have a newly developed MS Acess 2003 project regarding Employee Management
System. I have splitted the FE and BE. The BE resides on the Network Server
and is working fine. As the company is installing/moving out to to MS SQL
Server 2K, they want my BE to be in MS SQL and so with all other databases
created with MS ACCESS for maximizing and leveraging the benefit of a
scalable database server.

My problem is, I have no idea how to do it. I have read about upsizing the
Database and would like to follow the method of which only my database linked
tables will resides in the SQL Server and have no further modification on my
FE source codes, since they just want it to be deployed without delays. What
would you recommend? and how would I do it? Please help!!!

Thanks in advance for your help and time giving a solution. Hope to hear
from anyone of you...

Nader

 

[aaron.kempf]

you can use the upsizing wizard; but if i were you.. i'd talk in
general about what types of queries you use.. you'll probably need to
rewrite some of them

 

[Norman Yuan]

Since SQL Server and Jet Engine are different database system, when move
database in *.mdb file to SQL Server, some changes are inevitable, event the
move is merely on the BE.

You can move tables in *.mdb BE to SQL Server witj MS Access's "Upsizing
Wizard" fairly easy. But after upsizing, you still need to go through each
table's structure to verify each column's data type, index, primary key....
I remembered (could be wrong) the primary key may not be transferred, and
you need to re-specify primary key for each table. Also, the upsizing wizard
convert "Memo" column type in JET into "Text" or "NText", you may want to
change it to "vachar" or "nvarchar", since this data type in SQL Server can
le as long as 8K. Anyway, after transferring tables, carefully go through
the tables' structure, relationships, and do changes if necessary.

As for FE, you can still minimize the changes if your FE designed well (you
link the FE to SQL Server BE via ODBC). It is very hard to say how much
changes you need without seeing it, it could be minor, or major. One problem
with this FE is it is difficult to fully use SQL Server's power (SP,
UDF,...), many queries have to be processed in FE.

In order to fully use SQL Server's power in MS Access, you may re-design
your FE in MS Access *.adp project. Howerver, if the SQL Server is planning
to move SQL Server2005 soon, then MS Access ADP's future is still not clear
at this time.

 

[Nader B via AccessMonster.com]

Dear Mr. Yuan,

Thanks a lot for your valuable advices. I have read already about adp or
something but never develop one. I am practically comfortable writing
application in an MS ACCESS FE & BE without so much problems.

Now I have to try your inputs and have to be oriented to the SQL Server
application environment. This will surely a lot of work.

Thanks once again... God Bless You...

Nader

Since SQL Server and Jet Engine are different database system, when move
database in *.mdb file to SQL Server, some changes are inevitable, event the
move is merely on the BE.

You can move tables in *.mdb BE to SQL Server witj MS Access's "Upsizing
Wizard" fairly easy. But after upsizing, you still need to go through each
table's structure to verify each column's data type, index, primary key....
I remembered (could be wrong) the primary key may not be transferred, and
you need to re-specify primary key for each table. Also, the upsizing wizard
convert "Memo" column type in JET into "Text" or "NText", you may want to
change it to "vachar" or "nvarchar", since this data type in SQL Server can
le as long as 8K. Anyway, after transferring tables, carefully go through
the tables' structure, relationships, and do changes if necessary.

As for FE, you can still minimize the changes if your FE designed well (you
link the FE to SQL Server BE via ODBC). It is very hard to say how much
changes you need without seeing it, it could be minor, or major. One problem
with this FE is it is difficult to fully use SQL Server's power (SP,
UDF,...), many queries have to be processed in FE.

In order to fully use SQL Server's power in MS Access, you may re-design
your FE in MS Access *.adp project. Howerver, if the SQL Server is planning
to move SQL Server2005 soon, then MS Access ADP's future is still not clear
at this time.

I have a newly developed MS Acess 2003 project regarding Employee
Management

[quoted text clipped - 18 lines]

Nader

 

[aaron.kempf]

yeah.. i really reccomend ADP if you're new to SQL Server.

i mean-- you actually get a tool that lets you write sprocs and views--
i mean-- ADP makes SQL Server as easy to use as MDB.

-aaron

 

[joel sitbon]

Friend,

joel-ange sitbon has invited you to join GreenZap and get $50 WebCash to
spend online. Sign up for a FREE GreenZap account and get $50 to spend at
hundreds of the world's premier merchants, many of whom are offering
incredible upfront discounts. Click on the link below to go to GreenZap and
signup! All thanks to joel-ange sitbon.

It's Zappening in the GreenZap Storez.
http://www.greenzap.com/joel1962

If you do not want to receive these emails in the future click the link
below:
http://www.greenzap.com/optout_invite.asp

Since SQL Server and Jet Engine are different database system, when move
database in *.mdb file to SQL Server, some changes are inevitable, event
the move is merely on the BE.

You can move tables in *.mdb BE to SQL Server witj MS Access's "Upsizing
Wizard" fairly easy. But after upsizing, you still need to go through each
table's structure to verify each column's data type, index, primary
key.... I remembered (could be wrong) the primary key may not be
transferred, and you need to re-specify primary key for each table. Also,
the upsizing wizard convert "Memo" column type in JET into "Text" or
"NText", you may want to change it to "vachar" or "nvarchar", since this
data type in SQL Server can le as long as 8K. Anyway, after transferring
tables, carefully go through the tables' structure, relationships, and do
changes if necessary.

As for FE, you can still minimize the changes if your FE designed well
(you link the FE to SQL Server BE via ODBC). It is very hard to say how
much changes you need without seeing it, it could be minor, or major. One
problem with this FE is it is difficult to fully use SQL Server's power
(SP, UDF,...), many queries have to be processed in FE.

In order to fully use SQL Server's power in MS Access, you may re-design
your FE in MS Access *.adp project. Howerver, if the SQL Server is
planning to move SQL Server2005 soon, then MS Access ADP's future is still
not clear at this time.