SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access with ADOX too slow


Access with ADOX too slow

Author
Message
amadrazo2006
amadrazo2006
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 44
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.
rf44
rf44
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 1093
Try switching to the DAO library, it's usually faster than ADODB/ADOX.
amadrazo2006
amadrazo2006
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 44
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.
rf44
rf44
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 1093
You're welcome!
rf44
rf44
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 1093
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search