By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Automating migration from mdb to MS SQL

P: n/a
I am migrating two large databases from .mdb files into an MS SQL
database. I would like some information on how to automate the process
of moving the data over.

The two original databases store similar data so the goal of my work is
to create a new database that merges all the data from the old ones.
The table structure is changing slightly so I can't make a 1:1 copy.

My first idea is to write a program in VB to populate the new tables
with the old data, but are there better approaches? I've heard of the
Import function and the Upsizing Wizard, but I don't think they apply
since there are too many changes to the DB structure.

Thanks for your feedback,

Nathan

Apr 19 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Nathan wrote:
I am migrating two large databases from .mdb files into an MS SQL
database. I would like some information on how to automate the process
of moving the data over.

The two original databases store similar data so the goal of my work is
to create a new database that merges all the data from the old ones.
The table structure is changing slightly so I can't make a 1:1 copy.

My first idea is to write a program in VB to populate the new tables
with the old data, but are there better approaches? I've heard of the
Import function and the Upsizing Wizard, but I don't think they apply
since there are too many changes to the DB structure.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the SQL Server Data Transformation Service (DTS) in SQL'r
2000 or the Integration Services in SQL'r 2005 to read the data from the
Access DBs into the SQL'r DB. Read the SQL Books On Line (BOL) about
the data transformation.

You can also attach the Access db to the SQL'r and read the data from
the Access tables using SQL'r queries (DTS also does this, but in a more
structured way). Read the BOL articles on sp_attach_db for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREbfW4echKqOuFEgEQJTzACfVL+SZjP+zT1NM44P3Xuf0p EebEAAn0Yb
xijgCBTPzwU2GgMw5ydmNF1G
=zK1L
-----END PGP SIGNATURE-----
Apr 20 '06 #2

P: n/a
MGFoster wrote:
Nathan wrote:
I am migrating two large databases from .mdb files into an MS SQL
database. I would like some information on how to automate the process
of moving the data over.

The two original databases store similar data so the goal of my work is
to create a new database that merges all the data from the old ones.
The table structure is changing slightly so I can't make a 1:1 copy.

My first idea is to write a program in VB to populate the new tables
with the old data, but are there better approaches? I've heard of the
Import function and the Upsizing Wizard, but I don't think they apply
since there are too many changes to the DB structure.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the SQL Server Data Transformation Service (DTS) in SQL'r
2000 or the Integration Services in SQL'r 2005 to read the data from the
Access DBs into the SQL'r DB. Read the SQL Books On Line (BOL) about
the data transformation.

You can also attach the Access db to the SQL'r and read the data from
the Access tables using SQL'r queries (DTS also does this, but in a more
structured way). Read the BOL articles on sp_attach_db for more info.


CORRECTION:
That should have been sp_linkedservers not sp_attach_db.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Apr 20 '06 #3

P: n/a
I have already programmed that in vb.net

You should have a database in SQL SERVER with tables same with your
MDB.
and the MDB is located in same PC with your SQL SERVER.
Here's the code.

Imports System.data
Imports System.Data.SqlClient
Imports System.IO
************************************************** ************************************************** ********
Module Module1
Dim SQL_CONNECTION As String = "Server = NMCPHILS\NMCPHISRV_E3;
user = sa; Database = STP"
Dim connSQL As SqlConnection
Dim strSQL As String

Sub Main()
Dim cmdSQL As SqlCommandBuilder
Dim cmSQL As SqlCommand
Dim AdapterSQL As SqlDataAdapter
Dim ds As DataSet
Dim drRow As DataRow
Dim AffectedRec As Int64
strSQL = "SELECT [name] FROM [sysobjects] WHERE
OBJECTPROPERTY([id],N'IsUserTable') = 1 and [name] <> 'dtproperties'"
Try
connSQL = New SqlConnection(SQL_CONNECTION)
connSQL.Open()
cmSQL = New SqlCommand(strSQL, connSQL)

AdapterSQL = New SqlDataAdapter(cmSQL)

cmdSQL = New SqlCommandBuilder(AdapterSQL)
ds = New DataSet
AdapterSQL.Fill(ds, "TableName")

For Each drRow In ds.Tables(0).Rows
DeleteTable(drRow("name"))
ImportData1(drRow("name"))
ImportData2(drRow("name"))
Next
Console.WriteLine("All data have been imported")
cmdSQL.Dispose()
connSQL.Close()
connSQL.Dispose()

Catch e As SqlException
MsgBox(e.Message)
End Try
End Sub
************************************************** ************************************************** ********
Private Sub DeleteTable(ByVal TableName As String)

Dim cmSQL As SqlCommand
Dim AffectedRec As Int64

strSQL = "TRUNCATE TABLE [" & TableName & "]"

Try

cmSQL = New SqlCommand(strSQL, connSQL)
cmSQL.CommandTimeout = 160
AffectedRec = cmSQL.ExecuteNonQuery()

cmSQL.Dispose()

Catch e As SqlException
Msgbox ("Error")
End Try

End Sub
************************************************** ************************************************** ********
Private Sub ImportData1(ByVal TableName As String)

Dim cmSQL As SqlCommand
Dim AffectedRec As Int64

strSQL = "INSERT INTO [" & TableName & "] SELECT * FROM " _
&
"OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Product\ FSTPSys1.mdb';'admin';'',
'Select * From [" & TableName & "]')"

Try

cmSQL = New SqlCommand(strSQL, connSQL)
cmSQL.CommandTimeout = 260
AffectedRec = cmSQL.ExecuteNonQuery()
cmSQL.Dispose()

Catch e As SqlException
Msgbox ("Error")
End Try
End Sub
************************************************** ************************************************** ********
Private Sub ImportData2(ByVal TableName As String)

Dim cmSQL As SqlCommand
Dim AffectedRec As Int64

strSQL = "INSERT INTO [" & TableName & "] SELECT * FROM " _
&
"OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Product\ FSTPSys2.mdb';'admin';'',
'Select * From [" & TableName & "]')"

Try

cmSQL = New SqlCommand(strSQL, connSQL)
cmSQL.CommandTimeout = 260
AffectedRec = cmSQL.ExecuteNonQuery()
cmSQL.Dispose()

Catch e As SqlException
Msgbox ("Error")
End Try
End Sub

End Module

Apr 20 '06 #4

P: n/a
Thanks for your replies guys. I don't completely understand your
answers yet as I'm new to this, but I hope they'll help me later when I
get around to doing the migration.

Thanks,

Nathan

Apr 20 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.