Records written not imediately available

[ProfReynolds]

I have a Microsoft Access application (local machine) that writes records to a table in a second Access database (on another computer). I then immediately generate a report via Crystal Reports in a separate (local) application. The problem is that the last record does not print in the report.

Any ideas???

Program sequence:

set db = OpenDatabase(...) ' remote database
set rsin = db.OpenRecordset(..., dbOpenDynaset)
set rsout = db.OpenRecordset(..., dbOpenTable) ' same table
' replicate all of the rsin into rsout, with 
' rsout.AddNew and rsout.Update
rsout.Close
rsin.Cose
set rsout = nothing
set rsin = nothing
set db = nothing
' start Crystal Reports application

 

[JerryKlmns]

Seems that the problem comes from those replication lines of code we don 't see!

But i think it would be more efficient to use an insert into statement between the two tables. The receiving table could be a linked table or use something like

CurrentProject.Connection.Execute "INSERT INTO [TargetTable] IN '" & FullPathTo TargetDatabase & "' " & _
                     "SELECT * FROM [SourceTable];", , 129

You wont miss a thing!

 

[JoeAtWork]

I agree 100% with Jerry:
1. You have not shown the code that is likely causing your problem
2. You can probably copy the records with one INSERT statement, rather than using recordsets

 

[ProfReynolds]

Hmmmm

I thought that I had been adequately complete. Sorry. I'll try again.

Set db = OpenDatabase(<Path to DB on remote XP machine>)
Set rsIn = db.OpenRecordset("SELECT * FROM MyTable WHERE ORDERID=" & SourceID, dbOpenDynaset)
If rsIn.RecordCount > 0 Then
   Set rsOut = db.OpenRecordset("MyTable", dbOpenTable)
   rsIn.MoveFirst

   ' note - the LAST pass through this loop produces the 
   ' table record that is missing in the Crystal Report 
   ' below (it is definitely IN the table)

   Do Until rsIn.EOF()
      rsOut.AddNew
      For Each FieldVar In rsIn.Fields
         Select Case UCase(FieldVar.Name)
         Case "ORDERID"
            rsOut.Fields("ORDERID") = NewOrderID
         Case Else
            rsOut.Fields(FieldVar.Name) = FieldVar.Value
         End Select
      Next
      rsOut.Update 
      DoEvents
      rsIn.MoveNext
   Loop
   rsOut.Close
End If
rsIn.Close
Set rsIn = Nothing
Set rsOut = Nothing
db.Close
DoEvents
Set db = Nothing

' some other housekeeping, unrelated

' then I invoke the VB6 / Crystal report application (note the INI file tells this program what and how to print)
ReturnValue = Shell(Application.CurrentProject.Path & "\modules\PDFRPT.exe " & PDFRPTINI.INIFileName, vbNormalFocus)

 

[JoeAtWork]

Well, as far as I can tell your code would copy all records to the other table. Have you confirmed by looking at the new table that all records are really there? If so, the problem would eem to be with the Crystal report, and not in this code.

You say "the LAST pass through this loop produces the table record that is missing" but I don't see how you would know what the "last" record would be, since you are not specifying an ORDER BY clause in your SELECT statement. The missing record could actually have been processed anywhere in your loop.

Do you have an error handler in your subroutine? Are you doing something that would hide an error you might be getting for the particular record that seems to be missing (i.e. a "Resume Next")?