Fundamental problem with ACCESS 2010?

[vandewinkle]

I have written a small program to read then make some small changes to the data and write to another table. If I run the program, it misses writing around 3 records to the new table. But if I debug run (f8) thru the first 4 or 5 records, then those missing records are written to the new tab le. The only change was HOW I ran the program.
Any, and I mean any, suggestions would be appreciated. I'm thinking there is a fundamental problem with Access or my computer itself. Would like to find a way to verify where the problem is.

[boblarson]

Post the code you are using. It also could be a timing issue.

[vandewinkle]

Thanks in advance Bob for looking at this for me. BTW, forgot to mention that it is always the first 4 to 5 records that are missing. Here's the code:

Dim rst As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim prevPart As String
'WaitSeconds (20)
strSQL = "SELECT PartNo, ReqDelQty, ReqDelDate, CustOrd, CustOrdLn, Qualifier, FcstDate, CustName FROM tmpforecast" & intFile
strSQL = strSQL & " ORDER BY PartNo, ReqDelDate, Qualifier;"
If rst.State = adStateOpen Then rst.Close
rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
prevPart = ""

If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not rst.EOF
If prevPart <> rst.Fields(0) Then
newAccum = 0
prevPart = rst.Fields(0)
End If
calcType = rst.Fields(5)
startQty = rst.Fields(1)
StartDate = rst.Fields(2)
calcDivideBy = 1
accumMos = 0
varMos = 1
If calcType = "Q" And startQty > 3 Then calcDivideBy = 3
If calcType = "Q" And startQty < 3 Then calcDivideBy = 1
If calcType = "A" And startQty > 12 Then calcDivideBy = 12
If calcType = "A" And startQty < 12 Then
calcDivideBy = 4
varMos = 3
End If
If calcType = "A" And startQty < 4 Then calcDivideBy = 1
calcQty = startQty \ calcDivideBy
remQty = startQty - (calcQty * calcDivideBy)
newDate = StartDate
counter = calcDivideBy
Do While counter > 0
counter = counter - 1
newQty = calcQty
If remQty <> 0 Then
newQty = calcQty + 1
remQty = remQty - 1
End If
newAccum = newAccum + newQty
'MsgBox "Qty, Date, Type: " & newQty & ", " & newDate & ", " & calcType
If newQty > 0 Then
'WaitSeconds (1)
strSQL2 = "INSERT INTO tmpForecastCalcd" & intFile & " (PartNo, ReqDelQty, ReqDelDate, CustOrd, CustOrdLn, AccumTotal, Qualifier, OrigQty, ForecastDate, Customer)"
strSQL2 = strSQL2 + " VALUES('" & rst.Fields(0) & "', " & newQty & ", '" & newDate & "', '" & rst.Fields(3) & "', '" & rst.Fields(4) & "', " & newAccum & ", '" & calcType & "', " & startQty & ", '" & rst.Fields(6) & "', '" & rst.Fields(7) & "');"
If rs2.State = adStateOpen Then rs2.Close
rs2.Open strSQL2, CurrentProject.Connection, adOpenStatic, adLockOptimistic
End If
accumMos = accumMos + varMos
newDate = DateAdd("m", accumMos, StartDate)
Loop

rst.MoveNext
Loop
End If
If rst.State = adStateOpen Then rst.Close
If rs2.State = adStateOpen Then rst.Close

[boblarson]

A couple of quick questions

1. are you declaring your variables like newDate? I don't see it.

2. Is ReqDelDate a text field or a date field (as stored in the table)? The same goes for ForecastDate.

[vandewinkle]

global declarations:
Dim rst As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim calcQty, newQty, calcDivideBy, remQty, varMos, counter, startQty, newAccum, intFileNo As Integer
Dim accumMos As Double
Dim newDate As Date
Dim StartDate As Date
Dim dteFcstDate(2) As Date
Dim strFullFilePath(2) As String
Dim strTableName, strCustomer, strPathName, calcType, strSQL, strSQL2, strFileName, strPath, strPathtemp, strFile, strString
Dim strYYYYMMDD_HHMM, strYYYYMMDD1, strYYYYMMDD2 As String

...and both dates are in date/time format in all tables

[boblarson]

These are all being declared as Variant EXCEPT the last one (intFileNo)
Dim calcQty, newQty, calcDivideBy, remQty, varMos, counter, startQty, newAccum, intFileNo As Integer

And these are all being declared as Variant:
Dim strTableName, strCustomer, strPathName, calcType, strSQL, strSQL2, strFileName, strPath, strPathtemp, strFile, strString

And these too (except the last one):
Dim strYYYYMMDD_HHMM, strYYYYMMDD1, strYYYYMMDD2 As String

FYI - in VBA, it doesn't do like in VB6 where you can declare a bunch on the same line and specify the type in the last one. In VBA it is necessary to do EACH ONE.

So, that should be fixed but I don't think it is the entire source of the problem, but it could be part of the problem.

The other problem is when you use strings you need to include quotes for the values part and dates need the octothorpes (#). So, based on what I see you would at least need to change to this:

strSQL2 = "INSERT INTO tmpForecastCalcd" & intFile & " (PartNo, ReqDelQty, ReqDelDate, CustOrd, CustOrdLn, AccumTotal, Qualifier, OrigQty, ForecastDate, Customer)"
strSQL2 = strSQL2 & " VALUES('" & rst.Fields(0) & "', " & newQty & ", #" & newDate & "#, '" & rst.Fields(3) & "', '" & rst.Fields(4) & "', " & newAccum & ", '" & calcType & "', " & startQty & ", #" & rst.Fields(6) & "#, '" & rst.Fields(7) & "');"

(also use Ampersands (&) to concatenate)

[vandewinkle]

Made changes; but still doing the same old thing.
Decided to do a few runs and document results. (see attached Testresults.png)
Results tell me when I'm in debug mode and I give a 3 sec delay or more, then record counts are good.
I think I may have a timing issue. If true, what can I do? I've tried putting in a WAIT of 1 sec in between each write but that didn't correct my issues. Any suggestions would be helpful.

[ssanfu]

Made changes; but still doing the same old thing.
Decided to do a few runs and document results. (see attached Testresults.png)
Results tell me when I'm in debug mode and I give a 3 sec delay or more, then record counts are good.
I think I may have a timing issue. If true, what can I do? I've tried putting in a WAIT of 1 sec in between each write but that didn't correct my issues. Any suggestions would be helpful.

PMFJI,
Would you be able to provide (in a text file) 20-25 lines (or more) of data that you are using? (I don't have A2010)

Since you are using ADO, I am trying DAO; I want to see if DAO will have the same problem.

The code runs, but since I made up data (and only a few lines) it is hard to tell if there is a problem in my version of the code.

[vandewinkle]

Here ya go. tab delimited

[ssanfu]

Here ya go. tab delimited

The attached mdb is in A2K format - I think you will be able to run it. A reference to Microsoft DAO 3.6 is required.

There is one form and two tables. The source data is shown in the "Source" list box. Click on the "RUN" button and the results are shown in the "Results" list box.

Is this close to what you want??

[vandewinkle]

First, let me thank you for jumping in and taking the time to help me troubleshoot. Identifying the problem is half the battle, and now I know I'm halfway thru this dilemma.
DAO code gave me the same type of results. If I run in debug mode and F8 thru the 1st few records, then everything is fine. If not, then I get missing records in the beginning of the recordset.
So to be sure, I imported all my data into your test database and everything worked perfectly. this means there is something wrong with the database. I have compacted and repaired but that showed no change in results.
Any ideas on what to check in this database? This database is from our ERP software and its sole purpose is for creating our own customize reports. I will be talking with them on this also; but as I said any input on what could be happening will be much appreciated.

[ssanfu]

Any ideas on what to check in this database? This database is from our ERP software and its sole purpose is for creating our own customize reports. I will be talking with them on this also; but as I said any input on what could be happening will be much appreciated.

I don't know if this will work, but you could create a NEW blank database, then import the tables and form (you might have to recreate it manually) from the A2K mdb. Run it and see if you get the correct results.

If the new mdb (with DAO code) executes without errors, then your A2010 mdb is corrupt. Start over......

Also, I would go into OPTIONS and turn OFF auto-corrupt... sorry, auto-correct. In earlier versions (than A2010) it is a known cause of corruption..
That is all I've got. Good luck.

[vandewinkle]

Thanks again for all your help/input. I will give it a try when time alots it. It is a very old and large database and it needs to be cleaned up. Enjoy the holidays.