We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover. We have both .Net and Access applications running off of these databases, and are testing client redirection on a server failover event.

The .Net apps respond correctly after I added a "Failover Partner" to the connection string. On a failover the app will fail on the next DB request, then move to the mirror server when we restart the app.


For the Access applications going through ODBC we are trying to use the SQL Server Native Client 10.0 driver (it looks like the standard driver doesn't support mirroring?) and supplying a mirror server in the settings.

ODBC Mirror Screen

I'm specifying the database and the failover server name here. On a failover event the Access application does not seem to be redirected to the mirror server even after an application restart. Is there anything else I should be doing here to force ODBC to switch to the mirror server, or am I not specifying the mirror server name correctly?

1 Answer 1


I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring. I use the following connection string which allows the application to connect to the mirror after an application restart:

Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
    End If
Next a
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:

DRIVER=SQL Native Client
APP=Microsoft Data Access Components


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.