MS Access table bug?

N

nval005

Registered User.
Local time
Today, 14:42
Joined
Feb 20, 2007
Messages
45
here's a weird challenge guys....
is MS Access overwhelmed with data?

my code below DO what I want it to do but when the records jump up to 2 weeks of data (like 500 records)...it does not do what I want it to do.


example below is the data in my table... I will traverse thru this table and insert "z" record in between distinct records... it works perfectly for a week data. I also notice, if I save the actual MSAccess database in a fast laptop, and run it it WORKS OK.... on a Centrino 1.6GHz laptop...it doesnt....

any advice guys! Thanks !!! :)


* original table
a
a
b
b
b
b
c
c
c
d
e

* outcome table I want
a
a
z
b
b
b
b
z
c
c
c
z
d
z
e
z


and here's what it looks like when there's like 2000 of data
a
a
b <------there's should be a "z" before b. on debug window it showed it execute the code...
b
b
b
z <--------- on the debug window it does what I want it to do but final table does not match
c






below is my code:

Private Sub traverseMYOBNow()

' this method below will traverse the MYOBNow table
' and each unique record will do a query and
' loop thru this query and INSERT into MYOBFinal table
' then create a textfile based on MYOBFinal table then import to MYOB


CurrentDb.Execute "DELETE * FROM MYOBFINAL"
Dim strS, strSS, invNum As String
invNum = Me.txtInvNum.Value
Dim xa As DAO.Database, rxa As DAO.Recordset
Dim xa1 As DAO.Database, rxa1 As DAO.Recordset
strS = "SELECT * FROM MYOBNOW"
strSS = "SELECT DISTINCT MYOBNOW.CompanyOrLastname FROM MYOBNOW;"
Set xa = CurrentDb
Set rxa = xa.OpenRecordset(strS)
Set xa1 = CurrentDb
Set rxa1 = xa.OpenRecordset(strSS)

Dim countMYOBTotal, ix As Integer
rxa.MoveLast 'takes the pointer to the end to give total number
rxa1.MoveLast 'takes the pointer to the end to give total number
countMYOBTotal = rxa1.RecordCount


MsgBox countMYOBTotal & " and tot of MYOBNow table is: " & rxa.RecordCount
rxa.MoveFirst ' move pointer to the first in the list
rxa1.MoveFirst ' move pointer to the first in the list
For ix = 1 To countMYOBTotal

Dim strS1, rxSqlInsert As String
Dim xb As DAO.Database, rxb As DAO.Recordset
Dim ixx, rxbCounter As Integer
strS = "SELECT * FROM MYOBNow WHERE CompanyOrLastname='" & rxa1.Fields("CompanyOrLastName").Value & "';"
rxa1.MoveNext
Debug.Print strS
Set xb = CurrentDb
Set rxb = xb.OpenRecordset(strS)
rxb.MoveLast
rxbCounter = rxb.RecordCount
rxb.MoveFirst
Debug.Print "ix = " & ix & " -----> " & rxbCounter
For ixx = 1 To rxbCounter
'Debug.Print " " & rxbCounter

rxSqlInsert = "INSERT INTO MYOBFinal(CompanyOrLastname,Description,ItemNumber, Quantity, Price,Total,Invoice,CustomerPO,IncTaxTotal,IncTaxPrice) "
rxSqlInsert = rxSqlInsert & "VALUES('" & Replace(rxb.Fields("CompanyOrLastname").Value, "'", "") & "','" & rxb.Fields("Description").Value & "','"
rxSqlInsert = rxSqlInsert & rxb.Fields("ItemNumber").Value & "','" & rxb.Fields("Quantity").Value & "','" & rxb.Fields("Price").Value & "','" & rxb.Fields("Total").Value & "','" & rxb.Fields("Invoice").Value & "','" & rxb.Fields("CustomerPO").Value & "','" & rxb.Fields("IncTaxTotal").Value & "','" & rxb.Fields("IncTaxPrice").Value & " ');"
CurrentDb.Execute rxSqlInsert
Debug.Print "insert data..."
rxa.MoveNext
rxb.MoveNext

'ix = ix + 1
Next ixx

'z values

CurrentDb.Execute "Insert Into MYOBFinal(CompanyOrLastname) Values('z')" & ";"
Debug.Print "z..."
'PauseApp 1
'rxa.MoveNext

Next ix
Debug.Print ix & " --- end --- "
rxa.Close
rxb.Close


End Sub




OR is it time for me to move away from MSAccess?

any advice/ideas?

Thanks in advice guys!!! :):)
 
Last edited:
Tables are unordered lists. If you need to see the records in a particular sequence you must order them by some field.

You can insert records whereever you like as encountered in the recordset but they will be inserted anywhere on any page of the table itself.

Access is not the problem and handling thousands of records is a walk in the park. I have one table with 9.5 million records without any problems.
 
ok thanks !! I shall order them by invoice number.... I'll try that...

thank you! :)

I'll post again if it worked. :) I'm nearly there I can feel it!
 
But your inserted record would also need an invoice number too because it will be inserted at random into the table. Ideally use a Date field.

BTW. Avoid using an Autonumber to sort the records. Most of the time it will work but not 100 percent reliably. An Autonumber can only be relied on for a unique value not a sequence.
 
as Galaxiom says - think of the table as a "bucket" of data in to which you put all your records. Access will give them back to you in any order it like. You just set the parameters for the retrieval

so you might say, show me everything since a given date. Access will select just records dated since that date, and will show them in any convenient order.

Now, if you want a specific order, then you have to be able to identify a way of producing that sequence - either with a single field, or a combination of fields - and tell access that you want to see the data reported in that way.
 
the original table already has a sorted record arranged in company names all my code is doing is traversing a sorted record then when it detects it a new company it inserts a "z" which is an empty record

i debug print my code above and its doing what i want it to do, what i reallydont understand is why it works in a fast machine if i copy the database locally in c drive it produces the correct sequence but if run from a network shared drive it jumbles the sequence half way thru it.

is this a bug in my code? thanks in advance guys!!!
 
btw i tried ordering by invoice number, it jumbles the result table as well, also sorted the original table by company unique number and same issue as well

i cannot sortby date because thiscode is run in monthlybasis and other records are entered i. different dates. i want to group all entered entriesby the companies and separateby a "z" record in between different recordso that when i create a textfile for myob reading the table from top to bottom z indicates a VBCLF in the text file.

thanks in advance!!!
 
Let's try this again from a different perspective. (Hold onto your hat 'cause I tend to get crazy sometimes.)

OK, in general, and subject to certain exceptional cases, records have no order. There is no "next" record and no "previous" record. Records are part of a set. Any "true" SQL engine - and Access Jet database engine conforms to this - treats records as a set. If it happens that a particular method of implementation does things in a particular order, it is an accident of implementation. The "real" definition of any table action is that the action is supposed to act as though it all happened simultaneously. (No, I am not kidding. That is the way ANSI SQL is defined.)

Having said that, remember that QUERIES can include order-by clauses, group-by clauses, and other similar options. Queries impose an order on the table's contents. But here is the kicker. There must be something in the table on which a query can impose an order. Whether it is a date, an SKU number, a person's name in last, first, middle order, or something else, there must be a data element that will support the desired order. If you want to sort by date, you have to record the date. If you want to support by a sequence number, the table has to contain the number. If you want to sort by text, the table has to contain the text. (Beginning to see it?)

When you see things in what appears to you to be in the wrong order, it can mean one of several problems. First, a bad field was used for sorting. Second, a multi-column sort references the columns in the wrong order. Third, you have a multi-part key and only specified a sort on one of the member fields of the compound key.

Inserting your "z" record in a particular place successfully would imply that some very specific conditions had been met. First, before the insertion, a properly-populated field was defined for the sort. Second, you are viewing the data through a query that uses that field to impose the desired order. Third, you inserted the "z" record by applying an appropriate number in the field used to define the sort. Because that is the ONLY way that the "z" record even COULD go to the right place.

That got a little pedantic, but I don't think I lied in the description of this problem and solution.
 
ok i thought by arranging the records in a table in order or company name will do the trick

i shall use a query to sort it and iterate thru this query to create a new table with companies gruoped together and separate by "z"


thanks in advance ! will post again how i go.

thanks!!
 
back to the beginning

you don't need a separator "Z" record at all

your queries will (or at least CAN) automatically sort and filter items relating to different customers. you do not need an artificial separator.
 
nval005 said:
i shall use a query to sort it and iterate thru this query to create a new table with companies gruoped together and separate by "z"
Click to expand...

I don't think you are quite comprehending this.

No matter what you do or how you build it, no table EVER has a dependable order to the records even if it appears to when you open it in datasheet view.

Only queries can have ordered records.

You should be generating your export file from a query and inserting the z demarkator using the recordset during the generation of the text file.
 
so instead of iterating thru a table I will iterate thru a sorted query right? I shall do that then?...

I need the artificial "Z" record because my textconverted read for z then it inserts VBCrLF on the textfile, MYOB program needs a space to distinguish different invoices.
 
nval005 said:
so instead of iterating thru a table I will iterate thru a sorted query right? I shall do that then?....
Click to expand...

Yes. Just use a query (either stored or as SQL) to make the recordset. Then as you walk through the recordset during the export to text, send the z (or maybe the vbCrLf) when you encounter the change.
 
oh wait guys...I am doing the sorting thru a Query...

so I gather all the data entered for the month of say "1 Feb to 28 Feb", arranged by Company name......then move it to a table called MYOBNow...then create a query (see strS variable below).... then iterate thru MYOBNow table from top then loop...if it finds a different company creates a query and insert into MYOBFinal table....then if it detects a different company name...a Z record is artificially inserted....

this works perfectly if I move the actual database to a decent laptop...but run from a shared drive...it gives me weird MYOBFinal table where it reaches towards the end of the table the z separator does not separate two different companies...


check my code


Private Sub traverseMYOBNow()

' this method below will traver the MYOBNow
' and each unique record will do a query and
' loop thru this query and INSERT into MYOBFinal table
' and move the pointer.
CurrentDb.Execute "DELETE * FROM MYOBFINAL"
Dim strS, strSS, invNum As String
invNum = Me.txtInvNum.Value
Dim xa As DAO.Database, rxa As DAO.Recordset
Dim xa1 As DAO.Database, rxa1 As DAO.Recordset
strS = "SELECT * FROM MYOBNOW"
strSS = "SELECT DISTINCT MYOBNOW.INVOICE FROM MYOBNOW;"
Set xa = CurrentDb
Set rxa = xa.OpenRecordset(strS)
Set xa1 = CurrentDb
Set rxa1 = xa.OpenRecordset(strSS)

Dim countMYOBTotal, ix As Integer
rxa.MoveLast 'takes the pointer to the end to give total number
rxa1.MoveLast 'takes the pointer to the end to give total number
countMYOBTotal = rxa1.RecordCount


MsgBox countMYOBTotal & " and tot of MYOBNow table is: " & rxa.RecordCount
rxa.MoveFirst ' move pointer to the first in the list
rxa1.MoveFirst ' move pointer to the first in the list
For ix = 1 To countMYOBTotal

Dim strS1, rxSqlInsert As String
Dim xb As DAO.Database, rxb As DAO.Recordset
Dim ixx, rxbCounter As Integer
strS = "SELECT * FROM MYOBNow WHERE invoice='" & rxa1.Fields("Invoice").Value & "';" 'CompanyOrLastname='" & rxa1.Fields("CompanyOrLastName").Value & "';"
rxa1.MoveNext
Debug.Print strS
Set xb = CurrentDb
Set rxb = xb.OpenRecordset(strS)
rxb.MoveLast
rxbCounter = rxb.RecordCount
rxb.MoveFirst
Debug.Print "ix = " & ix & " -----> " & rxbCounter
For ixx = 1 To rxbCounter
'Debug.Print " " & rxbCounter

rxSqlInsert = "INSERT INTO MYOBFinal(CompanyOrLastname,Description,ItemNumber, Quantity, Price,Total,Invoice,CustomerPO,IncTaxTotal,IncTaxPrice) "
rxSqlInsert = rxSqlInsert & "VALUES('" & Replace(rxb.Fields("CompanyOrLastname").Value, "'", "") & "','" & rxb.Fields("Description").Value & "','"
rxSqlInsert = rxSqlInsert & rxb.Fields("ItemNumber").Value & "','" & rxb.Fields("Quantity").Value & "','" & rxb.Fields("Price").Value & "','" & rxb.Fields("Total").Value & "','" & rxb.Fields("Invoice").Value & "','" & rxb.Fields("CustomerPO").Value & "','" & rxb.Fields("IncTaxTotal").Value & "','" & rxb.Fields("IncTaxPrice").Value & " ');"
CurrentDb.Execute rxSqlInsert
Debug.Print "insert data..."
rxa.MoveNext
rxb.MoveNext

'ix = ix + 1
Next ixx

'z values

CurrentDb.Execute "Insert Into MYOBFinal(CompanyOrLastname) Values('z')" & ";"
Debug.Print "z..."
'PauseApp 1
'rxa.MoveNext

Next ix
Debug.Print ix & " --- end --- "
rxa.Close
rxb.Close


End Sub
 
hello Galaxiom !! thanks for the super quick response! hehehe

my code is right..I have been doing a query to sort them out...the weird thing is...it works perfectly when database is moved to C drive of a good computer and run...but when run from a shared drive.... i get sorted table..then as I scroll half-way....i get z on different places...but from debug.print it DOES what I want .... soooooo crazy weird...

(correction: I dont iterate thru MYOBNow table but iterate thru the recordset of strS variable...).
 
Last edited:
You are still expecting the final table to maintain the order that the records were added.

How can I say it more clearly? Tables are not ordered. Tables are not spreadsheets. They don't have rows, just records in no particular order.

If they appear to do so on a particular computer then it is nothing to do with the computer, just luck.

If you want to maintain order then put them into a structure that has order (a query, a spreadsheet or a text file).
 
hello!! :)

it makes me scratch and pull my hair :confused::eek:..it works..not luck... hahaha, in that case I'll create the text file as I traverse along the query (on my previous code).

It does work perfectly if database is moved to a Core2 Due laptop...

ok got it...table is a basket container of unordered lists! :) :D
 
Sometimes the biggest traps are the things that appear to work during testing.

The unordered nature of tables is a common one for the unwary because the table will often hold its order for small number of records. The problem then emerges when moving into production.

The rule that usually applies when it falls over is the one about the liklihood and seriousness of the failure being proportional to the importance of the observer you are demonstating to.
 
YAY !!!!!!!!!!!

thanks for the guide guys!!!

I was thinking.... debug.print tells me...my code is right... how about instead of moving the arranged record to a table I will just directly output it to the textfile which MYOB uses!! and it worked!...

Thank you thank you thank you!!!



Private Sub traverseMYOBNow()

' this method below will traver the MYOBNow
' and each unique record will do a query and
' loop thru this query and INSERT into MYOBFinal table
' and move the pointer.
CurrentDb.Execute "DELETE * FROM MYOBFINAL"
Dim strS, strSS, invNum As String
invNum = Me.txtInvNum.Value
Dim xa As DAO.Database, rxa As DAO.Recordset
Dim xa1 As DAO.Database, rxa1 As DAO.Recordset
strS = "SELECT * FROM MYOBNOW"
strSS = "SELECT DISTINCT MYOBNOW.INVOICE FROM MYOBNOW;"
Set xa = CurrentDb
Set rxa = xa.OpenRecordset(strS)
Set xa1 = CurrentDb
Set rxa1 = xa.OpenRecordset(strSS)

Dim countMYOBTotal, ix As Integer


'text maker
Dim i As Integer
Dim FileNum As Integer
Dim FileNameAndPath1 As String
Dim txtD, txtT, OutputLine, FileNameAndPath As String
txtD = Format(Now, "dd-mm-yyyy")
txtT = Format(Time, "hhmmss")
FileNum = FreeFile()
FileNameAndPath1 = CurrentProject.path & "\MYOB-Imports\" & "CCER_" & txtD & "_" & txtT & "_MYOB.txt"
'text maker






rxa.MoveLast 'takes the pointer to the end to give total number
rxa1.MoveLast 'takes the pointer to the end to give total number
countMYOBTotal = rxa1.RecordCount


'MsgBox countMYOBTotal & " and tot of MYOBNow table is: " & rxa.RecordCount
rxa.MoveFirst ' move pointer to the first in the list
rxa1.MoveFirst ' move pointer to the first in the list

'Open the file for output
Open FileNameAndPath1 For Output Access Write Lock Write As FileNum
i = 0
OutputLine = ""
For i = 0 To rxa.Fields.count - 1
If i > 0 Then
OutputLine = OutputLine & Chr(9) & rxa.Fields(i).Name
Else
OutputLine = rxa.Fields(i).Name
End If
Next i
i = 0
'Print #FileNum, OutputLine
For ix = 1 To countMYOBTotal

Dim strS1, rxSqlInsert As String
Dim xb As DAO.Database, rxb As DAO.Recordset
Dim ixx, rxbCounter As Integer
strS = "SELECT * FROM MYOBNow WHERE invoice='" & rxa1.Fields("Invoice").Value & "';" 'CompanyOrLastname='" & rxa1.Fields("CompanyOrLastName").Value & "';"
rxa1.MoveNext
'Debug.Print strS
Set xb = CurrentDb
Set rxb = xb.OpenRecordset(strS)
rxb.MoveLast
rxbCounter = rxb.RecordCount
rxb.MoveFirst
'Debug.Print "ix = " & ix & " -----> " & rxbCounter
For ixx = 1 To rxbCounter
Debug.Print " " & rxbCounter & " " & rxb.Fields.count

For i = 0 To rxb.Fields.count - 1
If i > 0 Then
OutputLine = OutputLine & Chr(9) & rxb.Fields(i).Value
Else
OutputLine = rxb.Fields(i).Value
End If
Next i


Print #FileNum, OutputLine
rxSqlInsert = "INSERT INTO MYOBFinal(CompanyOrLastname,Description,ItemNumber, Quantity, Price,Total,Invoice,CustomerPO,IncTaxTotal,IncTaxPrice) "
rxSqlInsert = rxSqlInsert & "VALUES('" & Replace(rxb.Fields("CompanyOrLastname").Value, "'", "") & "','" & rxb.Fields("Description").Value & "','"
rxSqlInsert = rxSqlInsert & rxb.Fields("ItemNumber").Value & "','" & rxb.Fields("Quantity").Value & "','" & rxb.Fields("Price").Value & "','" & rxb.Fields("Total").Value & "','" & rxb.Fields("Invoice").Value & "','" & rxb.Fields("CustomerPO").Value & "','" & rxb.Fields("IncTaxTotal").Value & "','" & rxb.Fields("IncTaxPrice").Value & " ');"
CurrentDb.Execute rxSqlInsert
'Debug.Print "insert data..."
rxa.MoveNext
rxb.MoveNext

'ix = ix + 1
Next ixx

'z values

CurrentDb.Execute "Insert Into MYOBFinal(CompanyOrLastname) Values('z')" & ";"
'Debug.Print "z..."
'skip this record
OutputLine = vbCrLf
Print #FileNum, OutputLine
OutputLine = ""
'GoTo JUMP:





'PauseApp 1
'rxa.MoveNext

Next ix
Debug.Print ix & " --- end --- "
rxa.Close
rxb.Close


Close #FileNum
'Set rs = Nothing
'Set db = Nothing
End Sub
 
I cleaned up my code and its allllllll goooooooooood !!!!!!! YAY !!!!!!!!!!!!!!!!!!!!!

Thank you "gemma-the-husky", "The_Doc_Man" and Galaxiom for pointing me to the right direction..... NOTE: table <----cannot hold sorted records!
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom