How can I use a variable in the ssis connection manager to change the Access Database?

To auto populate your variable, I would suggest holding the paths in another static database.  You can query for current month.

To literally populate your variable, I would suggest using the "Execute SQL Task".  See steps below to pull field from your database and populate your variable.

Add an "Execute SQL Task", double click it, and follow these steps:
1.  Set connection and SQL that pulls the one field holding your database path
2.  Under the Execute SQL task's properties, look for a section called "Result Set" - to which you'll set to "Single Row."
3.  Next, within the box options on the left, you'll select "Result Set"
4.  On the right, you'll set 0 for "Result Name" and your newly created variable for "Variable Name" - this will take the field you selected and populate your variable.

2 secs i give it a try. thanks

If i set my variable to the connection string property I have to use the data source string. For example,

"Data Source=Y:\SLA\Models\Models\ PCT - R2 - Month 3 - 2010.mdb;Provider=Microsoft.Jet.OLEDB.4.0;"

Is there a way in which i can just set the variable to the Access DB file name "Y:\SLA\Models\Models\ PCT - R2 - Month 3 - 2010"

Yes, but you would probably need to do it in a script.

The link below has a couple of good functions for reading and writing to variables from within SSIS scripting.
http://www.developerdotstar.com/community/node/512

Your other option is to populate the full connection string from where ever you're pulling.  Just make the path the dynamic part.

Actually, i take that back.  You could concatenate a few variables to build one final variable.

2 sesc, I'll have a read.

Thanks