Sql-server – MS Access gets Disconnected

I'd recommend moving away from sqlmaint.exe (will get deprecated) and move to SQLCMD in combination with actual SQL queries http://msdn.microsoft.com/en-us/library/ms180944.aspx

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

I have scheduled DBCC CHECKDB as scheduled jobs weekly, you shouldn't need to check it every time you back up

In my case, samsmith's example did not work.

However, I got it fixed by using something like this:

    Dim DB As DAO.Database
    Set DB = CurrentDb
    Dim Tdf As TableDef

    'Loops through list of tables
    For Each Tdf In DB.TableDefs
        If Tdf.SourceTableName <> "" Then
          'this checks if it actually is a odbc table as i do not want to change the connection string of local tables obviously
          If Not InStr(Tdf.Connect, "ODBC") = 0 And Not (Tdf.Attributes And
dbAttachedODBC) = 0 Then
              Tdf.Connect = "ODBC;DSN=xxx;APP=MicrosoftR Access;WSID=A2200;DATABASE=xxx;Network=DBMSSOCN;TABLE=dbo." & Tdf.Name
              Tdf.RefreshLink 'Saves the changes made
          End If
        End If
    Next
Debug.Print "Finished!"

The Network=DBMSSOCN is the key part, this specifies that tcp should be used.