Discussion :

[faroukvazaha]

Bonjour,
J'ai un pgm en VBA qui
1°) lit une table Access en séquence,
2°) pour chaque la valeur trouvée lit une table Oracle
3°) si la valeur est trouvée : mise à jour d'une 3ème table
Rien que du classique.
Cela fonctionne très bien en requête Access, ou en utilisant la requête SQL correspondante dans une SUB en VBA.
Le soucis est que cette table Oracle contient près de 2 000 000 enr et donc le temps d'execution est très, très, très long.
J'aimerais pouvoir faire un accès direct sur la table Oracle (liée dans ma base ACCESS).
La table Oracle est la table "CONTENU".
Les 2 autres sont des tables Access.
Mais la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Contenu.Index = "ValIndex_IDX_002"

génère l'erreur :

"Erreur d'execution 3251
Opération non autorisée pour ce type d'objet"

L'index est crée bien sur et est visible lors de l'ouverture en modif par Access de cette table ORACLE.

Code VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub IMPORT_RCONTENU_TEST()
    Dim DbChoix As Database
 
    Set DbChoix = CurrentDb()
    Set ALire = DbChoix.OpenRecordset("Journal")
    Set AEcrire = DbChoix.OpenRecordset("Total")
    Set Contenu = DbChoix.OpenRecordset("CONTENU")
 
    Contenu.Index = "ValIndex_IDX_002"
 
    ALire.MoveFirst
    Do Until ALire.EOF = True
        Contenu.Seek "=", [ALire]![TRANS]
        Do Until Contenu.EOF = True
 
            If Contenu.NoMatch = False Then
                EcrireAEcrire
            End If
            Contenu.MoveNext
 
            If Contenu!CODETrans > [ALire]![TRANS] Then
                Contenu.MoveLast
            End If
        Loop
        ALire.MoveNext
    Loop
 
    Set ALire = Nothing
    Set AEcrire = Nothing
    Set Contenu = Nothing
 
End Sub

ACCESS est en version 2003
ORACLE est en version 9

Merci de votre assistance pour que je puisse lire cette table en accès direct en utilisant son index.

[LedZeppII]

Bonjour,

Index et Seek ne sont disponibles qu'avec un recordset de type Table (dbOpenTable).
Or il n'est pas possible d'ouvrir un recordset de ce type sur une table liée.

Juste un idée:
Tu peux essayer une instruction SQL SELECT dans un objet QueryDef à la place de Seek.
En affectant à la propriété Connect de l'objet QueryDef le contenu de la propriété Connect de la table liée ODBC tu obtiens une requête SQL-Direct.
Cette dernière est exécutée par le serveur de base de données Oracle.
Avec un WHERE sur le champ indexé, Oracle devrait tirer avantage de cet index.

Un exemple vite fait (avec ODBC SQL Server mais le principe est le même):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Dim db As DAO.Database, qd As DAO.QueryDef, rs As DAO.Recordset
Dim strOdbcLinkedTable As String
Dim strSQL As String, strSELECT As String, i As Integer
 
strSELECT = "SELECT * FROM dbo.Fournisseurs"
 
Set db = CurrentDb
' Crée une requête temporaire
Set qd = db.CreateQueryDef("", strSELECT)
' Copie la propriété Connect d'une table liée odbc
strOdbcLinkedTable = "dbo_Fournisseurs"
qd.Connect = db.TableDefs(strOdbcLinkedTable).Connect
 
For i = 10 To 20
    ' Source SQL de la requête temporaire
    qd.SQL = strSELECT & " WHERE ([N° fournisseur]=" & i & ")"
    ' Ouvrir recordset à partir du résultat de la requête
    Set rs = qd.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
    If Not rs.EOF Then Debug.Print rs("N° fournisseur"); " "; rs("Société")
    rs.Close
Next
 
Set db = Nothing

A+

[faroukvazaha]

Bonjour,
Merci de cette réponse.
Je vais la tester dans mon environment et je te tiens au courant
A+

[LedZeppII]

un autre exemple plus optimisé car il maintient la connexion ODBC le temps que dure la boucle

Code vb :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Dim db As DAO.Database, errX As DAO.Error
Dim Wksp As DAO.Workspace, Conn As DAO.Connection, rsodbc As DAO.Recordset
Dim strOdbcLinkedTable As String, strODBCconn As String
Dim strSQL As String, strSELECT As String, i As Integer
Dim strErrMsg As String
 
strSELECT = "SELECT * FROM dbo.Fournisseurs"
Set db = CurrentDb
On Error GoTo ERRH
 
' Créer un nouveau workspace de type ODBC
Set Wksp = DBEngine.CreateWorkspace("Wksp2", "Admin", "", dbUseODBC)
' Chaîne de connexion ODBC
' * Copie la propriété Connect d'une table liée odbc
strOdbcLinkedTable = "dbo_Fournisseurs"
strODBCconn = db.TableDefs(strOdbcLinkedTable).Connect
' Ouverture connexion ODBC
Set Conn = Wksp.OpenConnection("sqlserver", dbDriverNoPrompt, False, strODBCconn)
 
For i = 10 To 20
    ' Source SQL de la requête temporaire
    strSQL = strSELECT & " WHERE ([N° fournisseur]=" & i & ")"
    ' Ouvrir recordset à partir du résultat de la requête
    Set rsodbc = Conn.OpenRecordset(strSQL, dbOpenSnapshot, dbExecDirect)
    If Not rsodbc.EOF Then Debug.Print rsodbc("N° fournisseur"); " "; rsodbc("Société")
    rsodbc.Close
Next
 
 
QUIT:
Set db = Nothing
If Not rsodbc Is Nothing Then rsodbc.Close
If Not Conn Is Nothing Then Conn.Close
If Not Wksp Is Nothing Then Wksp.Close
Exit Sub
 
ERRH:
 
strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.Description
Select Case Err.Number
  ' Erreurs ODBC
    Case 3146, 3151, 3154, 3155, 3156, 3157, 3231, 3232, 3234, 3225, 3238, 3247, 3254
        strErrMsg = strErrMsg & vbCrLf & vbCrLf & _
           ">>> Erreurs complémentaires DAO :" & vbCrLf & _
           "======================"
        ' Récupérations Erreur(s) driver ODBC
        For Each errX In DBEngine.Errors
            strErrMsg = strErrMsg & vbCrLf & Format(errX.Number, "00000") & " : " & errX.Description
        Next
  ' L'objet est incorrect ou n'est plus défini.
  ' Survient si on exécute plus d'une fois Object.Close
    Case 3420
        Resume Next
End Select
 
MsgBox strErrMsg
Resume QUIT

On ne perd qu'une fois le temps d'établissement de la connexion au serveur.
Dans mon premier exemple pour chaque boucle on avait : temps connexion + temps exécution requête.

De toute façons il ne faut pas s'attendre à un miracle.
Ce n'est pas aussi rapide que Seek sur un index d'une table Access.

A+