Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How can I dynamically set the Access System Database
By Darren Green

You can easily change the filename for an Access database connection using the Dynamic Properties Task in SQL Server 2000. Unfortunately the property selection dialog does not allow access to the system database property (Jet OLEDB:System database) which you need to specify correctly when accessing secured Access databases.

The sample code below shows you how to set both the normal database filename and the system database filename, reading them from global variables.

' Pkg 241 (Jet System Database)
Option Explicit
 
Function Main()

	Dim oConn
	Dim sNormalDatabase
	Dim sSystemDatabase

	sNormalDatabase = DTSGlobalVariables("NormalDatabase").Value
	sSystemDatabase = DTSGlobalVariables("SystemDatabase").Value
	
	' Get the Access connection by name
	Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Access")
	
	' Set the normal database file
	oConn.DataSource = sNormalDatabase

	' Set the system database
	oConn.ConnectionProperties("Jet OLEDB:System database").Value = sSystemDatabase

	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function