[A-03] Index Oracle avec VBA Access

Version imprimable

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:

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:

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.

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:

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+

12/11/2008, 13h42
faroukvazaha

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

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

Code:

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+