La Force de vba pour attendre la requête sql à exécuter avant de continuer

Cela semble être un problème assez courant, mais aucune des solutions que j'ai trouvé l'air de fonctionner.

Je suis en saisissant des données à partir de SQL Server et de le copier dans une feuille de calcul. Alors je veux copier une gamme de nouvelles données et de faire d'autres choses avec elle. Tout cela se produit en une seule fonction vba.

Mon problème est que lorsque la fonction est exécutée à partir d'Excel, il se déplace sur la deuxième partie de la fonction, sans attendre que la requête renvoie les données requises.

Bien sûr la fonction fonctionne très bien quand je le lance à partir de l'IDE vba.

Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset

a.Open (connStr)
Set r = a.Execute(sqlstr)

sht.Range("A2").CopyFromRecordset r

'please wait here until the proc has executed?

checkData = sht.Range("A2").Value

Quand je lance la fonction à partir d'Excel checkdata ce plug-in est toujours vide, quand je le lance avec la touche F5, qu'il a toujours les données requises.

Êtes-vous essayer de faire ça dans une fonction de feuille de calcul? Il ne fonctionne pas depuis une fonction ne peut retourner une valeur, il ne peut pas mettre à jour une feuille directement.
oui, je fais cela dans une fonction vba où sqlStr est une procédure stockée sql qui retourne les données requises.
Ensuite, utilisez simplement checkData = r.Fields(0).Value à moins qu'il n'y a plus de données à partir du jeu d'enregistrements dont vous avez besoin. Si vous avez besoin d'autres données, alors vous allez devoir trouver un moyen de faire tourner le code des autres que par le biais d'un fichier UDF.

OriginalL'auteur Skrealin | 2012-07-12

2

Fait ce travail?
```
Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset

a.Open (connStr)
Set r = a.Execute(sqlstr)

Do
  'Wait
Loop Until Not r Is Nothing

sht.Range("A2").CopyFromRecordset r

checkData = sht.Range("A2").Value
```
Sinon, si cela échoue, vous pouvez essayer de tester certaines propriétés de r, comme des expressions du FOLKLORE ou BOF, et si une erreur se produit, ou si vous obtenez une valeur inattendue vous savez que les données n'a pas encore chargé. Par exemple:
```
Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset

a.Open (connStr)
Set r = a.Execute(sqlstr)

On Error Resume Next
Do
  Err.Clear
  r.EOF 'Put your test here, you might test rowcount or similar.
        'I've simply asked for the EOF property and ignored the result, I'm 
        'not sure if this will work in your case. Some testing may be required.
While Err.Num <> 0
On Error GoTo 0 'Or whatever you previously had this set to

sht.Range("A2").CopyFromRecordset r

checkData = sht.Range("A2").Value
```
Merci pour la tentative @mkingston. Malheureusement, la boucle ne semble pas faire de différence. Pour la deuxième solution, j'ai déjà vérifier si les données sont retournées avant de faire la CopyFromRecordSet dans le cadre de mon ado standard extrait, il semble que la fonction est en cours d'exécution en arrière-plan et continuer sur checkdata ce plug-in.
J'ai pensé que peut-être un peu trop évident. Je te suggère de mettre un peu de debug.imprimer les lignes dans votre copyfromrecordset fonction pour vérifier que c'est effectivement arriver à chaque partie du code. Si elle l'est, sortie les valeurs de la variable afin de vérifier que tout est en lecture/traitées correctement.
Le nombre de fois où j'ai assumé le code est exécuté dans une certaine manière et a constaté qu'elle ne l'est pas. Je pense que cela est particulièrement vrai lorsque vous êtes dans un langage de script dans un autre environnement; souvent les objets sous-jacents sont en train de faire des choses que vous n'attendez pas et si vous n'avez pas une bonne compréhension de leur il est facile de rater des choses.
Après une tonne de débogage, il s'est avéré être dans une autre section de code qui avait été un bonheur de travailler pendant des mois avant, j'ai essayé d'ajouter ce nouveau genre de choses. Merci pour l'aide, au moins je sais maintenant beaucoup plus sur les jeux d'enregistrements!
Eh bien, je ne pense pas que j'ai vraiment eu beaucoup à faire avec elle, mais je suis content que vous avez trié! 🙂

OriginalL'auteur mkingston
9

Essayez d'utiliser:
```
Application.CalculateUntilAsyncQueriesDone
```
après l'exécution de l'instruction SQL, mais avant de copier le jeu d'enregistrements
```
Set r = a.Execute(sqlstr)
    Application.CalculateUntilAsyncQueriesDone
sht.Range("A2").CopyFromRecordset r 
```
En fait, ce doit être marqué comme réponse correcte. J'ai eu le même problème avec une requête sur le web, d'actualisation, que la suite des calculs ont été effectués avant que les données ont été téléchargées depuis le site web. Après l'ajout de la déclaration de Candidature.CalculateUntilAsnycQueriesDone il est en attente jusqu'à ce que le web actualiser les requêtes fini.

OriginalL'auteur Chris
2

Cela peut vous aider. Au lieu de définir la source de données dans le code, de le mettre en place sur la cible de la feuille de calcul d'une connexion de données (Excel menu Données | à Partir d'Autres Sources | etc.). Une fois un objet de connexion nommée "(par Défaut)" est créé, vous tapez dans le code le long de ces lignes:
```
  With ActiveWorkbook
    .Connections("(Default)").OLEDBConnection.BackgroundQuery = False
    .Connections("(Default)").OLEDBConnection.CommandText = sqlstr
    .RefreshAll
    ' do more stuff
    ' will wait for .RefreshAll to complete because .BackgroundQuery = false
  End With
```
OriginalL'auteur andy holaday
1

Je pense que vous avez besoin d'un r.movelast après l'exécution de assurez-vous que toutes les lignes sont retournées.

Quelque chose comme
```
Set r = a.Execute(sqlstr)

If Not r.EOF Then
    r.MoveLast
End If

sht.Range("A2").CopyFromRecordset r
```
OriginalL'auteur DJ.
1

Le long des lignes de andy holaday, j'ai eu ce travail en décochant "Activer l'actualisation en arrière-plan" dans la plage de données externes propriétés. La désactivation de cette fonctionnalité force excel à attendre que la requête est exécutée.

OriginalL'auteur Jeff Larsen

Mais si il n'y a pas de jeu d'enregistrements retournés j'ai trouvé ce code va attendre jusqu'à ce que le code SQL retourne avant de passer à la prochaine instruction VBA. Très pratique lorsqu'il y a dépendance par une commande sur un autre ou si vous avez besoin d'un ensemble complet de données créé avant de passer.

Dim Con As ADODB.Connection
Dim CmdTxt As String

Set Con = New Connection
Con.ConnectionString = ThisWorkbook.GetYourConnectString()
Con.Open

CmdTxt = "EXEC db.schema.StoredProc1 @Param1 = 'Yes'"
ExecuteSql Con, CmdTxt, True, True

CmdTxt = "EXEC db.schema.StoredProc2 @Param1 = 'No'"
ExecuteSql Con, CmdTxt, True, True

MsgBox "Both commands completed sequentially"

Le code pour ExecuteSql est:

Public Function ExecuteSql(Con As ADODB.Connection, sql As String, _
    Optional StopOnError As Boolean = True, _
    Optional WaitUntilDone As Boolean = False) As String

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

    .CommandType = 1
    .CommandText = sql
    .ActiveConnection = Con

    If WaitUntilDone = True Then
        .CommandTimeout = 0 'set timeout to unlimited
        .Execute , , adExecuteNoRecords 'no records value speeds up internal code
    Else
        .Execute
    End If

End With

ExecuteSql = ""

Exit Function

OriginalL'auteur SQLAlan

Vous devez vous connecter pour publier un commentaire.