Click here to Skip to main content
11,926,429 members (55,159 online)
Click here to Skip to main content
Add your own
alternative version


48 bookmarked

Creating and populating MS Access files from other databases, using DTS

, 31 May 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
An article on creating and populating an automated MS Access file from other databases.

Sample Image


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.


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.


Imports System.IO
Imports System.Data.OleDb
Module Module1
    Sub Main()
            '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
            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 = Nothing
            'OleDb Connection to Create New table
            Dim Access_Conn As OleDb.OleDbConnection
            Access_Conn = New OleDb.OleDbConnection(Access_ConnStr)
            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
            '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)
            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
            End While
            cmd = Nothing
            Access_Conn = Nothing
            srcReader = Nothing
            SrcCmd = Nothing
            SrcConn = Nothing
        Catch ex As Exception
        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.


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!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Malik Nasir
Program Manager ZonG, China Mobile Company
Pakistan Pakistan
I am working as Manager Enterprise Services where key responsibilities are to support end users on their day to day IT related issues.
The other function I am responsible is IT IPCC Services Support Since September 2013 and my key responsibilities are to maintain IPCC Uptime along with end users support at both CMPak call centers. IPCC enhancements and new feature development is also part of my job role.
The third area that was handed over to me in March 2015 is in-house apps support, where key responsibility is to provide support for all the application developed by IT department and to maintain the availability of these applications.
Previously I was working as Manager Automation & Planning and this is my key area of expertise. My responsibilities were to manage team in Project Planning, System Analysis, Requirements gathering, Preparation of SRS, Presentations to client about the requirements, getting Approvals on SRS from the Client, Database Design, Development Tracking, System Deployments, end user Feedback along with Change Management.

Planning and Defining Scope, Resource Planning, Time Estimating, Creating Charts and Schedules, Risk Analysis
Managing Risks and Issues, Monitoring and Reporting Progress, Team Leadership, Working with Vendors, Scalability were the key area of working along with an efficient problem-solver in professional way with envisions business and technical perspectives to develop workable solutions.

I started my career as developer in 2004 and promoted as team lead IT Automation in 2007 and then as manager so I spend 7 years in Software Development & Project Planning.

You may also be interested in...

Comments and Discussions

GeneralUsing log files to update an Access database Pin
Cher122127-Sep-10 10:31
memberCher122127-Sep-10 10:31 
GeneralAdding another Table Pin
MGDude11-Jun-08 20:03
memberMGDude11-Jun-08 20:03 
Questiondts ? Pin
scalpa981-Nov-07 11:04
memberscalpa981-Nov-07 11:04 
Generalcsv file Pin
scalpa9831-Oct-07 9:21
memberscalpa9831-Oct-07 9:21 
GeneralRe: csv file Pin
Malik Nasir1-Nov-07 4:08
memberMalik Nasir1-Nov-07 4:08 
Questionupdate DATE datatype into access Pin
Questionaire24-Nov-06 18:01
memberQuestionaire24-Nov-06 18:01 
AnswerRe: update DATE datatype into access Pin
Malik Nasir26-Nov-06 20:17
memberMalik Nasir26-Nov-06 20:17 
QuestionWhat's the fastest way to populate an MS Access table? Pin
MelStalcup28-Jun-06 11:12
memberMelStalcup28-Jun-06 11:12 
GeneralCopying a table from one Access db to another Pin
dralexh9-Jun-06 3:18
memberdralexh9-Jun-06 3:18 
GeneralRe: Copying a table from one Access db to another Pin
James H12-Jun-06 0:14
memberJames H12-Jun-06 0:14 
GeneralRe: Copying a table from one Access db to another Pin
Malik Nasir12-Jun-06 1:19
memberMalik Nasir12-Jun-06 1:19 
GeneralRe: Copying a table from one Access db to another Pin
dralexh12-Jun-06 3:21
memberdralexh12-Jun-06 3:21 
GeneralRe: Copying a table from one Access db to another Pin
Malik Nasir12-Jun-06 22:41
memberMalik Nasir12-Jun-06 22:41 
QuestionDTS? Pin
michaelschuer6-Jun-06 0:34
membermichaelschuer6-Jun-06 0:34 
AnswerRe: DTS? Pin
Malik Nasir6-Jun-06 1:41
memberMalik Nasir6-Jun-06 1:41 
AnswerRe: DTS? Pin
vlad.pitaru6-Jun-06 6:36
membervlad.pitaru6-Jun-06 6:36 
GeneralRe: DTS? Pin
Malik Nasir8-Jun-06 19:51
memberMalik Nasir8-Jun-06 19:51 
GeneralRe: DTS? Pin
Ziener16-Nov-06 23:48
memberZiener16-Nov-06 23:48 
GeneralRe: DTS? Pin
Jacek Gajek10-Jan-12 1:11
memberJacek Gajek10-Jan-12 1:11 
QuestionHi, Pin
| Muhammad Waqas Butt |3-Jun-06 0:06
member| Muhammad Waqas Butt |3-Jun-06 0:06 
AnswerRe: Hi, Pin
Malik Nasir4-Jun-06 20:34
memberMalik Nasir4-Jun-06 20:34 
AnswerRe: Hi, Pin
James H12-Jun-06 0:23
memberJames H12-Jun-06 0:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151126.1 | Last Updated 31 May 2006
Article Copyright 2006 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid