[XL-2010] access > excel: xretrieve s'arrete à rst.Open strSQL, cnx

[debdev]

Bonjour à tous,

J'ai lu le tuto de connexion entre access et excel et j'en ai repris le code.

J'ai une base access et j'ai un fichier excel dans lequel je veux récupérer des résultats à partir d'une requete dans le code vba.

Il y a quelques mois, j'étais sous 2003, et ça fonctionnait (pas tout mon code lol, mais la connexion se faisait et je récupérais des chiffres dans excel. Aujourd'hui, je suis sous 2010, j'ai touché à mon code et je ne récupère plus rien. Pourtant, le verrou de la base access apparait bien, ce qui prouve qu'il y a une connexion entre excel et access.

Le code, avec l'endroit où ça s'arrete (sans message d'erreur) :

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
 
Public Function xRetrieve(Optional ByVal whatparam As String = vbNullString, _
                          Optional ByVal Mois As Integer = 0)
    Dim rec As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT countparam AS COMPTE_PARAM " & _
             "FROM [QueryEtat] WHERE 1=1 "
    If Len(whatparam) > 0 Then
        strSQL = strSQL & " and ([what] = '" & whatparam & "')"
    End If
    If Mois > 0 Then
        strSQL = strSQL & " And ([moisparamG] = " & Mois & ");"
    End If
    Dim rst As New ADODB.Recordset
    rst.Open strSQL, cnx 	'en pas à pas, ça s'arrete ici, rien ne se passe, meme pas un message d'erreur
    On Error GoTo errH01
    rst.MoveFirst
    xRetrieve = CDbl(rst("COMPTE_PARAM"))
    rst.Close
    Set rst = Nothing
    Exit Function
errH01:
    Err.Clear
    xRetrieve = 0
    rst.Close
    Set rst = Nothing
End Function

Merci de votre aide

[jfontaine]

Bonjour,

En première lecture je me demande ou est instancié la variable cnx et à quoi sert la variable rec.
Peux tu m'éclairer ?

[debdev]

ouh là...
alors le vba je l'utilise en prenant des bouts de code à droite à gauche. dans le cas présent également.
j'ai pris le tuto de caféine ici et j'ai tout coper coller, sans forcément comprendre

cnx est déclarée public en haut du code et défini dans auto_open du tutoriel (moi j'ai mis une sub appelée CheckDB mais tout le reste est pareil).

et rec, je me suis posé la meme question que toi...

Code complet:

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
Public cnx As ADODB.Connection
 
Public Sub CheckDB()
    Dim strPath As String
    Application.GoTo Reference:="StrPath"
    strPath = ActiveCell
    If Len(Dir(strPath)) > 0 Then
        Set cnx = New ADODB.Connection
        ConnectDB cnx, strPath
        'MsgBox "OK"
    Else
        MsgBox "La base n'a pas pu être trouvée" & vbCrLf & _
                strPath & vbCrLf & _
                "n'est pas un chemin valide.", vbCritical + vbOKOnly
    End If
End Sub
 
Sub ConnectDB(ByRef cnx As ADODB.Connection, ByVal strPath As String)
    cnx.Provider = "Microsoft.Jet.Oledb.4.0"
    cnx.ConnectionString = strPath
    cnx.Open
End Sub
 
Public Function xRetrieve(Optional ByVal whatparam As String = vbNullString, _
                          Optional ByVal Mois As Integer = 0)
    Dim rec As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT countparam AS COMPTE_param " & _
             "FROM [QueryEtat] WHERE 1=1 "
    If Len(whatparam) > 0 Then
        strSQL = strSQL & " and ([what] = '" & whatparam & "')"
    End If
    If Mois > 0 Then
        strSQL = strSQL & " And ([moisparamG] = " & Mois & ");"
    End If
    Dim rst As New ADODB.Recordset
    rst.Open strSQL, cnx
    On Error GoTo errH01
    rst.MoveFirst
    xRetrieve = CDbl(rst("COMPTE_param"))
    rst.Close
    Set rst = Nothing
    Exit Function
errH01:
    Err.Clear
    xRetrieve = 0
    rst.Close
    Set rst = Nothing
End Function

[jfontaine]

Dans ton code, je ne retrouve pas l'appel à la procédure CheckDB.

Si tu ne l'appelles pas, ta variable cnx ne fonctionnera pas

Code :

1
2
3
Dim rst As New ADODB.Recordset
Call CheckDB
rst.Open strSQL, cnx

[debdev]

C'est de ma faute, j'ai oublié de mettre une partie du code.

En fait, je me rappelle plus pourquoi j'ai fait ça, mais la sub auto open du tuto, je l'ai divisée en 2 parties:
1. dans ThisWorkbook, le code

Code :

1
2
3
4
5
Public cnx As ADODB.Connection
 
Private Sub Workbook_Open()
    CheckDB
End Sub

2. dans Module1, le code du post précédent

En écrivant ce message, je me rends compte que cnx est déclarée Public dans ThisWorkbook et dans Module1, il doit surement y en avoir un qui sert à rien...

[jfontaine]

En écrivant ce message, je me rends compte que cnx est déclarée Public dans ThisWorkbook et dans Module1, il doit surement y en avoir un qui sert à rien...

Ca ne doit arranger les choses

Personnellement, je supprimerais celui de thisworkbook

[debdev]

J'ai donc supprimé celui de ThisWorkbook. Alors, je viens de voir un truc suite à cette modif. Quand je mets un breakpoint sur la fonction xretrieve:
1. je vais dans une cellule (exemple A1) comportant la fonction xretrieve
2. j'entre dans la formule et je tape entrée
3. le debuggeur se met en route
4. la cellule A1 affiche 0 (ce n'est pas le bon chiffre)
4. qd j'arrive à rst.Open strSQL, cnx alors un F8 entraine le déplacement du curseur de A1 vers A2. Mais A1 m'affiche #VALEUR. Et un F8 supplémentaire n'avance pas dans le code donc a priori, on ne rentre jamais dans la partie

Code :

1
2
3
4
rst.MoveFirst
xRetrieve = CDbl(rst("COMPTE_PARAM"))
rst.Close
Set rst = Nothing

Merci

[jfontaine]

Quand tu dis que la fonction retourne 0 c'est qu'elle est passé par la gestion d'erreur?

la ligne ci-dessous plantera si le champ est NULL

Code :

xRetrieve = CDbl(rst("COMPTE_param"))

Autre chose affiche la requête dans une msgbox avant son exécution. Cela te permettra de voir s'il y a une anomalie, voir de la tester directement dans access afin de vérifier son retour

[debdev]

Ah oui dsl, encore oubliée de dire que j'avais déjà mis un debug.print de ma requete, et je l'ai testé dans access, elle me retourne le bon chiffre.

Je me suis imprimée le tuto sur les recordsets car je ne maitrise pas du tout le sujet, p'tet que ça me permettra d'y voir plus clair.

Si vous avez une idée, n'hésitez pas à m'en faire part.
Merci

[jfontaine]

Tu trouveras ci dessous un exemple de fonction que j'utilise.
Tu noteras l'utilisation d'un ODBC pour la connexion à la base (Je trouve cela plus simple si cette base est utilisée dans divers fichiers)

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
Function RetourCA(Soc As Integer, Etab As String, Periode As Long) As Double
 
Dim Cnn As New ADODB.Connection
Dim Rec As New ADODB.Recordset
Dim strSQL As String
 
strUser = "Nom_User"
strPassword = "Password"
 
Cnn.Open "DSN=Nom_DSN;UID=" & strUser & ";PWD=" & strPassword
 
' ###############
' ### Requete ###
' ###############
 
 
strSQL = "select sum(Tbmontant) as CA from Ma_Table"
strSQL = strSQL & " where TbPeriode = " & Periode
strSQL = strSQL & " and TbSoc = " & Soc
strSQL = strSQL & " and TbEtab = '" & Etab & "'"
 
 
' ###########################
' ### Exécution et retour ###
' ###########################
 
Rec.Open strSQL, Cnn, adOpenDynamic, adLockReadOnly
 
If Rec.EOF = False Then
    RetourCA = Rec!CA
Else
    RetourCA = 0
End If
 
Rec.Close
Set Rec = Nothing
Cnn.Close
Set Cnn = Nothing
 
End Function

[debdev]

Salut,

Je ne réponds que maintenant car j'ai testé ton code mais je n'ai pas réussi (rq: je n'y connais rien aux connexions mais j'ai eu un message d'erreur. vu que la base n'est utilisée que dans un seul fichier, j'ai gardé le type de connexion précédent).

Mais ton code m'a permis de voir un truc: je me demande si ce n'est pas lié au mot de passe que j'ai mis sur la base. Le truc, c'est que j'ai un peu foiré quand j'ai suivi le tuto de sécurité (j'ai d'ailleurs posté ici ). Quand j'utilise le raccourci, on me demande le mot de passe. Quand j'utilise l'accès direct, on ne me demande pas le mot de passe...

Dans le fichier excel, j'ai mis le chemin de l'accès direct donc je ne sais pas si le mot de passe est utile. Vu que le verrou de la base apparait, c'est que la connexion s'est faite, non ?

Quoi qu'il en soit, j'ai testé en mettant le mot de passe à vide dans le code et en regroupant tout en une seule partie, mais je ne suis pas sure de le faire correctement. Voici mon code:

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
 
Public Function Test(Optional ByVal whatxx As String = vbNullString, _
                          Optional ByVal Mois As Integer = 0)
    Dim cnx As New ADODB.Connection
    cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\xxx\BDN xxx\xxxxxx.mdb;User Id=; Password="
 
    Dim strSQL As String
    strSQL = "SELECT countxx AS COMPTE_xx " & _
             "FROM [QueryEtat] WHERE 1=1 "
    If Len(whatxx) > 0 Then
        strSQL = strSQL & " and ([what] = '" & whatxx & "')"
    End If
    If Mois > 0 Then
        strSQL = strSQL & " And ([mois] = " & Mois & ")"
    End If
    Dim rst As New ADODB.Recordset
    rst.ActiveConnection = cnx
    Debug.Print "ActiveConnection=" & rst.ActiveConnection
    rst.Open strSQL, cnx 'ça s'arrete là, ça ne rentre pas dans cette ligne, et le verrou de la base disparait
    On Error GoTo errH01
    rst.MoveFirst
    Test = rst("COMPTE_xx")
    rst.Close
    Set rst = Nothing
    Exit Function
errH01:
    Err.Clear
    Test= 0
    rst.Close
    Set rst = Nothing
End Function

Voici ce que me donne le print de la connection:

ActiveConnection=Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=D:\xxx\BDNxxx\xxxxxx.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Est-ce normal ? Par défaut, ça m'a mis User Id =Admin.

Je précise que comme avant, en mode pas à pas, l'execution s'arrete à rst.Open strSQL et le verrou de la base disparait.

Merci de votre aide