Converting Access dB to SQL Server

[Guest]

I have an Access DB that I would like to convert to a MSDE/SQL Server
database. However, I don't know much about the topic. I've poked around the
MS website and their information about SQL server is intimidating and kind of
vague.

There's the developers edition, enterprise edition, MSDE, and so on. I don't
know which one to use for what I need.
My needs: construct the backend in MSDE and use a hosting company to serve
up data via ASP.Net. I already have a hosting company that does this for MS
Access and it works very well.

How do I bridge the gap and use MSDE instead? I've read about prices for SQL
Server in the area of $10,000. I'm assuming this is not what I need, but what
DO I need?

The software I have:
VS.Net
MS Office 2000 including MS Access

Can anyone point me in the right direction?

Thanks

 

[Norman Yuan]

From what I see, you have two issues here: 1. Converting your Access DB to
SQL Server/MSDE; 2. Who, where and what (SQL Server or MSDE) to use to host
your app online.

1. Converting Access DB:

You can use either SQL Server or MSDE, whichever is available. I do not know
whether the offer of $49 for SQL Server developer edition is still
available, you can alway download MSDE for free, though. Since MSDE does not
come with user interface for administrating, Tolearn how to create/mange it
is bit harder than using Enterprise Manager, coming with SQL Server. Also,
the way you access data in SQL Server/MSDE may as well need to be adjusted,
say, it is recommended to use Stored Procedure, rather than dynamic SQL
statement as you usually do in Access DB. There may also be some issues
while you transfer your database in Access to SQL Server/MSDE...

2. Hosting App:

Usually, the SQL Server service is provided by the hosting service company,
and you simply provide you database designed in SQL Server/MSDE and attach
it ti their SQL Server. The SQL Server can be shared by many small web apps.
Or if your app is big/popular enough so that you have to host it on one or
more dedicated servers, than you may have to also have one or more SQL
Servers running just for your app. But I do not think any service provider
would allow sereral web apps, hosted on the same server, to all use its on
MSDE on the same web server. You need to discuss the your service provider,
or find another one.

 

[Guest]

thanks. I like MSDE becaues it's cheap and because I'm not gonna exceed
the maximums, which are 2GB and 5 concurrent connections I think.

Is there a source you can steer me toward, perhaps a guide that specifically
can show me how to convert a database from Access to MSDE. By the way, is
there really any fundamental difference between MSDE and SQL Server except
for the maximums and the GUI that SQL Server offers?

 

[Norman Yuan]

To convert Access DB to SQLServer/MSDE, open Access, click Access menu
"Tools->Database Utilities->Upsizing Wizard". It will guide you through the
converting. Of course, you need to install the SQL Server/MSDE on your
computer or other computer on the network which your computer can access.

Search MSDN or MS KB for "MSDE", or even google "MSDE", you would be flooded
by tons of links.
I do not see fundamental difference between SQL Server and MSDE as fas as
you are concerned. Rather, the things that concern you more would be how to
deal with SQL Server/MSDE efficiently, using ADO.NET (as you mentioned in
prev. post that you are working with ASP.NET), comparing to using Access DB.