ms access mdb to ms sql express

Hi,

I had to convert a database access to Microsoft sql express
I wanted to keep the forms in access to minimize the work

Whatever i did, the search from access was always slower in mssql than it was with a simple access file (.mdb)

I used linked tables, 13 seconds to go to the end of the 13000 records with ms access search
I used a query (in access) directly to the sql server with a dsn, 3-4 seconds to search last recordm and sometimes, even with an index, my form became readonly...

I had to use a mixed technique, to fill a recordset from ms sql, then push it as source in the ms access form

Here the precious VBA code to do that in a form:

-------------- vba macro in access --------------------------

Private Sub form_open(cancel As Integer)

    sql01 = 1
 
    If sql01 = 1 Then
        db_Server01 = "mssqlipaddressorservername\sqlexpress,1533"
        database01 = "databasename"
        db_login01 = "sa"
        db_password01 = "password"
     
        Dim Conn1 As New ADODB.Connection
     
        x = 0
        Dim arrcon01()
        con01 = ""
 
        '=== mssql express 2017 connection build
        ReDim Preserve arrcon01(x): arrcon01(x) = "Provider=SQLOLEDB": x = x + 1
        ReDim Preserve arrcon01(x): arrcon01(x) = "Data Source=" & db_Server01: x = x + 1
     
        If Len(db_login01) = 0 And Len(db_password01) = 0 Then
            '=== use windows login if no login adn password
            ReDim Preserve arrcon01(x): arrcon01(x) = "Integrated Security=SSPI": x = x + 1
            ReDim Preserve arrcon01(x): arrcon01(x) = "Persist Security Info=True": x = x + 1
        Else
            '=== adodb login and pass (sqlserver is different syntax)
            ReDim Preserve arrcon01(x): arrcon01(x) = "user id=" & db_login01: x = x + 1
            ReDim Preserve arrcon01(x): arrcon01(x) = "password=" & db_password01: x = x + 1
        End If
     
        '=== ref code
        'ReDim Preserve arrcon01(x): arrcon01(x) = "Initial Catalog=" & db_name01: x = x + 1
 
        For i = 0 To UBound(arrcon01)
            If i < UBound(arrcon01) Then
                con01 = con01 & arrcon01(i) & ";"
            Else
                con01 = con01 & arrcon01(i)
            End If
        Next
     
        Conn1.ConnectionTimeout = 3
        'Conn1.CommandTimeout = 3600
        Conn1.ConnectionString = con01
        Conn1.Open
     
        Set Rs01 = CreateObject("ADODB.Recordset")  ' recordset
        'Rs01.LockType = adLockOptimistic
        'Rs01.CursorType = adOpenKeyset
     
        method01 = 1
        If method01 = 0 Then

        ElseIf method01 = 1 Then
            'https://sourcedaddy.com/ms-access/working-with-ado-recordsets.html
            Dim cmd1 As New ADODB.Command
            Set cmd1.ActiveConnection = Conn1
         
            s = "SELECT * "
            s = s & " FROM database.dbo.table order by no"
         
            'MsgBox ("sql access cmd: " & s)
         
            cmd1.CommandText = s
            cmd1.CommandType = adCmdText ' adCmdStoredProc
            Rs01.Index = "id"
            Rs01.CursorLocation = adUseClient
            Rs01.CursorType = adOpenStatic
            Rs01.LockType = adLockBatchOptimistic
         
            Rs01.Open cmd1, , adOpenKeyset, adLockPessimistic

        ElseIf method01 = 2 Then
            '=== method 2
            'https://stackoverflow.com/questions/37013322/receiving-an-error-the-object-you-entered-is-not-a-valid-record-set-property
            ' was slow, removed
         
        End If
    ElseIf sql01 = 0 Then

    End If
 
 
 
    If Not Rs01.EOF Then
        'stDocName = "principal"
        'Me.RecordSource = rs01
        'MsgBox ("query: " & s)
        'DoCmd.OpenForm stDocName, OpenArgs:=""
        'DoCmd.OpenForm stDocName, RecordSource:=s
        '=== reference from florida
        'Me.RResidentContrat_sous_formulaire1.Form.RecordSource = sql
        'Me.RResidentContrat_sous_formulaire1.Form.Requery
        'MsgBox ("query" & s)
        'DoCmd.OpenForm (stDocName), OpenArgs:=s
        'Forms(stDocName).RecordSource = s
        'Me.RecordSource = s
        Set Me.Recordset = Rs01
     
    Else
        msg01 = "ERROR" & vbCrLf
        msg01 = msg01 & "Il n'y a aucun enregistrement dans dbo.dos" & vbCrLf
        msg01 = msg01 & "Base de donnee SQL non disponible" & vbCrLf
        MsgBox (msg01)
    End If
 
 
Exit_Procedure:
    Exit Sub

End Sub