Cant Close Connection from Access to SQL

[Mike Napthine]

I am trying to close the fallback SQL server connection and return to the
live one, by issuing a close followed by new open in VB script. No error is
given on close, the database connection just does not close, even when run
from a macro with no forms open. (The code works when switching databases on
the same server). Versions Access 2003 sp2, SQL Sever 2000 sp3.

Previous values: dbstr="TM", serverstr="FS2"
New values: dbstr="TM", serverstr="FS1"

ConnectionStr = "PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=TRUE;INITIAL
CATALOG=" + dbstr + ";DATA SOURCE=" + serverstr + ";USER
ID=TimeManagerUser;PASSWORD=xxxxxxxxxx"

CurrentProject.Connection.Close --fails!
CurrentProject.Connection.Open ConnectionStr

 

[Douglas J Steele]

See whether setting it to nothing makes a difference:

CurrentProject.Connection.Close
Set CurrentProjecte.Connection = Nothing
CurrentProject.Connection.Open ConnectionStr

On the other hand, you may have to relink your tables instead.

 

[Mike Napthine]

Good idea, but that gave me an error "invalid use of property", probably as
that is the main access connection, I fear I may have to make a new copy as
suggested, thanks.