migrate access to sql server 2005

Preface

Prerequisites
·         .Net Framework Version 2.0
·         SQL Server 2005 (Express, Standard, Enterprise) installed
·         MS Access (2000,2002,2003) file to be migrated
·         MS Visual J# Version 2.0
·         SQL Server Migration Assistant (SMAS)

Download Links
·         .Net Framework Version 2.0
·         SQL Server 2005
·         MS Visual J# Version 2.0
·         SSMA for Access

Assumptions
·         .Net Framework Version 2.0 is installed
·         SQL Server 2005 is installed and remote connections are enabled

How-To

Instructions
1.     Log into your SQL Server 2005 data source
2.     Right click Databases and click “New Database…”
3.     In the “Database Name” field, enter the name of the database that you would like to create and press “OK” when done
4.     Download MS Visual J# Version 2.0 from the download link above
5.     Install MS Visual J# Version 2.0
6.     Download SQL Server Management Services (SMAS) from the download link above
7.     Install SSMA
8.     Start the SSMA application
9.     Create a new project by clicking File > New Project … or click the New Project icon
10.  Give the project a name and location of where to save the project file like Figure 1

Figure 1



11.  To preserve primary key numbering
a.     Click Tools > Project Settings
b.     Click the “Migration” link
c.     Change “Keep Identity” to true see Figure 2


Figure 2 - You can change other settings here to match your desired database environment

12.  Press “Apply” and “OK”
13.  Add your MS Access file to the SSMA project by clicking the “Add Files” graphic  in the upper left of your SMAS screen
14.  Under the Access Metadata Explorer, you can now expand the tree to see all of the objects related to this Access file and then click the name of database to make sure all of your objects are highlighted

Figure 3



15.  Add your SQL Server 2005 connection to the SSMA project by click the “Connect to SQL Server” graphic  in the upper left
16.  Enter your database server, database name, authentication, user name and password to connect to your SQL Server 2005 instance in Figure 4

Figure 4

17.  You will see in the SQL Server Metadata Explorer, the list of databases available to your SQL Server.  Click the name of the database you will be importing the MS Access data into

Figure 5

18.  From your Access Metadata Explorer, click the name of your Access database and then click the “Convert Schema” graphic
19.  Along the bottom, you will see the output panel start outputting the conversions of the schema in Figure 6

Figure 6

20.  From your SQL Server Metadata explorer, expand “dbo” tree and then expand the “Tables” tree and you will see that the converted schema was loaded into the database in Figure 7


Figure 7

21.  Now right-click the “Tables” folder that is above the tblTest table icon and from the options window you will see an option that says “Load to Database” or click the  graphic.  This will create and modify all of the tables that you have loaded into the schema and place them into the database as table objects.
22.  From your Access Metadata explorer, left click the “Tables” folder under your Access file tree see Figure 3
23.  You will see the “Migrate Data” graphic  become enabled
24.  Click “Migrate Data” and the data from your Access database will be exported into SQL Server 2005
25.  A report of the completed tasks will pop up, press close and your migration is complete

Kutipan Membangun -Albert Einstein-

-  Jika A adalah 'sukses', maka rumusnya adalah 'A=X+Y+Z', dimana X adalah 'kerja',
   Y adalah 'bermain', dan Z adalah jaga mulut anda agar tetap tertutup.
-  Cobalah tidak untuk menjadi seseorang yang sukses, tetapi menjadi seseorang
    yang bernilai.
-  Saya tidak memiliki bakat tertentu. Saya hanya ingin tahu.
-  Satu-satunya sumber pengetahuan adalah pengalaman.
-  Pelepasan tenaga atom telah merubah segalanya kecuali cara kita berpikir.
    pemecahan untuk masalah ini  tergantung kepada hati nurani umat manusia.
    Jika saya mengetahuinya, lebih baik saya menjadi pembuat jam tangan.
-   supaya kamu berhasil langgarlah peraturan peraturan yang ada, lakukan
     yang menurut kamu benar
-   hidup seperti naik sepeda agar tidak terjatuh kamu harus tetap bergerak
    yang terpenting adalah tidak berhenti bertanya.
-   jika anda ingin mendapatkan hasil yang berbeda tetapi masih melakukan
     hal yg sama itu namanya terori yg   gila.jika anda ingin sesuatu hal yg berbeda
     maka lakukan lah hal yg berbeda pula.
     -Albert Einstein-
      Related Posts with Thumbnails