I've created two ADO Connections. One that connects to a SQL server and runs a Stored procedure that simply reads through records. The other connects to a local temp table in the Access client for which I'm writing records. Both connections seem to be functioning.

At the end of the process, I use the DoCmd statement to open an access report which has a recordsource = the local temp table. I also close all my connections prior to opening the report.

However, the problem I'm having is that the report shows no data when it opens. I can leave the report open, and go open the temp table and it shows the records as being written. Meanwhile, the next time I click on the button that executes this code off the form, it then opens the report with the most recent records along with the records from the previous run. It seems that whenever I open the form for the first time, and I click on the button to execute my code, the report is empty even though the records have been written. Every supsequent execution of the code while the form is still open will then display the date on the report. Meanwhile, I substituted the OpenReport with OpenQuery, and the query will also show no records upon initial execution of the code. Meanwhile, I copied and pasted the code. Any and all suggestions would be most appreciative. Thanks.


'Declare variables
    Dim objCom As ADODB.Command 'Define an ADO command
    Dim param1 As ADODB.Parameter 'Define an ADO parameter
    Dim param2 As ADODB.Parameter 'Define an ADO parameter
    Dim strSQL2 As String
    Dim cntr As Integer


'Open connection to SQL
    Set cnn = New ADODB.Connection

'Open Connection to local DB
    With cnnLocal
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open CurrentProject.Path & "\EstimateTracker.mdb", "Admin"
    End With

    cnn.Open msConnectionString

    Set objCom = New ADODB.Command 'Set reference to ADO command

'Set the stored procedure to execute and command type
    objCom.CommandText = "spBiddersReport"
    objCom.CommandType = adCmdStoredProc

'Set the parameters for the stored procedure
    Set param1 = objCom.CreateParameter("SalesmanInit", adVarChar, adParamInput, Len("DJA"), "DJA")
    objCom.Parameters.Append param1
    'param1.Value = "DJA"

    Set param2 = objCom.CreateParameter("BidderName", adVarChar, adParamInput, Len("Altimate Electric"), "Altimate Electric")
    objCom.Parameters.Append param2
    'param2.Value = "Altimate Electric"


'Set the active connection to ADO connection
    objCom.ActiveConnection = cnn

'Execute the stored procedure and fill in the form
    Set rs = objCom.Execute
    If rs.EOF And rs.BOF Then
        MsgBox "No bidder information found."
        ' , vbInformation, "RetrieveEstimateBidder"
        rs.Close
        Set rs = Nothing

    End If

  strSQL2 = "SELECT * FROM tblTempOpenbyBidder"
  With rsLocal
    Set .ActiveConnection = cnnLocal
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
       .LockType = adLockOptimistic
        .Open strSQL2
    End With

    Do While Not rs.EOF

    With rsLocal

       rsLocal.AddNew
       rsLocal!Company = rs!Company
       rsLocal!BidDateDue = rs!BidDateDue
       rsLocal!Salesman = rs!Salesman
       rsLocal!JobName = rs!JobName
       rsLocal!Model = rs!Model
       rsLocal.Update


    End With

     rs.MoveNext

    Loop

'Clean up

    rs.Close
    rsLocal.Close
    Set rs = Nothing
    Set rsLocal = Nothing
    Set objCom = Nothing
    cnn.Close
    cnnLocal.Close
    Set cnn = Nothing
    Set cnnLocal = Nothing


DoCmd.OpenReport "rptOpenQuotes-byBidder", acViewPreview

End Sub

I think this is an ADP problem. The data is being written, but somehow not committed. This is my next research project.

The way I got around this with a subform is that I created an intermediate form that pops up when the form button is clicked. The On Load event of this form is the ADO call. Then the form has a timer event to close itself and open the form with subform, and the timer is set to 3000 (3 seconds.) Since the timer doesn't start to run till after the ADO call, this is usually enough time for the data to get to the table and the subform to display the data.

I set the form to remove all the controls and the border, and then make it pop up and modal. That way the user can't do anything with the form or application while it is on the screen.

I will do further research to find the real answer, but in the meantime, this kludge seems to work. Just make the form look professional and have a message that data is loading.

HTH


mmcdonal

I mean an ADO problem. (Another misspelling!)


mmcdonal

MMC--Thanks for the reply. I definitely think its and ADO bug also. Meanwhile, I added the following code which performs the same task with the Timer; however, I don't have to open another form. The delay is five seconds, and this definitely fixes the problem. I run the code after I close the connection. Thanks again for your reply!

Code:

PauseTime = 5
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

This is elegant code. I use the intermediate form just to notify my users what is going on, otherwise they will report a bug or slow application. I even include a Quit Application button on the pop up form in case they get impatient waiting the 3 seconds. Yeesh.


mmcdonal