Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Create an Access Database
By Darren Green
Version 7.0/2000
Level Intermediate

When exporting data to a text file, if the file does not exist it will be created, or if it does exist it will be overwritten. In contrast, when exporting to an Access database the file and table must exist already. This article will show you how to create an empty Access database ready to receive your data.

We can create the MDB file in an ActiveX Script Task using the Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security. It is more commonly known as the ADOX library which as the name suggests is an extension to regular ActiveX Data Objects (ADO).

The following snippet of code demonstrates how you could create a new MDB file from within an ActiveX Script Task:

	Dim oCatalog
	Set oCatalog = CreateObject("ADOX.Catalog")
	oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb"
	Set oCatalog = Nothing

The code snippet could be used as it stands, but hopefully the code below shows what can be done in a real world scenario. In this example a new file is created daily, and the filename includes the current date. The working path for the files is held in a global variable. If the package fails it may be re-run in which case the previous file is deleted when found. An existing Access connection in the package must be updated to point to the new days file.

' Pkg 288 (Create Access MDB - Scenario)
Option Explicit

Function Main()

	Dim sFilename
	Dim sConnectionString
	Dim oCatalog
	Dim oConn
	Dim oFSO

	' Derive new filename - X:\Path\Export_yyyymmdd.mdb
	sFilename = DTSGlobalVariables("Path").Value &  "Export_" & Year(Date) &_
		Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & ".mdb"

	' Check for existing instance of file and delete
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	If oFSO.FileExists(sFilename) Then
		oFSO.DeleteFile sFilename
	End If
	Set oFSO = Nothing

	' Create empty MDB
	sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilename
	Set oCatalog = CreateObject("ADOX.Catalog")
	oCatalog.Create sConnectionString
	Set oCatalog = Nothing

	' Get referemce to Access Connection and set new filename
	Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Access")
	oConn.DataSource = sFileName
	Set oConn = Nothing


	Main = DTSTaskExecResult_Success
End Function

To complete the setup phase we must create an empty table in the new Access database. We can use an Execute SQL Task to run a normal CREATE TABLE statement. When building the package for the first time you can use the Create button to generate the Jet SQL for you. Just copy and paste the SQL generated from the Create Destination Table dialog into the Execute SQL Task. You will notice that Jet SQL requires that the table name and column names are delimited with the ` character (ASCII code 96):

CREATE TABLE `New Table` (
`StringColumn` VarChar (128) NOT NULL, 
`IntegerColumn` Long NOT NULL )

Finally you can use a DataPump task to perform the actual data transfer. The completed package would look like this:
Package Layout