Trying to wait for Count > 0

[thayes5150]

I have an Access 2003 database linked to an MS SQL 2005 backend. We use the database as part of a process whereby we use Zone OCR to scan records off paper forms into an XML file, suck the data off the XML file into a table and then present the data in the MS ACCESS db for manipulation and correction before being pushed back into other SQL tables. The Scanning process runs on a seperate instance of SQL so the only way to fire it from a Form object is through a SQL job. Unfortunately this will not return a timely recordset, meaning I get a notice that the job started, but that happens immediately and I don't know when the job ends, because the job runs on a named instance of SQL. I have figured out a method of firing a job off in the main instance of SQL to insert a single record into a logging table when the job completes. What I would like to do is use a Do Until loop, or some other method to look at this logging table (UCT_Count) and when the Count of records in the table is greater than 0, then pop a message box in Access stating the job is complete. I Have the following code to fire off the Job

 
Function DataTransfer()
 
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim lngRecs As Long
 
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
 
    With cnn
      .Provider = "SQLOLEDB"
      .Properties("Data Source") = "ServerName"
      .Properties("Initial Catalog") = "APEXDATA"
      .Properties("User Id") = "XXXXuserName"
      .Properties("Password") = "XXXXXXXXX"
      .Open
    End With
 
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "dbo.ACC_util_UCT_DataTransfer"
    cmd.CommandType = adCmdStoredProc
 
    cmd.Execute
 
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing
  End Function
 

This will fire the stored proc which starts a job etc...that ends with a single record in the logging table. I am just not sure how to wrap this in a Do Until loop until rCount >0

  SELECT COUNT(CompleteDate) FROM UCT_Count as rCount 

Then pop a MsgBox to show the job completed.
I hope this makes sense, if not let me know.

[NeoPa]

Would something like this work for you :

Do
    DoEvents()
    rCount = DCount("[CompleteDate]", "[UCT_Count]")
Loop Until rCount > 0

[thayes5150]

Thanks, that did the trick - had to play around with placement to get it to keep from interrupting my code execution, but it works like a charm now. Much better alternative to a generic sleep timer that I was going to use. Thanks again. Completed code posted below:

Function DataTransfer()
 
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim lngRecs As Long
    Dim rCount As Integer
 
    rCount = 0
 
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
 
    With cnn
      .Provider = "SQLOLEDB"
      .Properties("Data Source") = "ServerName"
      .Properties("Initial Catalog") = "APEXDATA"
      .Properties("User Id") = "XXXXuser"
      .Properties("Password") = "XXXXXX"
      .Open
    End With
 
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "dbo.ACC_util_UCT_DataTransfer"
    cmd.CommandType = adCmdStoredProc
 
    cmd.Execute
 
        Do
        DoEvents
        rCount = DCount("[CompleteDate]", "[dbo_UCT_Count]")
        Loop Until rCount > 0
        MsgBox "The Data has been transfered."
 
 
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing
  End Function
 

[ADezii]

If you wish to avoid a Do..Loop all together:

1. Create a small Form and Minimize it.
2. Set the Form's Timer Interval to 10000 (10 secs).
3. Place the following code, or something similar, in the Form's Timer() Event.
   

   Private Sub Form_Timer()
   Dim intResponse As Integer
   Dim strMsg As String
    
   strMsg = "Record Count in Table UCT_Count > 0" & vbCrLf & vbCrLf & _
            "Reset/Minimize Form and Set Timer Interval?"
    
   If DCount("*", "UCT_Count") > 0 Then
     DoCmd.Restore
     Me.TimerInterval = 0        'Turn OFF the Timer
       intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Reset Form")
           If intResponse = vbYes Then
             DoCmd.Minimize
             Me.TimerInterval = 10000    'Restore Timer Interval to 10 secs
           End If
   End If
   End Sub
    

4. Every 10 seconds (you can vary this), Table UDC_Count will be polled to see if it contains a Record. If it does, the Form will be Restored with some kind of descriptive Message or a Message Box, and the Timer will be Disabled. You will then have the option to Rest the Timer, Minimize the Form again, and start all over.

[NeoPa]

While my suggestion answers your question more directly, I would certainly recommend ADezii's suggestion over that as it uses the facilities of Access better.

Note also the slight change to the DCount() usage. Your original call requests the count of all records which contain a value for [CompleteDate]. If you really need a simple count of all records, then ADezii's version is the more correct. Even if there will always be a value for any existing record, his code is clear what it's asking for whereas yours (in that situation) would return an accurate value, but be misleading for anyone reading your code.