April 28, 2014 at 10:14 am
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.
April 29, 2014 at 12:47 am
Try switching to the DAO library, it's usually faster than ADODB/ADOX.
April 30, 2014 at 12:06 am
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.
April 30, 2014 at 12:55 am
You're welcome!
April 30, 2014 at 12:58 am
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