How to change an Access project connection string dynamically?

[John]

Our users use an Access project to access our departmental database. I need a way to allow
the users to switch to another database (archive data) within the Access project. Trying to
do the following of course fails:

Application.CloseCurrentDatabase
Application.CurrentProject.AccessConnection.ConnectionString = NewConnectStr
Application.OpenCurrentDatabase AccessProjectFile

because as soon as you execute the CloseCurrentDatabase method the program terminates and the
next two lines never get executed and you can't change the connection string unless the
database is closed.

You can change the connection string by using the menus, i.e. File->Connection..., so it does
seem to be possible to change the connection string of a currently open database. I prefer
not to use any special add-ins as this project file is distributed to many people and I don't
want to be dependent on the add-in as Microsoft comes out with newer versions of Access.

Does anyone have any suggestions? Thanks for you help.

John

 

[Sylvain Lafontaine]

Use CurrentProject.CloseConnection(­) and OpenConnection() methods instead.

It might be a good idea to close the connection at the end if you don't want
your users to be a the archive data the next time. See the post from Adam
Bunter at:
http://groups.google.ca/group/micro...ection+openconnection&rnum=1#2e56a842bf4c6bf0

There is a newsgroup about ADP: m.p.access.adp.sqlserver .

 

[John]

Sylvain,

Thanks that works.

John

Use CurrentProject.CloseConnection(­) and OpenConnection() methods instead.

It might be a good idea to close the connection at the end if you don't want
your users to be a the archive data the next time. See the post from Adam
Bunter at:
http://groups.google.ca/group/micro...ection+openconnection&rnum=1#2e56a842bf4c6bf0

There is a newsgroup about ADP: m.p.access.adp.sqlserver .