Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access with ADOX too slow Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 1:59 AM
Points: 6, Visits: 42
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.
Post #1565644
Posted Tuesday, April 29, 2014 12:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 82, Visits: 1,009
Try switching to the DAO library, it's usually faster than ADODB/ADOX.
Post #1565771
Posted Wednesday, April 30, 2014 12:06 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 1:59 AM
Points: 6, Visits: 42
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.
Post #1566214
Posted Wednesday, April 30, 2014 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 82, Visits: 1,009
You're welcome!
Post #1566233
Posted Wednesday, April 30, 2014 12:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 82, Visits: 1,009
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!
Post #1566234
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse