Data load very slow when not use Linked Table Manager

[uoghk]

I use Linked Table Manager to connect tables from SQL server.


When running, data from SQL server can be retrieved very quickly.

I want to connect the SQL tables without using the Linked Table Manager.
I search the internet and the coding is below:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String


    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next


    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function


AttachDSNLessTable_Err:
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description


End Function

At the start of my program, I attach all the tables eg:
Call AttachDSNLessTable("Customer", "sqlCustomer", "SQLServer", "MyDatabase", "sa", "P@ssw0rd")
Call AttachDSNLessTable("Order", "sqlOrder", "SQLServer", "MyDatabase", "sa", "P@ssw0rd")
Call AttachDSNLessTable("OrderDetail", "sqlOrderDetail", "SQLServer", "MyDatabase", "sa", "P@ssw0rd")

When user click Customer button, it shows customer list form.
But it loads data very very slow and shows "Not Responding" in the header bar area.
However, if I change back to use the Linked Table Manager, it load very fast.

How can I improve it?
Thanks

[darkwind]

I'm not quite sure what your purpose in attaching the tables at runtime is. There are some security-related reasons to do that, but you seem to be hard-coding in your passwords (not asking users to input that), so that doesn't seem to be a concern of yours. I'm not certain what your goal is here. Depending on what it is, there might be a different solution, still attaching tables via LTM and accomplishing your goal.

Putting that aside, the first thing I'd look at is the connection strings. Attach a table via the linked table manager, then open it and look at its properties (F4). Check that connection string and see if there are settings there you aren't implementing in your VBA. Then add that in by adjusting AttachDSNLessTable().