Access with ADOX too slow

  • Hi

    My questrion is about connection to DB.

    I had migrate my DB Access to DB SQL Server 2008 and I' ve linked tables. In a function I have this code:

    Public Sub F_MANT_Load(Nom_Form As String, F_Cont_Actual As String, f_list_actual As String, _

    F_Mant_Actual As String, F_Cont_Padre As String, F_List_Padre As String, _

    Campo_Actual As String, Campo_Padre As String, cambio As Boolean)

    Dim Nom_Actual As String

    Dim Nom_Padre As String

    Dim T_Actual As String

    Dim T_Padre As String

    Dim Clave As Key

    Dim cnn As Connection

    Dim Catalogo As New ADOX.Catalog

    Nom_Actual = right(Nom_Form, Len(Nom_Form) - 7) 'Se obtiene el nombre raiz a partir del nombre del formulario

    T_Actual = "T_" & Nom_Actual 'Nombre de la tabla actual

    F_Cont_Actual = "F_CONT_" & Nom_Actual 'Nombre del formulario contenedor actual

    f_list_actual = "F_LIST_" & Nom_Actual 'Nombre del formulario listado actual

    F_Mant_Actual = "F_MANT_" & Nom_Actual 'Nombre del formulario mantenimiento actual

    T_Padre = Tabla_Padre(T_Actual) 'Nombre de la tabla padre

    If T_Padre <> "" Then

    Nom_Padre = right(T_Padre, Len(T_Padre) - 2) 'Nombre padre

    F_Cont_Padre = "F_CONT_" & Nom_Padre 'Nombre del formulario contenedor padre

    F_List_Padre = "F_LIST_" & Nom_Padre 'Nombre del formulario listado padre

    End If

    'Find primary key in current table

    Set cnn = CurrentProject.Connection

    Set Catalogo.ActiveConnection = cnn

    For Each Clave In Catalogo.Tables(T_Actual).Keys

    If Clave.Type = adKeyPrimary Then

    Campo_Actual = Clave.Columns(0).Name

    End If

    I'm debuggin the code and in this part of the code, that is to say on Loop For Each its delay about 2 minutes.

    I've read about this problem and it recommended to use ADODB. Is somebody can help me to change ADOX to ADODB?

    Thanks in advance.

    Arsenio.

  • Try switching to the DAO library, it's usually faster than ADODB/ADOX.

  • Thahks I was searching on the Web and found this solution and and the problem was fix. This is solution using DAO:

    Dim db As dao.Database

    Dim tdf As dao.TableDef

    Dim clave As dao.Index

    Set db = CurrentDb()

    Set tdf = db.TableDefs(T_Actual)

    For Each clave In tdf.indexes

    If clave.Primary = True Then

    Campo_Actual = clave.Fields(0).Name

    End If

    Next

    And this loop go fast.

    Greetings.

    Arsenio.

  • You're welcome!

  • Thinking of it, it could be faster:

    For Each clave In tdf.indexes

    If clave.Primary = True Then

    Campo_Actual = clave.Fields(0).Name

    Exit For ' No need to keep looking when the answer is found.

    End If

    Next

    Have a nice day!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply