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

--
--Mike Napthine RomulusEnterprises.com

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mike Napthine" <(E-Mail Removed)> wrote in message
news:46FBF083-8896-4115-8A0A-(E-Mail Removed)...
> 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
>
> --
> --Mike Napthine RomulusEnterprises.com

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.
--
--Mike Napthine RomulusEnterprises.com


"Douglas J Steele" wrote:

> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mike Napthine" <(E-Mail Removed)> wrote in message
> news:46FBF083-8896-4115-8A0A-(E-Mail Removed)...
> > 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
> >
> > --
> > --Mike Napthine RomulusEnterprises.com
>
>
>