MS Access connection to SQL server via VBA

[Carl Witte]

Hello. I have MS Access 2013 and I am trying and failing to programmaticall y connect to an sql server. Whenever I open a table (or do anything really) I get the manual connection screen and am able to reconnect manually. (See attached images for detail)

My goal would be to remove this manual connection screen through executing specific vba code at startup.

After research I have come up with the following code

Public Function startup_SQL()
Dim openSQL As ADODB.Connection
Set openSQL = New ADODB.Connection
openSQL.Open "Provider=SQLOLEDB.1;Data Source=LIMSQL01;Initial Catalog=Lab;User ID=XXXXX;Password=######"
openSQL.Close
End Function

The code appears to execute correctly. However, I continue to get the same manual connection screen before, during (after the .open command and before the .close command), and after.

I'm certain this is something others have had to deal with and while searching I expected to find a solution quite quickly, but I must be getting old. I have not been able to find a solution and have to ask a question. Thank you very much for your time.

[Carl Witte]

Ok. I now have figured out how to reconnect to the tables in SQL. I have to

1. delete the old table
2. relink the table
3. specify the primary key

This is clearly a bad idea. My code to do relink the tables is below. I feel like I'm close but I can't quite get there. Any help would be great.

Public Function startup()
    Dim lclTBLstr(50) As String
    Dim sqlTBLstr(50) As String
    Dim countER As Integer
  Dim tbl As DAO.TableDef
  Dim tables As DAO.TableDefs
  countER = 1
  Set tables = CurrentDb.TableDefs
 
DBEngine.RegisterDatabase "lab", "SQL Server", True, "Description=lab" & vbCr & "SERVER=LIMSQL01" & vbCr & "DATABASE=lab"
 
  For Each tbl In tables
    If tbl.Attributes = dbAttachedODBC Then
        lclTBLstr(countER) = tbl.Name
        sqlTBLstr(countER) = Right(lclTBLstr(countER), Len(lclTBLstr(countER)) - 4)
        countER = countER + 1
        tbl.Connect = "Provider=SQLOLEDB.1;Password=PASSWORDHERE;Persist Security Info=True;User ID=THIS_IS_AN_ID;Initial Catalog=Lab;Data Source=LIMSQL01"

    End If
  Next tbl
  countER = countER - 1
 While countER > 0
 
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=lab;UID=THIS_IS_AN_ID;PWD=PASSWORDHERE;LANGUAGE=us_english;DATABASE=lab", acTable, sqlTBLstr(countER), lclTBLstr(countER), False, True
    countER = countER - 1
 Wend
 


End Function

Please note this code hasn't been cleaned or commented yet.

[Seth Schrock]

I don't know how to use ADO connection in VBA, but I just use ODBC to connect to my SQL Server. See Managing DSN-less Connections and Relinking ODBC Tables using VBAYou will note, that my method in the first article to move servers was to delete and recreate the tables. Now, here is the code that I use to just change the link:

Public Function ChangeBackend(TableName As String, ServerLoc As Long) As Boolean
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim strConn As String

Set db = CurrentDb
Set td = db.TableDefs(TableName)

strConn = "ODBC;" & GetLocation(ServerLoc) & "Trusted_Connection=YES"
td.Connect = strConn

td.RefreshLink

ChangeBackend = True

Exit_Procedure:
    On Error Resume Next
    
    Set db = Nothing
    Set td = Nothing
    
    Exit Function

Error_Handler:
    ChangeBackend = False
    If TempVars("RunMode") = "Test" Then
        Call ErrorMessage(Err.Number, Err.Description, "modBackend: ChangeBackend")
    Else
        TSCs_ReportUnexpectedError "ChangeBackend", "modBackend", "Custom info"
    End If
    Resume Exit_Procedure
    Resume
    
End Function

The GetLocation function can be found in the original article. I hope this helps.