Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How can I change the filename for an Access Connection?
By Darren Green

You can change the Access MDB file of a connection at run-time using an ActiveX Script Task. The following example reads the new filename from a global variable and updates the connection called "Accounts".

' Pkg 201
Option Explicit

Function Main()
	Dim oConn

	Set oConn = DTSGlobalVariables.Parent.Connections("Accounts")
	oConn.DataSource = DTSGlobalVariables("MDB Filename").Value

	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function

If you are using SQL Server 2000 then it is usually quicker and simpler to use Dynamic Properties Task.