Skip to main content
Email Password   helpLost your password?

Sample Image

Introduction

If you are using databases like Oracle or SQL Server and you want to copy data from them to some other database that is not linked to your data in any way, you can do this by using some DTS like SQL Server, but the exact scenario that compelled me to develop this utility was to “do all in an automated way”. I developed this application to convert my source data to MS Access file format but the same can be used to convert any kind of data source to MS Access.

Background

As I mentioned earlier, the reason to develop this application was to do everything in an automated way. That means, I want to periodically convert my source data to MS Access in an automated fashion.

Code

Imports System.IO
Imports System.Data.OleDb
Module Module1
    Sub Main()
        Try
            'Access DB File path
            Dim File_Path As String
            File_Path = _
             AppDomain.CurrentDomain.BaseDirectory & "db.mdb"
            'If Exists already then delete it first
            If File.Exists(File_Path) Then
                File.Delete(File_Path)
            End If
            'Connection String for New DB File
            Dim Access_ConnStr As String = _
              "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
              " Data Source=" & File_Path
            'ADOX.Catalog Object to craete new DB
            Dim Catalog As New ADOX.Catalog()
            Catalog.Create(Access_ConnStr)
            Catalog.ActiveConnection.close()
            Catalog = Nothing
            'OleDb Connection to Create New table
            Dim Access_Conn As OleDb.OleDbConnection
            Access_Conn = New OleDb.OleDbConnection(Access_ConnStr)
            Access_Conn.Open()
            Dim strSQL As String = "CREATE TABLE tblTest (" & _
            " TerritoryID                              Text(20)," & _
            " TerritoryDescription                     Text(50)," & _
            " RegionID                                 Number);"

            Dim cmd As OleDbCommand = Access_Conn.CreateCommand()
            cmd.CommandText = strSQL
            cmd.ExecuteNonQuery()
            'Set and Open Connection of the Source DB
            Dim SrcConnStr As String = "Provider=sqloledb; " & _ 
                "Data Source=(local); Initial Catalog=northwind; " & _ 
                "User Id=sa; Password=;integrated " & _ 
                "security=SSPI;persist security info=False;"
            Dim SrcConn As New OleDbConnection(SrcConnStr)
            SrcConn.Open()
            Dim srcReader As System.Data.OleDb.OleDbDataReader
            strSQL = "select * from Territories"
            Dim SrcCmd As New OleDbCommand(strSQL, SrcConn)
            'Read from Source and populate in the Access DB
            srcReader = SrcCmd.ExecuteReader()
            While srcReader.Read
                strSQL = "insert into tblTest values('" & _
                         srcReader.GetValue(0).ToString & "','" _
                         & srcReader.GetValue(1).ToString & _
                         "','" & srcReader.GetValue(2).ToString & "')"
                cmd.CommandText = strSQL
                cmd.ExecuteNonQuery()
            End While
            cmd.Dispose()
            cmd = Nothing
            Access_Conn.Close()
            Access_Conn.Dispose()
            Access_Conn.ReleaseObjectPool()
            Access_Conn = Nothing
            srcReader.Close()
            srcReader = Nothing
            SrcCmd.Dispose()
            SrcCmd = Nothing
            SrcConn.Close()
            SrcConn.Dispose()
            SrcConn = Nothing
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Module

Working with the demo

Download the demo project and unzip it, and then run "DTS.exe". It will generate an MS Access file “db.mdb” in the same directory. If you view this file, it will contain all the data of the Northwind database of SQL Server. Note: To run this demo application, SQL Server must be installed on your machine.

Working with the code

Download the code and open it in Visual Studio .NET, then do the following changes if you want to change this according to your requirement:

That’s al...

Points of Interest

Summary

There are so many tools for DTS, but this is an effort to do the same in my own desired way; and using this single application, many targets can be achieved. So use this in your own way. Happy programming!

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
GeneralAdding another Table Pin
MGDude		20:03 11 Jun '08  
Generaldts ? Pin
scalpa98		11:04 1 Nov '07  
Generalcsv file Pin
scalpa98		9:21 31 Oct '07  
GeneralRe: csv file Pin
Malik Nasir		4:08 1 Nov '07  
Questionupdate DATE datatype into access Pin
Questionaire		18:01 24 Nov '06  
AnswerRe: update DATE datatype into access Pin
Malik Nasir		20:17 26 Nov '06  
GeneralWhat's the fastest way to populate an MS Access table? Pin
MelStalcup		11:12 28 Jun '06  
GeneralCopying a table from one Access db to another Pin
dralexh		3:18 9 Jun '06  
GeneralRe: Copying a table from one Access db to another Pin
James H		0:14 12 Jun '06  
GeneralRe: Copying a table from one Access db to another Pin
Malik Nasir		1:19 12 Jun '06  
GeneralRe: Copying a table from one Access db to another Pin
dralexh		3:21 12 Jun '06  
GeneralRe: Copying a table from one Access db to another Pin
Malik Nasir		22:41 12 Jun '06  
GeneralDTS? Pin
michaelschuer		0:34 6 Jun '06  
GeneralRe: DTS? Pin
Malik Nasir		1:41 6 Jun '06  
GeneralRe: DTS? Pin
vlad.pitaru		6:36 6 Jun '06  
GeneralRe: DTS? Pin
Malik Nasir		19:51 8 Jun '06  
GeneralRe: DTS? Pin
Ziener		23:48 16 Nov '06  
QuestionHi, Pin
| Muhammad Waqas Butt |		0:06 3 Jun '06  
AnswerRe: Hi, Pin
Malik Nasir		20:34 4 Jun '06  
AnswerRe: Hi, Pin
James H		0:23 12 Jun '06  
Last Visit: 2:40 14 Nov '09     Last Update: 2:40 14 Nov '09 1


Last Updated 31 May 2006 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2009