Upsize Access Data base to SQL 7 or MSDE

how to upsize Access database *.mdb to SQL 7 or MSDE files that can be distributed to other machine. I already read documents and need to dettach or attach files. How to do this exactly ?
ahTriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tchalkovCommented:
the best way to upsize a access database is by using Acceess Upsizing tools. They are downloadable from the microsoft site.

However you don't need them if you have Access 2000 - upsizing wizard is part of MS Access 2000.

when you need to move the sql database to a different sql server/msde, do this by copyng the database files, then executing on the new sql server/msde :

sp_attach_db @dbname=N'your_database_name ' , @filename1=N'path-to-file1',....

of course the new server must have the same database sort order and codepage

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ahTriAuthor Commented:
When I only install MSDE is there anyway to upsize the database ? by command line or any thing that don't need Access ... ?
0
tchalkovCommented:
why do you need this?
usually you upsize your database once, and then distibute it in sql file format.

You can also upsize the database, then create a script which creates it(this is done from SQL Senterprise Manager) and the you can execute this script whenever you want to create your database on SQL Server or msde.

As far as i know there is no easy way to upsize your databse with msde only, without using Access. It is possible however.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ahTriAuthor Commented:
The point is my customer now using Jet Database (Access stuff) now if my application shift to MSDE I have to upsize their existing database and I want to do that by install Shield or some automation so that my customer don't have to install Access 2000 and upsize the database one by one.
0
ahTriAuthor Commented:
Can you give some more sample command and details how to move SQL database to another machine

sp_attach_db @dbname=N'your_database_name ' , @filename1=N'path-to-file1',....

where can I execute this ? is it include in MSDE package ?
0
ahTriAuthor Commented:
Please give me an sample, say I have an Employee database on my MSDE server

what command to export it to files

on customer machine
what command to import those files into customer machine's MSDE server

If my customer already have Employee (Jet database) how to upsize it by Install Shield
0
tchalkovCommented:
There is an utility which can be used to execute sql statements on msde - osql. using it you can find what are the files of the database.

osql -S yourservername -w 1000 -E -Q "sp_helpdb employee" > res.txt

in the file res.txt you will have information about which files you need to copy.

stop msde and copy this files to some other location.

then on the client machine copy this files in the msde data folder and again using osql create the database :

osql -S yourservername -w 1000 -E -Q "sp_attach_db @dbname=N'employee', @filename1=N'...',@filename2=N'..'"

replace all '...' with the filenames of the files you copied.

if everything is ok this should be enough.

if your customer has only jet database, then there is a lot of work to do if you want to upsize it to MSDE without the upsizing tools and Access. Here are a few tips how this can be done.
First I assume that all jet databases have the same structure. If not then it is far more difficult to do the job.
Upsize one of the databases on SQL Server, then create a script which recreates the structure of the database.
Then create an sql script which creates a linked server to the client's jet database and then copies the data from every table in the jet database to MSDE.
On your customer's machine execute the both scripta so the database is created on MSDE and the data is copied.

the above is not easy to implement, although is possible. However there is a lot of work to do.
0
ahTriAuthor Commented:
There is an utility <- What util ? please specific

Thanks alot
0
tchalkovCommented:
its name is OSQL - located in
mssql7\binn directory
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.