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
Dim File_Path As String
File_Path = _
AppDomain.CurrentDomain.BaseDirectory & "db.mdb"
If File.Exists(File_Path) Then
File.Delete(File_Path)
End If
Dim Access_ConnStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=" & File_Path
Dim Catalog As New ADOX.Catalog()
Catalog.Create(Access_ConnStr)
Catalog.ActiveConnection.close()
Catalog = Nothing
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()
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)
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:
- Change “
File_Path
” and set it where you want to create a new file. - Change the
Create Table
command at line 26 according to your source table. - If you are using databases other than SQL Server then change the connection string for your source DB, at line 36.
- Change the source ‘
Select
’ command at line 41. - Change the ‘
Insert
’ command at line 47 according to the source and destination tables.
That’s al...
Points of Interest
- The application can be designed to import multiple tables.
- The console application can be scheduled as an automated data import utility.
- The application can be designed in such a way that the user just has to select a database and click to import the data.
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!